我正在努力优化一些查询.
对于下面的查询,
SET STATISTICS IO ON;
DECLARE @OrderStartDate DATETIME2 = '27 feb 2016';
DECLARE @OrderEndDate DATETIME2 = '28 feb 2016';
SELECT o.strBxOrderNo,o.sintOrderStatusID,o.sintOrderChannelID,o.sintOrderTypeID,o.sdtmOrdCreated,o.sintMarketID,o.strOrderKey,o.strOfferCode,o.strCurrencyCode,o.decBCShipFullPrice,o.decBCShipFinal,o.decBCShipTax,o.decBCTotalAmount,o.decWrittenTotalAmount,o.decBCWrittenTotalAmount,o.decBCShipOfferDisc,o.decBCShipOverride,o.decTotalAmount,o.decShipTax,o.decShipFinal,o.decShipOverride,o.decShipOfferDisc,o.decShipFullPrice,o.lngAccountParticipantID,CONVERT(DATE,120) as OrderCreatedDateConverted
FROM tablebackups.dbo.tblBOrder o
WHERE o.sdtmOrdCreated >= @OrderStartDate
AND o.sdtmOrdCreated < @OrderEndDate
AND EXISTS (
SELECT *
FROM tablebackups.dbo.tblBOrderItem oi
WHERE oi.strBxOrderNo = o.strBxOrderNo
AND oi.decCatItemPrice > 0
)
OPTION (RECOMPILE);
我创建了以下FILTERED索引：
-- table dbo.tblBorderItem
CREATE NONCLUSTERED INDEX IX_tblBOrderItem_decCatItemPrice_INCL
ON dbo.tblBorderItem
(
strBxOrderNo ASC,sintOrderSeqNo ASC,decCatItemPrice
)
INCLUDE
(
blnChargeShipping,decBCCatItemPrice,decBCCostPrice,decBCFinalPrice,decBCOfferDiscount,decBCOverrideDiscount,decBCTaxAmount,decCostPrice,decFinalPrice,decOfferDiscount,decOverrideDiscount,decTaxAmount,decWasPrice,dtmOrdItemCreated,sintOrderItemStatusId,sintOrderItemType,sintQuantity,strItemNo
)
WHERE decCatItemPrice > 0
WITH (DROP_EXISTING = ON,FILLFACTOR = 95);
此索引特别不用于此查询,还有其他查询使用此相同的索引,因此包含INCLUDED列.
特别是对于这个查询,我只想检查(EXISTS)订单是否有任何项目,其中decCatItemPrice> 0.
sql Server正在进行索引扫描,如下图所示.
>统计数据刚刚更新.
>项表在测试中有41,208行.
请注意,我没有从items表中选择任何列.
这个项目表有164,309,397现场直播.我想避免在那里扫描.
问题：
为什么sql Server没有进行索引查找？
我是否应该考虑其他因素/事项以改进此查询？
(4537 row(s) affected) Table 'tblBorder'. Scan count 1,logical reads 116,physical reads 0,read-ahead reads 0,lob logical reads 0,lob physical reads 0,lob read-ahead reads 0. Table 'tblBorderItem'. Scan count 1,logical reads 689,lob logical reads 0,lob physical reads 0,lob read-ahead reads 0. (1 row(s) affected)
这是表tblBorderItem的定义和索引
IF OBJECT_ID('[dbo].[tblBorderItem]') IS NOT NULL
DROP TABLE [dbo].[tblBorderItem]
GO
CREATE TABLE [dbo].[tblBorderItem] (
[strBxOrderNo] VARCHAR(20) NOT NULL,[sintOrderSeqNo] SMALLINT NOT NULL,[sintOrderItemStatusId] SMALLINT NOT NULL,[sintNameStructureID] SMALLINT NOT NULL,[strItemNo] VARCHAR(20) NOT NULL,[sintQuantity] SMALLINT NOT NULL,[strCurrencyCode] VARCHAR(3) NOT NULL,[decCostPrice] DECIMAL(18,4) NOT NULL,[decCatItemPrice] DECIMAL(18,2) NOT NULL,[decOfferDiscount] DECIMAL(18,[decOverrideDiscount] DECIMAL(18,[decFinalPrice] DECIMAL(18,[decTaxAmount] DECIMAL(18,[strBCCurrencyCode] VARCHAR(3) NOT NULL,[decBCCostPrice] DECIMAL(18,[decBCCatItemPrice] DECIMAL(18,[decBCOfferDiscount] DECIMAL(18,[decBCOverrideDiscount] DECIMAL(18,[decBCFinalPrice] DECIMAL(18,[decBCTaxAmount] DECIMAL(18,[dtmOrdItemCreated] DATETIME NOT NULL,[blnChargeShipping] BIT NOT NULL,[lngTimeOfOrderQtyOnHand] INT NULL,[sdtmTimeOfOrderDueDate] SMALLDATETIME NULL,[lngProdSetSeqNo] INT NULL,[lngProdRelationId] INT NULL,[lngProdRelationMemberId] INT NULL,[decWasPrice] DECIMAL(18,2) NULL,[sintOrderItemType] SMALLINT NULL,[tsRowVersion] TIMESTAMP NULL,[sdtmOrderItemStatusUpdated] SMALLDATETIME NULL,CONSTRAINT [PK_tblBOrderItem]
PRIMARY KEY CLUSTERED
([strBxOrderNo] asc,[sintOrderSeqNo] asc)
WITH FILLFACTOR = 100)
GO
CREATE NONCLUSTERED INDEX
[IX_tblBOrderItem__dtmOrdItemCreated]
ON [dbo].[tblBorderItem] ([dtmOrdItemCreated] asc)
WITH FILLFACTOR = 100
CREATE NONCLUSTERED INDEX [IX_tblBOrderItem__sintOrderItemStatusId]
ON [dbo].[tblBorderItem] ([sintOrderItemStatusId] asc)
INCLUDE ([sdtmOrderItemStatusUpdated],[sintOrderSeqNo],[strBxOrderNo],[strItemNo])
WITH FILLFACTOR = 100
CREATE NONCLUSTERED INDEX [IX_tblBOrderItem__
sintOrderItemStatusId_decFinalPrice_
sdtmOrderItemStatusUpdated_
include_strBxOrderNo]
ON [dbo].[tblBorderItem]
([sintOrderItemStatusId] asc,[decFinalPrice] asc,[sdtmOrderItemStatusUpdated] asc)
INCLUDE ([strBxOrderNo])
WITH FILLFACTOR = 100
CREATE NONCLUSTERED INDEX [IX_tblBOrderItem__strBxOrderNo]
ON [dbo].[tblBorderItem]
([strBxOrderNo] asc)
WITH FILLFACTOR = 100
CREATE NONCLUSTERED INDEX [IX_tblBOrderItem__strItemNo]
ON [dbo].[tblBorderItem] ([strItemNo] asc)
WITH FILLFACTOR = 100
CREATE NONCLUSTERED INDEX
[IX_tblBOrderItem_decCatItemPrice_INCL]
ON [dbo].[tblBorderItem]
([strBxOrderNo] asc,[sintOrderSeqNo] asc,[decCatItemPrice] asc)
INCLUDE ([blnChargeShipping],[decBCCatItemPrice],[decBCCostPrice],[decBCFinalPrice],[decBCOfferDiscount],[decBCOverrideDiscount],[decBCTaxAmount],[decCostPrice],[decFinalPrice],[decOfferDiscount],[decOverrideDiscount],[decTaxAmount],[decWasPrice],[dtmOrdItemCreated],[sintOrderItemStatusId],[sintOrderItemType],[sintQuantity],[strItemNo])
WHERE ([decCatItemPrice]>(0))
WITH FILLFACTOR = 95
这是表tblBorder上的定义和索引
IF OBJECT_ID('[dbo].[tblBorder]') IS NOT NULL
DROP TABLE [dbo].[tblBorder]
GO
CREATE TABLE [dbo].[tblBorder] (
[strBxOrderNo] VARCHAR(20) NOT NULL,[uidOrderUniqueID] UNIQUEIDENTIFIER NOT NULL,[sintOrderStatusID] SMALLINT NOT NULL,[sintOrderChannelID] SMALLINT NOT NULL,[sintOrderTypeID] SMALLINT NOT NULL,[blnIsBasket] BIT NOT NULL,[sdtmOrdCreated] SMALLDATETIME NOT NULL,[sintMarketID] SMALLINT NOT NULL,[strOrderKey] VARCHAR(20) NOT NULL,[strOfferCode] VARCHAR(20) NOT NULL,[lngShippedToParticipantID] INT NOT NULL,[lngOrderedByParticipantID] INT NOT NULL,[lngShipToAddressID] INT NOT NULL,[lngAccountAddressID] INT NOT NULL,[lngAccountParticipantID] INT NOT NULL,[lngOrderedByAddressID] INT NOT NULL,[lngOrderTakenBy] INT NOT NULL,[decShipFullPrice] DECIMAL(18,[decShipOfferDisc] DECIMAL(18,[decShipOverride] DECIMAL(18,[decShipFinal] DECIMAL(18,[decShipTax] DECIMAL(18,[decBCShipFullPrice] DECIMAL(18,[decBCShipOfferDisc] DECIMAL(18,[decBCShipOverride] DECIMAL(18,[decBCShipFinal] DECIMAL(18,[decBCShipTax] DECIMAL(18,[decTotalAmount] DECIMAL(18,[decBCTotalAmount] DECIMAL(18,[decWrittenTotalAmount] DECIMAL(18,[decBCWrittenTotalAmount] DECIMAL(18,4) NULL,[blnProRataShipping] BIT NOT NULL,[blnChargeWithFirstShipment] BIT NOT NULL,[sintShippingServiceLevelID] SMALLINT NOT NULL,[sintShippingMethodID] SMALLINT NOT NULL,[sdtmDoNotShipUntil] SMALLDATETIME NULL,[blnHoldUntilComplete] BIT NOT NULL,CONSTRAINT [PK_tblBOrder]
PRIMARY KEY CLUSTERED
([strBxOrderNo] asc) WITH FILLFACTOR = 100)
GO
CREATE NONCLUSTERED INDEX
[IX_tblBOrder__lngAccountAddressID]
ON [dbo].[tblBorder]
([lngAccountAddressID] asc,[sintOrderStatusID] asc)
WITH FILLFACTOR = 100
CREATE NONCLUSTERED INDEX
[IX_tblBOrder__lngAccountParticipantID]
ON [dbo].[tblBorder]
([lngAccountParticipantID] asc)
WITH FILLFACTOR = 100
CREATE NONCLUSTERED INDEX
[IX_tblBOrder__lngOrderedByAddressID]
ON [dbo].[tblBorder]
([lngOrderedByAddressID] asc,[sintOrderStatusID] asc)
WITH FILLFACTOR = 100
CREATE NONCLUSTERED INDEX
[IX_tblBOrder__lngOrderedByParticipantID]
ON [dbo].[tblBorder] ([lngOrderedByParticipantID] asc)
WITH FILLFACTOR = 100
CREATE NONCLUSTERED INDEX
[IX_tblBOrder__lngShippedToParticipantID]
ON [dbo].[tblBorder]
([lngShippedToParticipantID] asc)
WITH FILLFACTOR = 100
CREATE NONCLUSTERED INDEX
[IX_tblBOrder__lngShipToAddressID]
ON [dbo].[tblBorder]
([lngShipToAddressID] asc,[sintOrderStatusID] asc)
WITH FILLFACTOR = 100
CREATE NONCLUSTERED INDEX
[IX_tblBOrder__sdtmOrdCreated_sintMarketID__include_strBxOrderNo]
ON [dbo].[tblBorder]
([sdtmOrdCreated] asc,[sintMarketID] asc)
INCLUDE ([strBxOrderNo])
WITH FILLFACTOR = 100
CREATE NONCLUSTERED INDEX
[IX_tblBOrder_sdtmOrdCreated_INCL]
ON [dbo].[tblBorder]
([sdtmOrdCreated] asc)
INCLUDE ([decBCShipFinal],[decBCShipFullPrice],[decBCShipOfferDisc],[decBCShipOverride],[decBCShipTax],[decBCTotalAmount],[decBCWrittenTotalAmount],[decShipFinal],[decShipFullPrice],[decShipOfferDisc],[decShipOverride],[decShipTax],[decTotalAmount],[decWrittenTotalAmount],[lngAccountParticipantID],[lngOrderedByParticipantID],[sintMarketID],[sintOrderChannelID],[sintOrderStatusID],[sintOrderTypeID],[strCurrencyCode],[strOfferCode],[strOrderKey])
WITH FILLFACTOR = 100
CREATE NONCLUSTERED
INDEX [IX_tblBOrder_sintMarketID_sdtmOrdCreated]
ON [dbo].[tblBorder]
([sintMarketID] asc,[sdtmOrdCreated] asc)
INCLUDE ([sintOrderChannelID],[strBxOrderNo])
WITH FILLFACTOR = 100
CREATE NONCLUSTERED
INDEX [IX_tblBOrder__sintOrderChannelID_sdtmOrdCreated_INCL]
ON [dbo].[tblBorder]
([sintOrderChannelID] asc,[sdtmOrdCreated] asc)
INCLUDE ([decBCShipFinal],[strOrderKey])
WITH FILLFACTOR = 100
CREATE NONCLUSTERED INDEX [IX_tblBOrder_strBxOrderNo_sdtmOrdCreated_incl]
ON [dbo].[tblBorder] ([strBxOrderNo] asc,[strOrderKey],[decBCShipFinal],[decBCShipTax])
结论
我在LIVE系统上应用了我的索引,并更新了我的存储过程以使用SMALLDATETIME,以便匹配数据库中涉及的列的数据类型.
这正是我想要它的方式.
我认为在这种情况下查询优化器做了很好的工作,以获得两个环境上最好的查询计划,我很高兴我没有添加任何查询提示.
我通过发布的3个答案了解到了.
感谢Max Vernon,Paul White和Daniel Hutmacher的回答.
解决方法
如果您希望查询优化器获得良好的结果,那么请注意数据类型.
您的变量输入为datetime2：
DECLARE @OrderStartDate datetime2 = '27 feb 2016'; DECLARE @OrderEndDate datetime2 = '28 feb 2016';
但是这些被比较的列是typeld smalldatetime(正如sdtm前缀所示！)：
[sdtmOrdCreated] SMALLDATETIME NOT NULL
类型不兼容使得优化器很难通过类型转换计算得到的基数估计值,如执行计划xml中所示：
<ScalarOperator ScalarString="GetRangeWithMismatchedTypes([@OrderStartDate],NULL,(22))"> <ScalarOperator ScalarString="GetRangeWithMismatchedTypes(NULL,[@OrderEndDate],(10))">
目前的估计可能准确也可能不准确(可能不准确).修复类型不兼容可能会也可能不会完全解决您的计划选择问题,但这是我在深入研究问题之前要解决的第一个问题(简单！)：
DECLARE @OrderStartDate smalldatetime = CONVERT(smalldatetime,'20160227',112); DECLARE @OrderEndDate smalldatetime = CONVERT(smalldatetime,'20160228',112);
在决定重写查询或使用提示之前,请务必检查基数估计的准确性以及任何差异的原因.
有关动态搜索的更多详细信息,请参阅我的sqlblog.com文章“Dynamic Seeks and Hidden Implicit Conversions”.
