T-SQL Where Clause Case Statement Optimization (optional parameters to StoredProc)

Question

I've been battling this one for a while now. I have a stored proc that takes in 3 parameters that are used to filter. If a specific value is passed in, I want to filter on that. If -1 is passed in, give me all.

I've tried it the following two ways:

First way:

SELECT field1, field2...etc  
FROM my_view  
WHERE  
parm1 = CASE WHEN @PARM1= -1 THEN parm1  ELSE @PARM1 END  
AND parm2 = CASE WHEN @PARM2 = -1 THEN parm2  ELSE @PARM2 END  
AND parm3 = CASE WHEN @PARM3 = -1 THEN parm3  ELSE @PARM3 END

Second Way:

SELECT field1, field2...etc  
FROM my_view  
WHERE  
(@PARM1 = -1 OR parm1 = @PARM1)  
AND (@PARM2 = -1 OR parm2 = @PARM2)  
AND (@PARM3 = -1 OR parm3 = @PARM3)  

I read somewhere that the second way will short circuit and never eval the second part if true. My DBA said it forces a table scan. I have not verified this, but it seems to run slower on some cases.

The main table that this view selects from has somewhere around 1.5 million records, and the view proceeds to join on about 15 other tables to gather a bunch of other information.

Both of these methods are slow...taking me from instant to anywhere from 2-40 seconds, which in my situation is completely unacceptable.

Is there a better way that doesn't involve breaking it down into each separate case of specific vs -1 ?

Any help is appreciated. Thanks.

Answer 1

I read somewhere that the second way will short circuit and never eval the second part if true. My DBA said it forces a table scan.

You read wrong; it will not short circuit. Your DBA is right; it will not play well with the query optimizer and likely force a table scan.

The first option is about as good as it gets. Your options to improve things are dynamic sql or a long stored procedure with every possible combination of filter columns so you get independent query plans. You might also try using the "WITH RECOMPILE" option, but I don't think it will help you.