April 3, 2014 at 3:27 am
I have a view which takes date as one of its parameters and a list of 1000 codes passed with IN clause. This view runs slow. Takes close to 40 seconds.
If i pass a date variable as parameter to date column the query runs in less than 2 secs.
The view is very simple. Below is the query in the view.
select a.date, a.code, a.value, b.isvalid
from table1 a left join table2 b
on a.date= b.date and a.code= b.code
where a.date = '2014-02-03' and code IN (1000 codes passed)
Below query runs slow
select a.date, a.code, a.value, b.isvalid
from table1 a left join table2 b
on a.date= b.date and a.code= b.code
where a.date = '2014-02-03' and code IN (1000 codes passed)
This one runs faster.
declare @date datetime
set @date = '2014-02-03'
select a.date, a.code, a.value, b.isvalid
from table1 a left join table2 b
on a.date= b.date and a.code= b.code
where a.date = @date and code IN (1000 codes passed)
Table structure.
table1 - 3 columns. date, code, value
index - nonclustered on date, code
table2 - 4 columns. date, code, isvalid, isrequired
index - nonclustered on date, code
Table1 has 400million records and table2 relatively lesser.
Tried to run query as below to reproduce the query plan but of no help. This still runs longer.
select a.date, a.code, a.value, b.isvalid
from table1 a left join table2 b
on a.date= b.date and a.code= b.code OPTION (RECOMPILE)
Can somebody help?
Note - Modifying indexes does not help.
April 3, 2014 at 3:34 am
My guess is that when using the date parameter, there isn't an implicit conversion and the index is used.
Just a guess though 🙂
You should take a look at the query plans and see where they are different.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 3, 2014 at 3:39 am
The indexes are appropriate. I looked at the query plan in one of the other env where there is relatively lesser data. It seems to do an index seek on both the tables.
April 3, 2014 at 3:43 am
suba.sathyanathan 40131 (4/3/2014)
The indexes are appropriate. I looked at the query plan in one of the other env where there is relatively lesser data. It seems to do an index seek on both the tables.
If the longest query takes 40 seconds, you can still view the actual execution plan. Just go grab a cup of coffee 🙂
The query plan from the other environment might be different because of statistics or other reasons.
Regarding the indexes: you might want to include the columns from your SELECT list (as INCLUDE, they don't need to be part of the index key).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 3, 2014 at 3:48 am
There is the issue. I cant look at the execution plan in prod env. Restoring prod backup to non-prod envs involve lengthy process. 🙁
April 3, 2014 at 10:16 am
To help you we need to see the execution plan (actual), table definitions and index definitions. Otherwise we're just guessing.
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 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply