有这样一个需求，系统中有个库存异动明细表(tblStockVar) ,给出一个日期，需要计算从当前日期倒推到该日期的库存数。库存异动明细表很大，每天都以几百笔的资料在增加，表结构像这个样子(序(自动增长列), 功能别，仓库别，材料，储位，材料批号，异动日期，异动良品数，异动不良数，当前库存良品数，当前库存不良数)

於是有这以下SQL查询在给出日期之后总的库存异动数.

SELECT  WHCode , LocoID  , PartCode  , LotNo  , Sum ( VarStockQty  ) AS VarStockQty  , Sum ( VarBadQty  ) AS VarBadQty 
FROM  tblStockVar  
WHERE   VarDate   > 20060801
GROUP BY WHCode , LocoID  , PartCode  , LotNo

tblStockVar中VarDate上建有索引( IX_tblStockVar_VarDate), 一个复合索引( WHCode,LocoID,PartCode,LotNo)，PK是SortKey(序) 栏位, 并且是Clustered Unique 一的个INDEX。

按我们的理解，以上SQL应该要用到索引IX_tblStockVar_VarDate, 对此索引查找后再来一个Bookmark Lookup,搜寻到对应的其它字段资料,然后再汇总。但在实际应用中，该语句经常要执行60秒左右，显然没有用到索引.来看一下执行计划.
用set statistics profile on 输出以下结果.

  |--Hash Match(Aggregate, HASH:([tblStockVar].[WHCode], [tblStockVar].[LocoID], [tblStockVar].[PartCode], [tblStockVar].[LotNo]), RESIDUAL:((([tblStockVar].[WHCode]=[tblStockVar].[WHCode] AND [tblStockVar].[LocoID]=[tblStockVar].[LocoID]) AND [tblStockVar
       |--Clustered Index Scan(OBJECT:([CustDB].[dbo].[tblStockVar].[PK_tblStockVar]), WHERE:([tblStockVar].[VarDate]>20060801))
很明显，这里使用了一个Clustered Index Scan,而不是我们想的Index Seek,那究竟是为什么呢?

我们知道，对於一条SQL语句，执行大概是这样: (1)语法分析 (2)编译并且产生执行计划 (3)执行
对於同一句SQL, SQLServer可以产生无数个不同执行计划，究竟要选择哪一个执行计划呢，这个就是优化器(Optimizer)要干的事, 它会依据每一个操作符需要的成本来决定使用总成本最低的执行计划。
先看一下上面SQL的执行成本,

(1).清除Buffer  执行 DBCC DropCleanBuffers  DBCC FlushProcInDb(<db_id>)
(2).执行 Set Statistics IO ON  Set Statistics Time ON
(3).执行语句.
以下是执行结果:

SQL Server parse and compile time:
   CPU time = 16 ms, elapsed time = 500 ms.

(影響 10750 個資料列)

Table 'tblStockVar'. Scan count 1, logical reads 91690, physical reads 2, read-ahead reads 91835.

SQL Server Execution Times:
   CPU time = 1109 ms,  elapsed time = 71024 ms.
整个的执行时间是71.024秒

如果我们手动加上IX_tblStockVar_VarDate的使用呢?
SELECT WHCode , LocoID  , PartCode  , LotNo  , Sum ( VarStockQty  ) AS VarStockQty  , Sum ( VarBadQty  ) AS VarBadQty 
FROM  tblStockVar  WITH(INDEX(IX_TblStockVar_VarDate))
WHERE  VarDate   > 20060801
GROUP BY WHCode , LocoID  , PartCode  , LotNo

产生执行计划如下:
  |--Hash Match(Aggregate, HASH:([tblStockVar].[WHCode], [tblStockVar].[LocoID], [tblStockVar].[PartCode], [tblStockVar].[LotNo]), RESIDUAL:((([tblStockVar].[WHCode]=[tblStockVar].[WHCode] AND [tblStockVar].[LocoID]=[tblStockVar].[LocoID]) AND [tblStockVar
       |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([CustDB].[dbo].[tblStockVar]) WITH PREFETCH)
            |--Index Seek(OBJECT:([CustDB].[dbo].[tblStockVar].[IX_tblStockVar_VarDate]), SEEK:([tblStockVar].[VarDate] > 20060801) ORDERED FORWARD)
没错，跟我们预想的一样，先在VarDate索引上做Seek,然后再Bookmark Lookup.来看一下这段SQL的IO&Time.
还是同样先Clear Buffer及Execute plan cache.然后再执行,以下是结果输出

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 429 ms.

(影響 10750 個資料列)

Table 'tblStockVar'. Scan count 1, logical reads 416158, physical reads 443, read-ahead reads 236.

SQL Server Execution Times:
   CPU time = 1438 ms,  elapsed time = 7840 ms.

OMG！语句执行时间只有7.8秒！比上一条语句快了将近１０倍！那究竟为什么SQL Server不选择使用IX_tblStock_Var的索引呢？欲知后事如何，且听下回分解。

发表于 @ 2007年02月05日 10:59:00|评论(loading...)|编辑