统计和查询的效率哪个高？

Question

背景：假设有1个表：bug，包含字段：严重程度 缺陷来源 ...
该表有100万条记录（包含多个产品的缺陷）。现在需要做1个统计报表：根据 不同字段 统计 一个 产品的 缺陷数目，格式如下：-----------------------------------------
类型        缺陷数
-----------------------------------------
严重程度
  致命      100
  严重      1000
  xxx
缺陷来源：
  需求      50
  设计      100
  编码      2000假设该产品有1万个缺陷。
问题：
目前考虑两种做法：
1.取出该产品的所有缺陷（1万个）到1个DataTable中，然后再对该DataTable进行统计。
  SELECT 严重程度,缺陷来源,... FROM bug WHERE 产品 = @产品
2.直接统计
  统计 严重程度 = 致命 的缺陷数
       SELECT count(*) FROM bug WHERE 产品 = @产品 and 严重程度 = 致命
  统计 严重程度 = 严重 的缺陷数  ...
  统计 缺陷来源 = 编码 的缺陷数
  // 可能需要统计10多次。现在问题是 两种做法，那种效率更高？不需要考虑内存问题。分析：
做法1：只需要访问1次数据库，但是需要取出大量数据。
做法2：需要多次访问数据库，每次需要遍历bug表，但是只需要得到1个数
我目前倾向于做法1，不知道大家有什么看法？

Answer 1

建议使用方法二，同时在相应的表加上索引，并且不要用“count(*)”来统计，用具体的某个字段来替代。这样速度和效率都提高很多。

Answer 2

感觉你的统计，在数据库中应该可以用group by分组来做

Answer 3

1.是否可以这样说，如果没有索引，第1种效率比第2种高？
2.各位选第二种的，能否说一下理由？
3.TO:Knight94(愚翁) 关于count(*)的问题
  count(1)比count(*)更有效率
  参见：http://www.programfan.com/article/showarticle.asp?id=2935
  不过我也看到 好像orcale 9 已经自行将所有count(*) 、count(1)、count(rowID)之类全部优化成一种表达方式，不知道ADO.NET有没有优化，还是由不同的数据库自行优化。
  参见：http://www.vckbase.com/document/viewdoc/?id=1308
  如果这样，还是手工写成count(1)比较好。
4.关于索引问题
  索引的创建要与应用结合考虑，建议大的OLTP表不要超过6个索引。
  参见：http://www.programfan.com/article/showarticle.asp?id=2935
  因为统计会从不同角度进行统计，可能根据10个8个字段来进行不同的统计，如果都建索引，
  是否会有问题？

Answer 4

如果你的表用了索引，用第二种比较好点
如果数据量大，一次查询出来会有很多问题，表面上看起来像死机一样：）
count(*) '这个效率太低，建议改成相应的字段

Answer 5

exing(铱星) 建议使用group by，现在想想也有一些道理，特别是该字段是外键时，
如 根据修复人进行统计，该字段关联用户表，不可能从用户表循环读出字段，然后分别count，
所以第二种方法改为：
   SELECT 严重程度,count(1) AS num FROM bug WHERE 产品 = @产品 GROUP BY 严重程度
   SELECT 修复人,count(1) AS num FROM bug WHERE 产品 = @产品 GROUP BY 修复人

Answer 6

补充：
另外，原来5个字段，50种情况，需要count 50次，使用group by后，只需要 查询5次。

Answer 7

to 不知道ADO.NET有没有优化，还是由不同的数据库自行优化。ADO.net不会做优化，它会把sql用message的方式传递到数据库服务器端，即要看数据库端是否优化。

Answer 8

to :即使没有索引，方法二也比方法一高。数据库的统计算法要比你自己写高很多。
    如果单说执行1次第1种方法和执行1次第2种方法，肯定是第2种高，
    问题是否执行1次第1种方法和执行多次（可能10次，可能50次）第2种方法，这时候谁高？
    我来说说我的分析过程：
    sql的瓶颈有两个：锁；I/O操作；
    这里是查询，所以不考虑锁，只要考虑I/O操作。
    I/O操作，在这里表现为 遍历 的记录条数。
    方法1：遍历 100万条记录 + 读取 1万条记录（每条记录5个字段，每个字段为int型）
 
    方法2分为两种情况考虑：有索引，无索引

Answer 9

修正方法1：
  因为 字段：产品 是索引，该产品有1万条记录。
  方法1：遍历 1万条记录 + 读取 1万条记录（每条记录5个字段，每个字段为int型）

Answer 10

方法2：假设我们采用group by，则需要 使用5个count语句group字段无索引：
  每次sql:遍历 1万条记录 + 统计得到1个数字
  5次：5* (遍历1万条记录 + 统计得到1个数字)group字段有索引：
  假设 每个字段有10种情况，则每种情况为1千条记录
  每次sql:遍历 1千条记录 + 统计得到1个数字  
  5次：5* (遍历1千条记录 + 统计得到1个数字)

Answer 11

建议使用方法2第一种方法会让你的内存很快被耗尽,如果是ASP.NET,会更加严重(IIS可能会死掉,网络也有瘫痪的可能)

Answer 12

sql的瓶颈有两个：锁；I/O操作；
    这里是查询，所以不考虑锁，只要考虑I/O操作。
    I/O操作，在这里表现为 遍历 的记录条数。
    方法1：遍历 100万条记录 + 读取 1万条记录（每条记录5个字段，每个字段为int型）
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~磁盘IO并不仅仅是IO一条记录,而是含有某记录的一个磁盘块,如果仅仅从磁盘IO分析性能的话,在这个问题中,意义并不是很大另外,SQLServer和.NET CLR不是同一个进程,进程间通信本身也是很耗资源的,如果可以减少需要传递的数据量,性能自然就提高了

Answer 13

刚才不能连续发3次，所以这部分没有发上来。问题：读取 1万条记录（每条记录5个字段，每个字段为int型）会很占用时间吗？
结论：
  1.如果 group 字段没有索引，且group字段较多时，使用方法1
  2.如果 group 字段有索引，或者group字段较少时，使用方法2to:
前者表面看着是一次，但是你需要对临时记录集做处理。
第一个时间消耗在于大量记录集返回；
第二个时间消耗在于临时记录集处理。第二部分在内存中处理，不需要考虑，第一个到是需要考虑。
修正方法1：
查询时，时间主要消耗在两个方面：记录定位；字段读取，因为 字段：产品 为 聚合索引，
所以不考虑定位。
方法1：读取 1万条记录* 5个字段 = 5万修正方法2：
方法2：假设我们采用group by，则需要 使用5个count语句group字段无索引：
  每次sql: 读取 1万条记录* 1个字段（group字段）= 1万
  5次：5* （读取 1万条记录* 1个字段）= 5万group字段有索引：
  假设 每个字段有10种情况，则每种情况为1千条记录
  每次sql:读取 1千条记录* 1个字段  = 1千
  5次：5* (读取 1千条记录* 1个字段) = 5千结论：
  看来 如果无索引，则一样，有索引则 使用group进行count效率能明显提高。