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.