Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

tsql NOT using INDEX when parameter value in use Expand / Collapse
Author
Message
Posted Tuesday, February 4, 2014 6:26 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 6:38 AM
Points: 73, Visits: 236
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)
Post #1537732
Posted Tuesday, February 4, 2014 7:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:30 AM
Points: 12,927, Visits: 32,325
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1537745
Posted Tuesday, February 4, 2014 8:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 6:38 AM
Points: 73, Visits: 236
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.
Post #1537790
Posted Tuesday, February 4, 2014 8:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:30 AM
Points: 12,927, Visits: 32,325
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1537795
Posted Tuesday, February 4, 2014 9:32 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 6:38 AM
Points: 73, Visits: 236
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
Post #1537833
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse