Slow execution in dynamic SQL with OR clause

  • I have a stored procedure which accepts parameters and then builds the query and WHERE clause dynamically based on these parameters.

    The performance is fine but now I have added another LEFT JOIN to a third table - the LEFT JOIN does not decrease performance - what does decrease performance is the added

    OR clause on this table.

    For example:

    This section is fast:

    SET @SQLWhere = @SQLWhere + ' OR (Table1.Col1 =' + QUOTENAME(@Value,'''') + ')'

    But extending it like this makes it really slow:

    SET @SQLWhere = @SQLWhere + ' OR (Table1.Col1 = '+ QUOTENAME(@Value,'''') + ' OR Table2.Col1 = '+ QUOTENAME(@Value,'''')+')'

    The added OR section to Table2.Col1 slows this down from 4 seconds to over a minute.....

    any ideas on how I can improve this?

  • Probably missing indexes. OR requires different indexes to AND, without the query executes as a scan usually.

    http://sqlinthewild.co.za/index.php/2011/05/03/indexing-for-ors/

    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 2 posts - 1 through 1 (of 1 total)

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