SQL IN clause causing slow performance

May 4th, 2009 — ¥ong¥s

As some of you aware, SQL IN clause is known as “slow” operator. This is very true when you come to big number of keys to search in the IN clause. E.g.

SELECT XXX
FROM Sales
WHERE CustomerID IN (SELECT CustomerID FROM @SearchCustomer)

** @SearchCustomer is a table variable consist of all the customerid (e.g. 20,000 IDs).

To accomplish the same result, we can always write it as:

SELECT XXX
FROM Sales S
INNER JOIN @SearchCustomer SC
ON S.CustomerID = SC.CustomerID

The result can be improved significantly. For my case, old query that take 5 minutes to execute can be done in 20 seconds with the new query.

Posted in MS SQL.

2 Responses to “SQL IN clause causing slow performance”

  1. Neha Says:
    September 24th, 2010 at 6:05 pm

    Try this and you will see results are not same -
    select abc from big
    1
    0
    2
    2
    4
    3
    select abc from small
    1
    3
    3
    5
    2
    2
    select * from big where abc in (select abc from small)
    select * from big b inner join small s on b.abc = s.abc

  2. Atheeh Says:
    October 14th, 2010 at 9:50 pm

    Insted of join can we use Exists , will it give better perf than inner join

Leave a Reply

« SQL Server Reporting Services 2005 report parameter auto Postback Issue!

Proclarity standard - The selected page could not be opened because the cube could not be found »