SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


tsql NOT using INDEX when parameter value in use


tsql NOT using INDEX when parameter value in use

Author
Message
Daniel Taylor-446457
Daniel Taylor-446457
SSC-Enthusiastic
SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)

Group: General Forum Members
Points: 134 Visits: 286
This is my first post so HELLO to everyone!

Hi All,

I am working on a sql 2005 Enterprise Edition DB using sp4.

I am seeing strange performance when using a parameter in part of my WHERE clause.
Looking at the Execution plan. The ‘parameter’ query appears NOT use a NON CLUSTERED INDEX on the RowUpdateTime. While the hardcoded value ‘02/02/2014’ does use the NCI when running the TSQL

I have included example sample code to see if anyone can point to anything obvious.
Is it the way im setting my ‘date’ value?

Any help would be great.
As you can imagine the performance difference is huge!
Thanks


--============================================
--Sample TSQL
--============================================


-- e.g.1 with NO parameter on RowUpdateDateTime (hardcoded)
--uses INDEX
SELECT *
FROM [LiveDB].[dbo].[LabSpecimenTests]
WHERE (SourceID = 'CCH')
AND ([RowUpdateDateTime] >= '02/02/2014')
AND (NOT [ResultDateTime] IS NULL)

--tsql e.g.2 with parameter
--Execution plan does NOT use index?

--declare variable
DECLARE @RefreshDate datetime
--set value
SET @RefreshDate = (SELECT CONVERT(DATETIME, '02/02/2014', 103))

SELECT *
FROM [LiveDB].[dbo].[LabSpecimenTests]
WHERE (SourceID = 'CCH')
AND ([RowUpdateDateTime] >= @RefreshDate)
AND (NOT [ResultDateTime] IS NULL)

--e.g.3 with basic parameter
--Execution plan does NOT use index?

--decalre variables
DECLARE @RefreshDate datetime
--set variables
SET @RefreshDate = '02/02/2014'
SELECT *
FROM [LiveDB].[dbo].[LabSpecimenTests]
WHERE (SourceID = 'CCH')
AND ([RowUpdateDateTime] >= @RefreshDate)
AND (NOT [ResultDateTime] IS NULL)
Lowell
Lowell
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71344 Visits: 40930
what is the definition of the index?
your query has three columns in the WHERE statement, is the index on all three columns?

since you are doing SELECT *, at a minimum, there's going to be a key lookup to get the other columns, so the optimizer might decide the cost is less to use the clustered index; more details on the existing indexes is needed i think.


WHERE (SourceID = 'CCH')
AND ([RowUpdateDateTime] >= @RefreshDate)
AND (NOT [ResultDateTime] IS NULL)



Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Daniel Taylor-446457
Daniel Taylor-446457
SSC-Enthusiastic
SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)

Group: General Forum Members
Points: 134 Visits: 286
Thanks for your help Lowell

The NCI is on ResultDateTime and doesn’t include any columns.
The CI is on the PK which is partly the SourceID (+ 2 more fields)

The bit that interests (or causes the greatest confusion! ;-) ) is why does the optimizer USE the Index consistently when the date is hardcoded. When I change to a Parameter it will NOT use the Index. It suggests to me that optimizer deals with parameter values different to hardcoded values. (this makes me want to check some other queries of mine eeek!)

From reading around I can see using the WITH HINT can force it to use an Index and this has helped significantly. (I appreciate this may not be the best way)

FROM [LiveDB].[dbo].[LabSpecimenTests] WITH (INDEX(NCI_LabSpecimenTests))
WHERE ([RowUpdateDateTime] >= @RefreshDate)
AND (NOT [ResultDateTime] IS NULL)


There is work that can be done to the indexes; unfortunately at present I cannot modify these.
Lowell
Lowell
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71344 Visits: 40930
for that specific query, that index is not a good match for a parameterized query

a new index, something like this would probably work immediately for your existing query and you wouldn't need to second guess the optimizer with hints

CREATE INDEX
IX_RowUpdateDateTimeSourceIDResultDateTime
ON LabSpecimenTests(RowUpdateDateTime,SourceID,ResultDateTime)



Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Daniel Taylor-446457
Daniel Taylor-446457
SSC-Enthusiastic
SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)

Group: General Forum Members
Points: 134 Visits: 286
Excellent.
I will endavour to modify the NCI and update the ticket if any issues.

It still interests me as to why such similar queries can perform so differently.
Who knew the optimizer would have this quirk... everybody apart form me Hehe
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search