主页 / dba / 问题 / 104890
Accepted
Zane
Asked: 2015-06-24 07:35:22 +0800 CST

谓词不会导致预期的扫描

  • 772

所以我有这个查询，我正在尝试调整它，但遇到了一个我无法理解的问题。首先是我正在使用的查询。

SELECT  
    si.LoanNbr AS [LoanNumber],
    fi.[SvcClientNbr] AS ClientID,
    si.LoanMasterID,
    si.LoanSrcCode AS [LoanSourceCode],
    fi.LoanPurpCode,
    fi.[PropState] AS [Property State],
    im.ImagedocumentID AS [Image Document ID],
    -- im.requestID AS [Request ID],
    CONVERT(VARCHAR(10),im.[ImageDate],101) AS ImageDate,
    im.[PageCount],
    im.[SignatureInd]
FROM dbo.NotMybaseTable Si
INNER JOIN dbo.NotMyTableName fi
    ON si.LoanMasterID = fi.LoanMasterID
INNER JOIN [dbo].[ImagedDocument] im
    ON si.loanmasterid = im.loanmasterid
    AND im.[DocTypeCode] = '10112'
WHERE CASE WHEN si.loansrccode = 'CORE' AND  Im.[SignatureInd] IN ('Y') THEN 1
        WHEN si.FundingSysCode = 'LIS' and CASE WHEN si.loansrccode = 'CORE' THEN 0 ELSE 1 END = 1 THEN 1
    ELSE 0 END = 1
    AND [ImageDate] BETWEEN DATEADD(WK, DATEDIFF(WK, 0, GETDATE()) - 4, -30) AND DATEADD(WK, DATEDIFF(WK, 0, GETDATE()) - 4, 0) + 5

当我运行此查询时，我得到的执行计划如下所示。

扫描

所以查询正在对这个返回 3000 万行的大表进行非常大的扫描，只是为了将其减少到 3K。它使用的索引看起来像这样。

CREATE NONCLUSTERED INDEX [IX_ImageDoc_DocType] ON [dbo].[ImageDoc]
(
    [LoanMasterID] ASC,
    [ImageDate] ASC,
    [ImageDocType] ASC
)

我改变了查询方式，通过将谓词的日期部分移动到初始查询之外，从而提供更好的性能。

select
    *
from (
    SELECT  
        si.LoanNbr AS [LoanNumber],
        fi.[SvcClientNbr] AS ClientID,
        si.LoanMasterID,
        si.LoanSrcCode AS [LoanSourceCode],
        fi.LoanPurpCode,
        fi.[PropState] AS [Property State],
        im.ImagedocumentID AS [Image Document ID],
        CONVERT(VARCHAR(10),im.[ImageDate],101) AS ImageDate,
        im.[PageCount],
        im.[SignatureInd]  
    FROM dbo.NotMybaseTable Si
    INNER JOIN dbo.NotMyTableName fi
      ON si.LoanMasterID = fi.LoanMasterID
    INNER JOIN [dbo].[ImagedDocument] im
      ON si.loanmasterid = im.loanmasterid
      AND im.[DocTypeCode] = '10112'
    WHERE CASE WHEN si.loansrccode = 'CORE' AND  Im.[SignatureInd] IN ('Y') THEN 1
            WHEN si.FundingSysCode = 'LIS' and CASE WHEN si.loansrccode = 'CORE' THEN 0 ELSE 1 END = 1 THEN 1
        ELSE 0 END = 1
        ) as F
WHERE [ImageDate] BETWEEN DATEADD(WK, DATEDIFF(WK, 0, GETDATE()) - 4, -30) AND DATEADD(WK, DATEDIFF(WK, 0, GETDATE()) - 4, 0) + 5

这带来了更好的性能和明显更优的查询计划。

更好扫描

它正在扫描的索引现在看起来像这样。

CREATE NONCLUSTERED INDEX [IX_ImageLoanDoc_SystemName_ICMPDocTypeCode] ON [dbo].[ImageDoc]
(
    [SystemName] ASC,
    [ICMPDocTypeCode] ASC,
    [LoanMasterID] ASC,
    [ImageDate] ASC
)
INCLUDE (   [ImageDocumentID],
    [ImageDocType],
    [BatchName],
    [SignatureInd],
    [ICMPDocCategoryCode],
    [ICMPDocSubTypeCode])

所以这是我的问题。由于 ImageDate 在此索引中，为什么在我的查询中包含它会严重损害性能？包含该谓词难道不应该更容易消除行吗？我在做什么/想错了什么？

sql-server sql-server-2012

2 个回答

  1. Best Answer

    您的查询正在查找2015-04-25AND之间的值2015-05-30。

    其中看起来你有大约 3000 万。

    不幸的是，基数估计存在一个错误DATEDIFF，其中涉及组件被逆转的地方。

    SELECT DATEADD(WK, DATEDIFF(WK, GETDATE(), 0) - 4, -30) ,  
       DATEADD(WK, DATEDIFF(WK, GETDATE(), 0) - 4, 0) + 5

    返回范围1784-05-15到1784-06-19。极有可能您在表（或统计直方图）中没有该范围内的日期，因此 SQL Server 将估计不会从扫描中返回任何行，当然也不需要进行 3000 万次查找。

    这是一个已修复的错误，但需要启用跟踪标志 4199 。

    如果那不可能，您可以尝试以不同的方式重新表述谓词，避免使用DATEDIFF或将值分配给变量并使用OPTION (RECOMPILE)

    • 4

  2. 未使用索引中的 ImageDate，因为您对 ImageDate 的条件涉及函数（“DATEADD”）。通常，如果条件包含在查询执行时评估的函数，则系统不会对属性使用索引。

    • -1

相关问题