In and Exist are both slow. Anything else?

[o1webdawg]

Hi everyone,

I posted here once before when my own research was not enough. I am hoping you can all help me again. In the following query I am trying to return all categories containing matching products and the # of products in each category. The query is accurate, but painfully slow. I have tried "IN" and "Exists" and both are slow. Does anyone see anything really obviously wrong that I have missed?

SELECT COUNT(categories.idcategory) AS ProductsInCategory, categories.idcategory, categories.categoryDesc 
FROM (categories_products INNER JOIN categories ON categories_products.idcategory=categories.idcategory) 
LEFT OUTER JOIN products as A ON A.idProduct=categories_products.idProduct 
WHERE ((A.serviceSpec<>0 AND A.pcProd_BTODefaultPrice>=0 And A.pcProd_BTODefaultPrice<=999999999) OR (A.serviceSpec=0 AND A.price>=0 And A.price<=999999999))
AND A.active=-1 
AND A.removed=0 
AND categories.iBTOhide=0 
AND categories.pccats_RetailHide=0 
AND configOnly=0 
AND A.idproduct IN (SELECT E.idproduct FROM pcSearchFields_Products E WHERE E.idSearchData=2135) 
AND A.idproduct IN (SELECT E.idproduct FROM pcSearchFields_Products E WHERE E.idSearchData=2869) 
GROUP BY categories.idcategory, categories.categoryDesc 
ORDER BY categories.idcategory;

[szlamany]

Is this MS SQL? Are you using a stored procedure?

If so I would get the product id's into a temp table first and then build the query from that temp table (or table variable if the number of rows is low enough)

My second consideration would be to somehow get those product id's first - by using something like a derived query.

Select ....

From (SELECT Distinct E.idproduct FROM pcSearchFields_Products E WHERE E.idSearchData in (2135,2869)) as "X"
Left Join ...

Notice how I'm getting the idproduct field as the FIRST FROM item - and it's a derived table named "X". And of course there was never a reason to do it twice - mine as well get all the id's in a single query.

Then you start JOIN'ing your other tables to this list of product id's.

[smeet.sinha]

SELECT COUNT(c.idcategory) AS ProductsInCategory, c.idcategory, c.categoryDesc
FROM categories_products
INNER JOIN categories c
ON categories_products.idcategory=c.idcategory
INNER JOIN pcSearchFields_Products pp1
ON ( A.idproduct = pp1.idproduct
and (pp1.idSearchData=2135 or pp1.idSearchData=2869)
)
LEFT OUTER JOIN products A
ON A.idProduct=categories_products.idProduct
WHERE ((A.serviceSpec<>0
AND A.pcProd_BTODefaultPrice>=0
And A.pcProd_BTODefaultPrice<=999999999)
OR (A.serviceSpec=0 AND A.price>=0 And A.price<=999999999))
AND A.active=-1
AND A.removed=0
AND c.iBTOhide=0
AND c.pccats_RetailHide=0
AND configOnly=0
GROUP BY c.idcategory, c.categoryDesc
ORDER BY c.idcategory;