Make OR query more streamlined

Question

I am trying to search by type customerType and I am having a little problem:

SELECT DISTINCT CustomerID, CustomerName, City, State, Zip FROM qrySearchFields WHERE CustomerID in (Select CustomerID from tblCustomerTypeLineItems Where CustomerTypeID = 241) 

takes less than a second, and it also performs the following actions:

 SELECT DISTINCT CustomerID, CustomerName, City, State, Zip FROM qrySearchFields WHERE CustomerID in (Select CustomerID from tblCustomerTypeLineItems Where CustomerTypeID = 240) 

But when I try to use OR to search for both types at once:

 SELECT DISTINCT CustomerID, CustomerName, City, State, Zip FROM qrySearchFields WHERE CustomerID in (Select CustomerID from tblCustomerTypeLineItems Where CustomerTypeID = 241) Or CustomerID in (Select CustomerID from tblCustomerTypeLineItems Where CustomerTypeID = 240) 

It takes about 40 seconds.

Is there a better way to do this or something that I am missing?
For more information, see Parent Question: Displaying Query Results Horizontal

Answer 1

Why not restructure your OR query as follows:

 SELECT DISTINCT CustomerID, CustomerName, City, State, Zip FROM qrySearchFields WHERE CustomerID IN (SELECT CustomerID FROM tblCustomerTypeLineItems WHERE CustomerTypeID IN (241, 240)) 

If you are using SQL Server 2005 and above, you can use Common Table Expression (CTE):

 WITH cteCustomerId AS ( SELECT CustomerID FROM tblCustomerTypeLineItems WHERE CustomerTypeID IN (241, 240) ) SELECT DISTINCT CustomerID, CustomerName, City, State, Zip FROM qrySearchFields WHERE CustomerID IN (SELECT CustomerID FROM cteCustomerId); 

Answer 2

Why use OR in an external query?

Try the following:

 SELECT DISTINCT CustomerID, CustomerName, City, State, Zip FROM qrySearchFields WHERE CustomerID IN ( SELECT CustomerID FROM tblCustomerTypeLineItems WHERE CustomerTypeID IN (240, 241) ) 

Answer 3

He has to do a lot more work due to two SELECT IN statements; much more efficient:

 SELECT DISTINCT CustomerID, CustomerName, City, State, Zip FROM qrySearchFields WHERE CustomerID in (Select CustomerID from tblCustomerTypeLineItems Where CustomerTypeID IN (240,241)) 

Answer 4

2 subqueries are probably quite expensive. Try this version:

 SELECT DISTINCT sf.CustomerID, sf.CustomerName, sf.City, sf.State, sf.Zip FROM qrySearchFields sf INNER JOIN tblCustomerTypeLineItems ctli ON sf.CustomerID = ctli.CustomerID AND ctli.CustomerTypeID IN (240,241)