February 4, 2014 at 6:26 am
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)
February 4, 2014 at 7:02 am
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
February 4, 2014 at 8:00 am
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.
February 4, 2014 at 8:06 am
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
February 4, 2014 at 9:32 am
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:
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply