Hello, I have a store with Products and Sub-Products. The following query below uses two select statements because it needs to find "matching products" OR "the parent of matching sub-products".Unfortunately, this query is painfully slow and usually timeout. Does anyone have any ideas that would return the same results, but improve speed.One thing I noticed it that removing the ORDER BY speeds it up quite a bit. Please me know if you have any ideas.Here is the query:SELECT TOP 50 A.idProduct, A.sku, A.description, A.price, A.listHidden, A.listPrice, A.serviceSpec, A.bToBPrice, A.smallImageUrl, A.noprices, A.stock, A.noStock, A.pcprod_HideBTOPrice, A.pcProd_BackOrder, A.FormQuantity, A.pcProd_BackOrder, A.pcProd_BTODefaultPrice, A.sDesc FROM products A WHERE idProduct IN ( SELECT TOP 50 idProduct FROM categories_products, categories WHERE idProduct = A.idProduct AND ((A.serviceSpec<>0 AND A.pcProd_BTODefaultPrice>=0 And A.pcProd_BTODefaultPrice<=999999999) OR (A.serviceSpec=0 AND A.configOnly=0 AND A.price>=0 And A.price<=999999999)) AND A.active=-1 AND A.removed=0 AND categories.idCategory=categories_products.idCategory AND categories.iBTOhide=0 AND categories.pccats_RetailHide=0 AND idSupplier=10 AND ((((content1 LIKE '%apparel%') OR (content2 LIKE '%apparel%') OR (content3 LIKE '%apparel%'))) OR ((A.details LIKE '%apparel%') OR (A.description LIKE '%apparel%') OR (A.sDesc LIKE '%apparel%') OR (SKU LIKE '%apparel%')))) OR idProduct IN ( SELECT B.pcProd_ParentPrd FROM products B WHERE pcProd_ParentPrd = A.idProduct AND ((B.serviceSpec=0 AND B.configOnly=0 AND B.price>=0 And B.price<=999999999)) AND B.active=0 AND B.pcProd_SPInActive=0 AND B.pcProd_ParentPrd>0 AND B.removed=0 AND idSupplier=10 AND ((((content1 LIKE '%apparel%') OR (content2 LIKE '%apparel%') OR (content3 LIKE '%apparel%'))) OR ((B.details LIKE '%apparel%') OR (B.description LIKE '%apparel%') OR (B.sDesc LIKE '%apparel%') OR (SKU LIKE '%apparel%'))) ) ORDER BY A.sku ASC, A.idproduct DESC
Query 100% Slow. Any Recommendations?
Posted - 2008-06-30 : 11:33:11
