Changing a Variable modified query plan

[Gawaine79]

Hi I've got a query which shows and creates two different execution plans based on the value of a variable....the problem is that one is significantly slower than the other and it only happens with a particular value.

basically the query looks like this....
select
from tblA a
join tblB b
join tblC c
left outer join tblD d
where a.value = 1 and (b.value2 = 1 or b.value2 = 3)

typically when this query executes it's very fast

however when i change the a.value to lets say 2 rather than 1 it becomes extremely slow and the query plan changes.
it works fine for values 3, 4, 5, etc...just 2 for some reason switches the query plan.

it changes at the very beginning of the query plan...let's say if the faster query plan typically starts the query with a table scan on tblB based on the where clause and when i change the value to 2 it changes the plan to start with an index scan on tblA where a.value = 2. Note both plans contain the same elements however they're just in different positions... Also when i view the statistics the slower query plan (a.value = 2) has significantly more logical reads on tblA.

can anyone give me any suggestions on how to furthur diagnose/resolve this problem?

Thanks

 

[SQLDenis]

could it be that value 2 is 99% of the table and a table scan is done because the selectivity is not optimal in this case

run this

select count(*),value
from tblA
group by value
order by 1 desc

and see it 2 is at the top
since you have significantly more logical reads on tblA this should be the case



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Data distribution probably. How many 2's are in table a, compared to 3's 4's... and total number of rows?

Or maybe outdated/missing statistics...

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[Gawaine79]

1's 1938349
2's 73512
3's 347111
4's 114041
5's 108295

these 5 represent about 99% of the total data in the table.
statistics are run every week for us.

 

[vongrunt]

Index with 5+ distinct values on 2.5 million rows is not much selective.

In this case (no extra WHERE conditions on base table) I would either rewrite query to use derived tables (sometimes this help, sometimes not) or force table/clustered index scan with hint.

Btw. how frequently are you using this query? I hope not often...


------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[Gawaine79]

it's actually used very often..and there are other where conditions and derived tables (all conditions and derived tables are the same through out)...it usually runs in 1-2 seconds but when the value = 2 it can take up to a minute to run..usually around 40 seconds.

the index/selectivity would explain why the query is slower when it starts with the index scan on tblA ...how do i force it to do a table scan with hint...i tried a few like fast, force order and maxop to no avail..

 

[Gawaine79]

one thing that i forgot to mention is that the select is usually selecting the top 250 from this query...
at most selecting the top 1000

 

[vongrunt]

> one thing that i forgot to mention is that the select is usually selecting the top 250 from this query...

And ORDER BY is... ?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[Gawaine79]

not typically used

 

[vongrunt]

> ..how do i force it to do a table scan with hint...

WITH(INDEX(0)) after table name/alias.

Still... hints should be last measure available. If there are chances to rewrite query, do it.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]