October 12, 2009 at 11:45 am
I have a query that if I use a variable in the Where clause it has a high number of reads however if I remove the parameter and use a specific value the reads are considerably lower. I am running this in EM. Any suggestions?
declare @To datetime, @From datetime
Set @To = '2009-10-09' Set @From = '2009-10-10'
---Large Number of reads
Select Order From Table
Where Logged between @To and @From
----Low Number of Reads
Select Order From Table
Where Logged between '2009-10-09' and '2009-10-10'
October 12, 2009 at 8:43 pm
Is logged a datetime or smalldatetime datatype?
What are the execution plans?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 13, 2009 at 7:01 am
Jack,
Thanks for the reply.
The datatype is datetime are not smalldatetime.
If I remove all references to the 'Active' Column on that table in the Large Read Query then the reads are almost the same as the Low Read Query. I've tried the query with and without an Index for the Active column and it doesn't seem to help.
I ran the execution plans. The large read query has a clustered index scan which has a cost of 94% then Parallelism at a cost of 6%. The low read query has a Index seek at 1% then a Bookmark Lookup at 99%. The difference is about 13,000 Reads and 400ms CPU.
Large Read Query:
declare @From datetime,@To datetime
SET @From = '2009-10-09'
SET @To = '2009-10-10'
select ORDER_ID,Logged from t_MTSTM_SHIPMENT_LOG
where ACTIVE = 1 and LOGTYPE_ID = 8648 and Logged between @From and @To
-------------
|--Parallelism(Gather Streams)
|--Clustered Index Scan(OBJECT:([MTS].[dbo].[t_MTSTM_SHIPMENT_LOG].[PK_T_SHIPMENT_LOG]), WHERE:((([t_MTSTM_SHIPMENT_LOG].[ACTIVE]=1 AND [t_MTSTM_SHIPMENT_LOG].[LOGTYPE_ID]=8648) AND [t_MTSTM_SHIPMENT_LOG].[LOGGED]>=[@From]) AND [t_MTSTM_SHIPMENT_LOG
Low Read Query:
select ORDER_ID,LOGGED from t_MTSTM_SHIPMENT_LOG
where ACTIVE = 1 and LOGTYPE_ID = 8648 and Logged between '2009-10-09' and '2009-10-10'
----------------------------------------------
|--Filter(WHERE:([t_MTSTM_SHIPMENT_LOG].[LOGTYPE_ID]=Convert([@2]) AND [t_MTSTM_SHIPMENT_LOG].[ACTIVE]=1))
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([MTS].[dbo].[t_MTSTM_SHIPMENT_LOG]) WITH PREFETCH)
|--Index Seek(OBJECT:([MTS].[dbo].[t_MTSTM_SHIPMENT_LOG].[IX_Logged]), SEEK:([t_MTSTM_SHIPMENT_LOG].[LOGGED] >= Convert([@3]) AND [t_MTSTM_SHIPMENT_LOG].[LOGGED] <= Convert([@4])) ORDERED FORWARD)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply