Posted by Jared on 01/25/08 16:51

On Jan 24, 10:48 pm, Ed Murphy <emurph...@socal.rr.com> wrote:
> Jared wrote:
> > Consider the following two functionally identical example queries:
>
> > Query 1:
>
> > DECLARE @Name VARCHAR(32)
> > SET @Name = 'Bob'
> > SELECT * FROM Employees
> > WHERE [Name] = CASE WHEN @Name IS NULL THEN [Name] ELSE @Name END
>
> > Query 2:
>
> > SELECT * FROM Employees WHERE [Name] = 'Bob'
>
> > I would expect SQL Server to construct an identical QEP under the hood
> > for these two queries, and that they would require essentially the
> > same amount of time to execute.  However, Query 1 takes much longer to
> > run on my indexed table of ~300,000 rows.  By "longer", I mean that
> > Query 1 takes about two seconds, while Query 2 returns almost
> > instantly.
>
> > Is there a way to implement a conditional WHERE clause without
> > suffering this performance hit?  I want to avoid using the IF...THEN
> > method because I frequently require several optional parameters in the
> > WHERE clause.
>
> I would at least try the following:
>
> WHERE (@Name IS NULL OR [Name] = @Name)
>
> as well as
>
> WHERE NOT([Name] <> @Name)- Hide quoted text -
>
> - Show quoted text -

Tom van Stiphout wrote:
> "I don't know why you would expect the QEP to be the same. I wouldn't."

I misspoke. I didn't mean that the QEP would be identical step for
step, but rather that the performance would be pretty much the same.


Ed Murphy wrote:
> I would at least try the following:
> WHERE (@Name IS NULL OR [Name] = @Name)

This gives me the same performance as hard-coded values, which is to
say it returns almost instantly. I'm not sure why using CASE results
in a longer execution time, but at this point I don't really care.
=)

Thanks for your help!

[Back to original message]