Case TSQL statement in WHERE clause causing scanning (ss2005)

Tim Wappat

Jan 24, 2014 • 1 min read

AND 1 = CASE
    WHEN @IncludeFreight='Y' THEN 1
    WHEN (ItemNumber NOT LIKE '9898%') AND (ItemNumber <> 'FREIGHT') THEN 1
    ELSE 0
    END

The above is an snippet from a “catch all” query, I uncovered it in one of our reporting SQL statements it, was declared “**WITH RECOMPILE”**as it should, on order to properly optomise, but the query was refusing to finish executing on the 4.5million rows in the table involved. It lots of IO wait states on our table.

It seems that the query optomiser just couldn’t handle the case in the where clause with parameters.

Changed it to the following and it executed in seconds.

AND ( @IncludeFreight='Y'
OR (@IncludeFreight='N' AND  ItemNumber != 'FREIGHT' AND ItemNumber NOT LIKE '9898%'))

Most of the theory behind this behaviour can be seen in this article: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/