Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
why poor performance involving OR condition with multiple tables

Answered why poor performance involving OR condition with multiple tables

  • Sunday, February 17, 2013 4:53 PM
     
     
    Sign In to Vote
    0

    I faced this issue:

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/e12a6c0c-4ab1-4844-95e7-730537a58b90

    Although not exactly same but similar kind of query having same issue:

    http://stackoverflow.com/questions/9983057/poor-performance-for-or-condition-involving-multiple-tables

    What is the reason for poor performance ?

    Mahesh

     

All Replies

  • Sunday, February 17, 2013 5:20 PM
     
     
    Sign In to Vote
    0
    As already said: Missing indices, poor or wrong design.. Have you already tested alternative queries like UNION's?
     
  • Sunday, February 17, 2013 8:41 PM
     
     
    Sign In to Vote
    0
    In other words SQL Server has to do full scans on the source table instead of using indexes and seeks the data.
     
  • Monday, February 18, 2013 12:46 AM
     
     
    Sign In to Vote
    0
    As already said: Missing indices, poor or wrong design.. Have you already tested alternative queries like UNION's?

    Yes Indexes are present main table on both columns; staging tables hold very few rows.

    Yes I tried other workaround options/UNION which worked well; I just wanted to understand why the plain logic don't work.

    Thanks

    Mahesh

     
  • Monday, February 18, 2013 12:52 AM
     
     
    Sign In to Vote
    0

    Yes Indexes are present main table on both columns; staging tables hold very few rows.


    SQL Server needs to use ONE index for whole query, so the fact that you have multiple indexes is not helping at all in such case. Using UNION allows to use different index for each query.
     
  • Monday, February 18, 2013 2:30 AM
     
     Answered
    Sign In to Vote
    1

    I think that the correct answer is that SQL Server could to a better job, but on the other hand, it is not a trivial task to estimate when using two indexes for the same query is a good idea. SQL Server is able to do that, but it just don't happen very often.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se