Simple Query, but so slow. Any Advice for me?
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:
Code:
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
Try this ... This might be equivalent to ur Query
Also ... I assume that content1, content2, content3 are not from Products table otherwise u should think of moving them outside ....
Please do post the count of rows and table structure for tables when u do post a Query. It helps for analysis .....
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
((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 (A.details LIKE '%apparel%'
OR A.description LIKE '%apparel%'
OR A.sDesc LIKE '%apparel%' SKU LIKE '%apparel%')
idProduct IN (
SELECT TOP 50 idProduct
FROM categories_products, categories
WHERE
AND categories.idCategory=categories_products.idCatego ry
AND categories.iBTOhide=0
AND categories.pccats_RetailHide=0
AND idSupplier=10
AND (
content1 LIKE '%apparel%'
OR content2 LIKE '%apparel%'
OR content3 LIKE '%apparel%' )
union
SELECT B.pcProd_ParentPrd
FROM products B
WHERE 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%'
)
) ORDER BY A.sku ASC, A.idproduct DESC