Post reply

Question on SARGability

  • May 30, 2013 at 12:55 pm

    #293940

    I have a reporting project where the users can choose to filter on multiple field parameters. The filter can be a specific field, or "ANY" to not filter on that particular column.

    In a nutshell, my T-SQL to pull data is simplified down to what's pasted below. In reality, there are multiple joins involved and the filter fields aren't necessarily in the same tables.

    My question is whether the following T-SQL might be considered SARGable, or if there's a preferred best practice that might work better in this case. Some of these queries are in fairly long stored procedures so dynamic SQL isn't necessarily a good option.

    Thanks in advance for your thoughts,

    Andre Ranieri

    SELECT somefielda, somefieldb, somefieldc, somefieldd)

    FROM SomeTable

    WHERE somefielda = (CASE WHEN @filtera = 'ANY' THEN somefielda ELSE @filtera END) AND

    somefieldb = (CASE WHEN @filterb = 'ANY' THEN somefieldb ELSE @filterb END) AND

    somefieldc = (CASE WHEN @filterc = 'ANY' THEN somefieldc ELSE @filterc END) AND

    somefieldd = (CASE WHEN @filterd = 'ANY' THEN somefieldd ELSE @filterd END) AND

  • May 30, 2013 at 1:03 pm

    #1619833

    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

  • May 30, 2013 at 1:46 pm

    #1619868

    Thanks for the link.

    As I think about this, using OPTION (RECOMPILE) on the statement makes a lot of sense. You've got me thinking that this is perhaps not so much an issue with SARGability as it is with parameter sniffing.

    A pre compiled execution plan wouldn't know whether to optimize for ANY, which might return millions of rows, or a fairly restrictive parameter which would only return a few hundred. OPTION (RECOMPILE) on the statement might add 200ms to the execution time of the stored procedure but would potentially save a lot of I/O reads from the tables.

    Thanks,

    Andre Ranieri

  • May 30, 2013 at 1:54 pm

    #1619881

    Andre Ranieri (5/30/2013)

    You've got me thinking that this is perhaps not so much an issue with SARGability as it is with parameter sniffing.

    Correct, it's not. It's a mix of parameter sniffing and the optimiser's requirement to create a plan safe for reuse.

    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

Viewing 4 posts - 1 through 3 (of 3 total)

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