January 2, 2008 at 2:03 pm
I'm have a query that calls a view and the data is filtered by a where clause that uses a date range.
When I provide the actual date in the where clause the query runs in approx. 1 second. When I declare an @Startdate and @EndDate variable and assign the same date range the query takes approx. 7 seconds.
When I run the Execution Plan I notice a lot of Parrallelism. What also strikes me as odd is when I Show Statics for this query the Scan Count and Logical Reads are much smaller with the longer running query. (I mean thousands and 10 thousands less.) Below are samples of what I am doing:
-------- Without Variables 1 Sec runtime -------------
SELECT ClosedDate,Order,Commission
FROM v_View
Where ClosedDate between '2007-11-01 00:00:00' and '2007-12-31 23:59:59'
-------- With Variables 7 Sec runtime -------------
Declare @StartDate datetime,@EndDate Datetime
SET @StartDate = '2007-11-01 00:00:00'
SET @EndDate = '2007-12-31 23:59:59'
SELECT ClosedDate,Order,Commission
FROM v_View
Where ClosedDate between @StartDate and @EndDate
Any ideas what could be the cause?
And should I look into correcting the Parallelism in this query?
TIA.
January 2, 2008 at 2:18 pm
As a quick test you could use the maxdop option in the query itself OPTION (MAXDOP 1) will use one processor, maybe the parelism is the culprit.
January 2, 2008 at 2:36 pm
Thanks for the reply.
I had to figure out how to use the option first 🙂
That worked. It resulted in a query of about 1 sec again.
Thanks!
Now my question is.....how to avoid parallelism without using the MaxDOP hint.
I guess I'll have to dig around SSC for some performance tuning articles.
January 3, 2008 at 12:16 am
When you use a variable in the where clause, the query optimiser cannot see the value of that cariable at the time the query is compiled and optimised. Since the value is unknown, it's hard for the optimiser to estimate the number of rows that will be affected by the query. It estimates at 1/3 of the table.
If a constant or stored proc parameter is used, the optimiser does know the value and can optimise based on a fairly accurate knowledge of the number of rows that will be affected by the query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply