6

So I have this query that I'm trying to tune and am running into an issue that I'm having trouble understanding. First of this is the query I'm using.

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

When I run this query the execution plan I get looks like this.

Scan

So the query is doing a very large scan on this large table returning 30 million rows just to pare it down to 3K. The index it's using looks like this.

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

I altered to query in a way that gives it much better performance by moving the date portion of the predicate outside of the initial query like so.

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

This resulted in much better performance and a query plan that is obviously superior.

BetterScan

The index it's scanning now looks like this.

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])

So here's my question. With ImageDate being in this index why does having it in my query hurt performance so badly? Shouldn't having that predicate included make it easier to eliminate rows? What am I doing/thinking incorrectly?

  • 1
    In the second query, it's not directly using the ImageDate value, is it? The outer where clause is going to operate on CONVERT(VARCHAR(10),im.[ImageDate],101) AS ImageDate instead of the native date/datetime/datetime2 data type. – billinkc Jun 23 '15 at 15:49
  • What happens if you change CONVERT(VARCHAR(10),im.[ImageDate],101) AS ImageDate to CONVERT(DATE,im.[ImageDate]) AS ImageDate? Converting to a string ranks among the worst ways to strip time from a datetime. – Aaron Bertrand Jun 23 '15 at 15:54
  • 2
    In the first query what plan do you get if you try with the results of the expression? i.e. [ImageDate] BETWEEN '2015-04-25' AND '2015-05-30'? There is a cardinality estimation bug with DATEDIFF that may be playing a part? – Martin Smith Jun 23 '15 at 16:28
  • @MartinSmith that's it! – Zane Jun 23 '15 at 16:46
  • So if you use OPTION (QUERYTRACEON 4199) on the original query does the problem disappear? – Martin Smith Jun 23 '15 at 16:47
4

Your query is looking for values between 2015-04-25 AND 2015-05-30.

Of which it looks like you have about 30 million.

Unfortunately there is a bug with the cardinality estimations where DATEDIFF is involved where the components get reversed.

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

Returns a range of 1784-05-15 to 1784-06-19. Extremely likely you have no dates in that range in the table (or statistics histogram) so SQL Server will estimate that no rows will be returned out of the scan, and certainly no need for 30 million lookups.

This is a fixed bug but requires trace flag 4199 enabled.

If that isn't possible you could try rephrasing the predicate in a different way that avoids the use of DATEDIFF or assigning the values to variables and using OPTION (RECOMPILE)

-1

ImageDate in the index is not used because your condition over ImageDate concerns a function ("DATEADD"). In general systems do not use indexes over attributes if the condition contains a function which is evaluated at query execution time.

  • 3
    This is valid when the function is used on the indexed column, which is not the case – Lamak Jun 23 '15 at 15:46
  • Yes, but in this case the value of the interval is not know by the optimizer, no? In any case it should be simple to try to substitute two constants in the BETWEEN and ask the access plan. – Renzo Jun 23 '15 at 15:48
  • 2
    If my recolection serves me correctly GETDATE() functions are calculated prior to runtime and therefore this does not apply. Also it is used in the first query and the Image date is used in that one. – Zane Jun 23 '15 at 15:51
  • Could you try the test by using two constants? Optimizers in general follows complex rules and it is not very easy to predict their behaviour. – Renzo Jun 23 '15 at 15:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.