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.
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.
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?
CONVERT(VARCHAR(10),im.[ImageDate],101) AS ImageDate
instead of the native date/datetime/datetime2 data type.CONVERT(VARCHAR(10),im.[ImageDate],101) AS ImageDate
toCONVERT(DATE,im.[ImageDate]) AS ImageDate
? Converting to a string ranks among the worst ways to strip time from a datetime.[ImageDate] BETWEEN '2015-04-25' AND '2015-05-30'
? There is a cardinality estimation bug withDATEDIFF
that may be playing a part?OPTION (QUERYTRACEON 4199)
on the original query does the problem disappear?