Making an OR query more streamlined

I'm trying to do a search by customerType, and I'm running into a small 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 to run, and so does:

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 an OR to look 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'm missing?

For more background, see the parent question: Displaying Query Results Horizontally

---

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 could use a 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);

---

Why using OR in the outer query?

Try this:

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

---

It's having to do a lot more work because of the two SELECT IN statements; much more efficient to do:

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

---

The 2 subqueries are probably fairly expensive. Give this version a try:

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)