conditional variable order - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

conditional variable order

Author

Topic

gsnk
Starting Member

24 Posts

Posted - 2005-12-12 : 11:14:22

I've tried to find a good answer to this question -- without avail.

Consider this (simplified) query:

SELECT *
FROM table t
WHERE (@incoming IS NULL OR t.field = @incoming)

I understand that writing something like
WHERE @incoming = t.field
is VERY bad since any existing index will be thrown out since the db doesn't know what to do with the variable.

Is the same true for the statement above if we "only" check for the existence of the variable before trying the same on the field itself. My impression -- though uneducated -- has been that by checking the existence of the variable first we can avoid having to check the field at all, thus, speeding up the query.

How far off am I in that assumption?

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2005-12-12 : 13:41:44

I'm not sure I understand what you are implying regarding the indexes not being used. The optimizer has the variable in place when it compiles the statement and so will definitely use the indexes. The whole point of using stored procedures with variables is that they can be pre-compiled and cached instead of having to have a different query plan for each possible value of the variable.

However, t.field = @incoming becomes a problem when the value for @incoming is NULL.

The phrase as you write it is correct as it will first check if the parameter is NULL, and if not then it will do the compare to the value in the field. You can also do the same using dynamic sql instead so that there is no where clause at all if the value is NULL.

gsnk
Starting Member

24 Posts

Posted - 2005-12-12 : 14:03:36

quote:
I'm not sure I understand what you are implying regarding the indexes not being used. The optimizer has the variable in place when it compiles the statement and so will definitely use the indexes.

Our DBA told me -- after having seen the above-mentioned conditional -- that the optimizer will not use any index on the table if the variable comes first (as in 'where @var = field') and happily perform a table scan each time. However he was unsure if that would apply to the 'where @var is null' check as well.

a. WHERE @var = field --<< bad
b. WHERE field = @var --<< good
c. WHERE @var is null or field = @var --<< good or bad??
d. WHERE field = COALESCE(@var, field) --<< how about that solution??

in regards to d. doesn't 'WHERE field = field (if @var is null) degrade performance??

quote:
However, t.field = @incoming becomes a problem when the value for @incoming is NULL.

hence the check for the null variable.

quote:
You can also do the same using dynamic sql instead so that there is no where clause at all if the value is NULL.

not possible in my case -- aside from the fact that I don't like dealing with dynamic sql code. it's messy (most of the times) and hard to maintain.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-12 : 14:32:25

Bottom line is for good optimization you should look at the execution plan for all those versions and see how the optimizer is dealing with each variation given your tables, statistics, index selectivity etc.

Obviously, the optimizer will have the best chance at coming up with a good plan for a simple, short, sweet statement. A couple of alternatives to dynamic sql could be control of flow code blocks and nested procedures.

Be One with the Optimizer
TG

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-12-12 : 14:50:25

The best way to answer the question of whether this will use an index is to just test it yourself by looking at the query plan in Query Analyzer. Try all the differntt versions of the query, and look at the query plans. Maybe the DBA is correct and meybe he isn't, but it is within your power to test.

declare @incoming varchar(50)
set @incoming = 'somevalue'

select * from MYTable
where @incoming = r.field

select * from MYTable
where r.field = @incoming

select * from MYTable
where (@incoming IS NULL OR t.field = @incoming)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources