代码之家 › 专栏 › 技术社区 › Heinzi

SUM/GROUP性能和主键

performance sql-server

Heinzi · 技术社区 · 16 年前

myTable myGuid (唯一标识符), myValues myGroup

SELECT SUM(myValues)
  FROM myTable
 WHERE myGuid IN (SELECT * FROM ##test)
 GROUP BY myGroup

##test guid_filter )

查询速度很慢(<EDIT>8-12s</EDIT>)。
当我使用myAutoInc创建myTable时,一个整数自动递增字段作为主键,查询速度很快(~2s),即使

查找myValues,而不必通过guid->myAutoInc->

XML query plan

|--Compute Scalar(DEFINE:([Expr1007]=CASE WHEN [Expr1015]=(0) THEN NULL ELSE [Expr1016] END))
     |--Stream Aggregate(GROUP BY:([myDB].[dbo].[myTable].[myGroup]) DEFINE:([Expr1015]=COUNT_BIG([myDB].[dbo].[myTable].[myValues]), [Expr1016]=SUM([myDB].[dbo].[myTable].[myValues])))
          |--Sort(DISTINCT ORDER BY:([myDB].[dbo].[myTable].[myGroup] ASC, [myDB].[dbo].[myTable].[myGuid] ASC))
               |--Nested Loops(Inner Join, OUTER REFERENCES:([tempdb].[dbo].[##test].[guid_filter], [Expr1014]) OPTIMIZED WITH UNORDERED PREFETCH)
                    |--Table Scan(OBJECT:([tempdb].[dbo].[##test]))
                    |--Clustered Index Seek(OBJECT:([myDB].[dbo].[myTable].[PK__myTable__2334397B]), SEEK:([myDB].[dbo].[myTable].[myGuid]=[tempdb].[dbo].[##test].[guid_filter]) ORDERED FORWARD)

Table 'myTable'. Scan count 0, logical reads 38046, physical reads 1, read-ahead reads 6914, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '##test'. Scan count 1, logical reads 23, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

XML query plan

|--Compute Scalar(DEFINE:([Expr1007]=CASE WHEN [globalagg1009]=(0) THEN NULL ELSE [globalagg1011] END))
     |--Stream Aggregate(GROUP BY:([myDB].[dbo].[myTable].[myGroup]) DEFINE:([globalagg1009]=SUM([partialagg1008]), [globalagg1011]=SUM([partialagg1010])))
          |--Parallelism(Gather Streams, ORDER BY:([myDB].[dbo].[myTable].[myGroup] ASC))
               |--Stream Aggregate(GROUP BY:([myDB].[dbo].[myTable].[myGroup]) DEFINE:([partialagg1008]=COUNT_BIG([myDB].[dbo].[myTable].[myValues]), [partialagg1010]=SUM([myDB].[dbo].[myTable].[myValues])))
                    |--Sort(DISTINCT ORDER BY:([myDB].[dbo].[myTable].[myGroup] ASC, [myDB].[dbo].[myTable].[myAutoInc] ASC))
                         |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([myDB].[dbo].[myTable].[myGroup], [myDB].[dbo].[myTable].[myAutoInc]))
                              |--Nested Loops(Inner Join, OUTER REFERENCES:([myDB].[dbo].[myTable].[myAutoInc], [Expr1017]) OPTIMIZED WITH UNORDERED PREFETCH)
                                   |--Nested Loops(Inner Join, OUTER REFERENCES:([tempdb].[dbo].[##test].[guid_filter], [Expr1016]) OPTIMIZED WITH UNORDERED PREFETCH)
                                   |    |--Table Scan(OBJECT:([tempdb].[dbo].[##test]))
                                   |    |--Index Seek(OBJECT:([myDB].[dbo].[myTable].[myGuid]), SEEK:([myDB].[dbo].[myTable].[myGuid]=[tempdb].[dbo].[##test].[guid_filter]) ORDERED FORWARD)
                                   |--Clustered Index Seek(OBJECT:([myDB].[dbo].[myTable].[PK__myTable__2334397B]), SEEK:([myDB].[dbo].[myTable].[myAutoInc]=[myDB].[dbo].[myTable].[myAutoInc]) LOOKUP ORDERED FORWARD)

Table 'myTable'. Scan count 0, logical reads 66988, physical reads 48, read-ahead reads 2515, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '##test'. Scan count 5, logical reads 23, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

2 回复 | 直到 16 年前

Remus Rusanu 16 年前

在“慢”的情况下查看计划,它显示查询在索引上进行查找 [myDB].[dbo].[myTable].[myGuid] 然后进行聚集索引查找 [myDB].[dbo].[myTable].[PK__myTable__2334397B]

可能解释一下区别,但我怀疑这是全部。

请重新进行测试,确保myGuid上有聚集键,并且同一键上没有非聚集索引。该计划应仅在myTable上使用聚集索引进行一次搜索,以准确比较您想要比较的情况。

此外,显然,请确保您比较了相同的##测试内容,并且缓冲池缓存在这两种情况下都以相同的方式预热。跑 DBCC FREESYSTEMCACHE('All') 在每个测试之前,至少运行5次查询,忽略第一次运行(这将是预热缓冲池的运行)。

Gary McGill 16 年前

每非聚集索引(因为这是用来定位数据的),这加剧了问题。