Post reply

Case Statement

  • September 28, 2003 at 5:10 am

    #82604

    I am trying to convert some dynamic sql into static by using 'CASE' to determine a column to use in my WHERE statement. The query is very long and complicated. If I don't use the Case statement, this part of the WHERE clause (for one possibility) is:

    AND tblImageCountryJoin.SSOffice223 = 0

    If I substitute the CASE statement, I have:

    AND Case @CountryID when 223 then tblImageCountryJoin.SSOffice223 when 115 then tblImageCountryJoin.SSOffice115 else 0 end = 0

    When I run the query without the CASE statement the query runs in 2 seconds for a complicated procedure. If I insert the CASE statement it takes 8 seconds to run. When I look at the query plans they seem to be the same.

    I realize I could just call different procedures but I would prefer not to do that.

    I don't understand the significant penalty. Any suggestions appreciated.

    TIA, John

    Edited by - jmccary on 09/28/2003 05:16:09 AM

  • September 28, 2003 at 3:35 pm

    #476115

    If your query plans are identical then I assume they're both (clustered) index scans.

    I would suggest that the CASE statement is introducing additional CPU (try profiler to confirm or refute this). Without the CASE, SQL evaluates the critereon and goes through each row knowing exactly what it's looking for. With the CASE statement it has to do the CASE evaluation for each and every row that it examines.

    Cheers,

    - Mark


    Cheers,
    - Mark

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply