SQL Tips by Namwar Rizvi

May 1, 2007

Improving Query performance which contains "OR"

Filed under: Uncategorized — namwar @ 9:48 PM

I have noticed quite a few times that people overlook the performance issues related to using “OR” operators in their SQL queries.

Suppose you have a following query

Select ProductId, Name,Description from tblProducts
where
Name
=‘Keyed Washer’ OR Description =‘Assembly Item’

and the only index you have is on ‘Name’ column. In this scenario, SQL Server will not use any index and will perform table scan which may result in poor query performance depending on the size of table.

Above query can be optimized by rewriting it as follows:

Select ProductId, Name,Description from tblProducts
where Name=‘Keyed Washer’
Union All
Select ProductId, Name,Description from tblProducts
where Description =‘Assembly Item’

Note the usage of “Union All” here. This query has following benefits:

1. Since these are two independent queries joined together by Union All operator therefore, SQL Server will use index in first query at least.

2. Union All just combines two queries and does not skip any duplicate row so it will give exactly the same number of rows which you were getting before but with improved system response.

3. Future query tuning is easy because of modular approach in the rewritten query. You can add other indexes to improve performance as and when necessary.



Advertisements

1 Comment »

  1. I’m sorry but

    “2. Union All just combines two queries and does not skip any duplicate row so it will give exactly the same number of rows which you were getting before but with improved system response.”

    is just not true. UNION ALL is not functionally equivalent to the OR operator, although it may seem so.

    Please compare the result sets in this example:

    use AdventureWorks

    select ContactID
    ,Title
    ,FirstName
    ,LastName
    from Person.Contact
    where (Title = ‘Mr.’ or LastName = ‘Adams’)
    order by ContactID

    select ContactID
    ,Title
    ,FirstName
    ,LastName
    from Person.Contact
    where (Title = ‘Mr.’)
    union all
    select ContactID
    ,Title
    ,FirstName
    ,LastName
    from Person.Contact
    where (LastName = ‘Adams’)
    order by ContactID

    Actually, it’s UNION (without ALL) that will behave that way – provided that the conditions have been properly declared.

    In fact, in SQL Server the processing of OR conditions stops as soon as one of the conditions is true (the other is not even checked to improve performance). This of course does not mean that if both conditions were true the result would include duplicate rows.

    ML

    Comment by Matija Lah — May 9, 2007 @ 1:43 PM | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: