Best method of handling optional parameters in WHERE clause

  • Hi all,

    I'm trying to determine what the preferred method is for handling optional parameters in WHERE clauses from a theory point of view. I've come up with three different ways: LIKE, OR, and CASE. From searching the web, CASE seems to be the most common, but I'm not seeing any differences in CPU, disk, or table usage. Here's a test script I've run with different ways of handling optional parameters.

    DECLARE @SCVARCHAR(2)--Our optional parameter

    CREATE TABLE #OPTStates--Test table

    (

    StateCodeCHAR(2)

    ,StateNameVARCHAR(20)

    )

    --Now, fill the table with data

    INSERT INTO #OPTStates(StateCode, StateName)

    SELECT 'CA', 'California'

    UNIONSELECT 'AZ', 'Arizona'

    UNIONSELECT 'CO', 'Colorado'

    UNIONSELECT 'NM', 'New Mexico'

    UNIONSELECT 'NY', 'New York'

    --For the tests, set the optional parameter to either blank or a value by uncommenting one of the below lines

    --SET @SC = ''--All queries take the same amount of batch

    SET @SC = 'CO'--All queries take the same amount of batch

    --Test 1: LIKE

    SELECT * FROM #OPTStates

    WHERE StateCode LIKE LEFT(@SC + '%', 2)

    --Test 2: OR

    SELECT * FROM #OPTStates

    WHERE (@SC <= '' OR StateCode = @SC)

    --Test 3: CASE

    SELECT * FROM #OPTStates

    WHERE StateCode = (CASE WHEN @SC > '' THEN @SC ELSE StateCode END)

    --Now clean up

    DROP TABLE #OPTStates

    From a theory perspective, it would seem the CASE would be preferred, since it causes tautologies. However on a more complicated query I ran, the CASE took a larger cost relative to the batch, with the LIKE leading the OR.

    From how you've handled optional parameters, which method do you prefer? Is there another method that works even better?

    Thanks!

  • http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Nice to hear the question is asked in many other places. Your post was well-researched, and adds another solution: dynamic SQL. It also stresses the importance of a recompile to rebuild the plan cache. Maybe that's why I was getting query costs that seemed bizarre.

    For some tasks, dynamic seems like an overkill. I wonder if the answer for best would change for smaller tables, or whether you're doing numbers vs strings.

  • I think that you will find that dynamic SQL with sp_executesql almost always gives you the best performance.

    The more optional parameters you have, the more likely it is that there will be a big difference in performance over other catch-all methods.

  • That confirms it. In my code, I'll steer the work toward dynamic sql whenever I can. Thanks for all your help.

  • Can I wave the flag for separating out the logic completely, either in two distinct stored procedures (one for queries with the optional parameter, one without) or by use of an IF statement to direct the code to the appropriate query...

    However, the fact that no-one has recommended this makes me cautious - could anyone cast any light on why this approach has not been suggested please? (Need to learn if I am getting this wrong 😛 )

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • I thought of that too, and I think some of the hesitance of using it comes from the problem getting unmanageable. I would consider two select statements if I only had one optional parameter, but projects often grow, and two statements could turn into four. That always gets compounded by a developer just trying to get the job done by the end of the day and wanting the least-invasive change, regardless of efficiency.

    My opinion has always been that dynamic SQL should be reserved for more complex tasks, which explains my reluctance of using it, but my current boss loves it, so I should change.

    For what it's worth though, before I got the answers above, I managed to run some tests, and found that using CASE was the best method, with the OR being the next.

  • Case gives you table/index scans. Very consistent performance, but not good performance once you get to tables with more than a couple hundred rows. The OR method can use index scans (not seeks) with lookups resulting in very erratic performance.

    On SQL 2008 SP2 and above you can use the OR method with OPTION RECOMPILE for decent execution plans, the CASE will likely still scan.

    As for splitting the logic out, excellent idea if there's only one parameter, borderline if there are 2, near impossible over that. For 5 parameters you're looking at needing a huge number of different procedures for all the possible combinations of parameter passed or not passed.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (5/8/2013)


    As for splitting the logic out, excellent idea if there's only one parameter, borderline if there are 2, near impossible over that. For 5 parameters you're looking at needing a huge number of different procedures for all the possible combinations of parameter passed or not passed.

    Thanks Gail, and yes I agree that you might not want to split the queries out for multiple parameters because of the code maintenance aspect, but good to know that where it is practical to do it, it is an excellent idea 😛

    How do people feel about the OPTION(optimize for ...) hint?

    I have seen that "do good" in the past, where a suitable value can be optimised for, but is it a "be aware of what you are doing" or an "OMG... fool" usage pattern in your opinion?

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

    Viewing 9 posts - 1 through 8 (of 8 total)

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