Poor SQL query performance due to ORDER BY clause

Question

I have a query joining 4 tables with a lot of conditions in a WHERE clause. The query also includes an ORDER BY clause in a numeric column. The return takes 6 seconds, which is too large, and I need to speed it up. Surprisingly, I found that if I delete the ORDER BY clause, it takes 2 seconds. Why does the order make such a huge difference and how to optimize it? I am using SQL Server 2005. Thank you very much.

I cannot confirm that ORDER BY matters a lot, as I am clearing the execution plan cache. However, can you shed some light on how to speed this up a bit? The query is as follows (for simplicity there is "SELECT *", but I only select the ones that I need).

SELECT * FROM View_Product_Joined j INNER JOIN [dbo].[OPR_PriceLookup] pl on pl.siteID = NodeSiteID and pl.skuid = j.skuid LEFT JOIN [dbo].[OPR_InventoryRules] irp on irp.ID = pl.SkuID and irp.InventoryRulesType = 'Product' LEFT JOIN [dbo].[OPR_InventoryRules] irs on irs.ID = pl.siteID and irs.InventoryRulesType = 'Store' WHERE (((((SiteName = N'EcommerceSite') AND (Published = 1)) AND (DocumentCulture = N'en-GB')) AND (NodeAliasPath LIKE N'/Products/Cats/Computers/Computer-servers/%')) AND ((NodeSKUID IS NOT NULL) AND (SKUEnabled = 1) AND pl.PriceLookupID in (select TOP 1 PriceLookupID from OPR_PriceLookup pl2 where pl.skuid = pl2.skuid and (pl2.RoleID = -1 or pl2.RoleId = 13) order by pl2.RoleID desc))) ORDER BY NodeOrder ASC 

Answer 1

Why does the order make such a huge difference and how to optimize it?

ORDER BY needs to sort a result set, which can take a lot of time if it is large.

To optimize it, you may need to index tables correctly.

However, the access path to the index has its drawbacks, so it may even take longer.

If you have something other than equijoins in your query, or range predicates (like < , > or BETWEEN or GROUP BY ), then the index used for ORDER BY may use other indexes.

If you post a request, I can probably tell you how to optimize it.

Update:

Record the request:

 SELECT * FROM View_Product_Joined j LEFT JOIN [dbo].[OPR_InventoryRules] irp ON irp.ID = j.skuid AND irp.InventoryRulesType = 'Product' LEFT JOIN [dbo].[OPR_InventoryRules] irs ON irs.ID = j.NodeSiteID AND irs.InventoryRulesType = 'Store' CROSS APPLY ( SELECT TOP 1 * FROM OPR_PriceLookup pl WHERE pl.siteID = j.NodeSiteID AND pl.skuid = j.skuid AND pl.RoleID IN (-1, 13) ORDER BY pl.RoleID desc ) pl WHERE SiteName = N'EcommerceSite' AND Published = 1 AND DocumentCulture = N'en-GB' AND NodeAliasPath LIKE N'/Products/Cats/Computers/Computer-servers/%' AND NodeSKUID IS NOT NULL AND SKUEnabled = 1 ORDER BY NodeOrder ASC 

The View_Product_Joined relationship, as the name suggests, is probably a view.

Could you post your definition?

If it's indexable, you can benefit from creating an index on View_Product_Joined (SiteName, Published, DocumentCulture, SKUEnabled, NodeOrder) .