Using Case Conditions in WHERE clause

Is there an alternative for case statements?

"Anith Sen" wrote:

> >> Can i use case conditions in where clause?
>
> Yes, you can. What you have done is very reasonable. There are a variety of
> options, some of which can be found at: www.sommarskog.se/dyn-search.html
>
> >> does it affect the performance?
>
> It depends on a different factors and in many cases it may. You may want to
> test the various options out and see.
>
> --
> Anith
>
>
>
 >> Stay informed about: Using Case Conditions in WHERE clause 

> But in
>
> SELECT ...
> FROM ...
> WHERE @value = CASE @type
> WHEN 1 THEN indexedcol1
> ELSE indexedcol2
> END
>
>It could still use a plan where it tries both indexes (and will only
>access one of them at run time.)

but let's make the life easier for the optimizer:

if(@type = 1)
SELECT ...
FROM ...
WHERE @value = indexedcol1
else
SELECT ...
FROM ...
WHERE @value = indexedcol2

the chances you'll get a better plan could be higher. Makes sense?
 >> Stay informed about: Using Case Conditions in WHERE clause 

did you try it?
did you look at the execution plan? [estimated or actual]

iow, you could probably answer this question better yourself than
someone on a ng.

performance depends on your indexing, the number of rows in the table,
selectivity of the criteria used, etc.
throwing a case into the mix adds to this list the complexity of the
when expressions [are they scalar, like the example, or are they
exists(), etc.]

the case may not have anything to do with why it's slow, e.g. in the
given example, if you have millions of rows and only 2 values in this
column, even ... where type='order' ... will probably be slow.




HP wrote:
> Can i use case conditions in where clause? does it affect the performance?
>
> eg.
>
> select fldnames from table
> where type =
> case when @ctype = '1' then 'quote'
> else 'order'
> end
>
> Thanks
>
>
>
>
 >> Stay informed about: Using Case Conditions in WHERE clause 

Thanks

"Trey Walpole" wrote:

> did you try it?
> did you look at the execution plan? [estimated or actual]
>
> iow, you could probably answer this question better yourself than
> someone on a ng.
>
> performance depends on your indexing, the number of rows in the table,
> selectivity of the criteria used, etc.
> throwing a case into the mix adds to this list the complexity of the
> when expressions [are they scalar, like the example, or are they
> exists(), etc.]
>
> the case may not have anything to do with why it's slow, e.g. in the
> given example, if you have millions of rows and only 2 values in this
> column, even ... where type='order' ... will probably be slow.
>
>
>
>
> HP wrote:
> > Can i use case conditions in where clause? does it affect the performance?
> >
> > eg.
> >
> > select fldnames from table
> > where type =
> > case when @ctype = '1' then 'quote'
> > else 'order'
> > end
> >
> > Thanks
> >
> >
> >
> >
>
 >> Stay informed about: Using Case Conditions in WHERE clause 

> Is there an alternative for case statements?

Sure, one trick I learned here and have adopted is for things like positive
INTegers, you can help the optimizer choose a seek instead of a scan by
doing something like this:

CREATE PROCEDURE dbo.foo
@value INT = NULL
AS
BEGIN
SET NOCOUNT ON;

SELECT col1,col2,col3 FROM table
WHERE value >= COALESCE(@value, 0)
AND value <= COALESCE(@value, 2147483647);
END
GO

This way, if you have an index on the column 'value', you aren't hurt as
much by optional parameters, as opposed to this case, which is much more
likely to cause a scan and slow down your query:

WHERE value = COALESCE(@value, value);

...which is essentially shorthand for:

WHERE value = CASE WHEN @value IS NULL THEN value ELSE @value END;

Of course, when you have multiple ways of getting something done, you should
always test performance *in your environment* and *against your data.* It
is very difficult for any of us to give you a straight answer about which
way is faster unless we have a very comprehensive understanding of your
hardware, data model and usage.

I am not even clear exactly how you intend to use case in all scenarios, but
I will say that there is often an alternative, and I will state again that
there is no clear-cut answer that option a is better than option b in all
cases. This is something you learn over time, with experience, and by
testing every time you change anything.
 >> Stay informed about: Using Case Conditions in WHERE clause