由于 ORDER BY 子句导致 SQL 查询性能不佳
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2139980/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Bad performance of SQL query due to ORDER BY clause
提问by David
I have a query joining 4 tables with a lot of conditions in the WHERE clause. The query also includes ORDER BY clause on a numeric column. It takes 6 seconds to return which is too long and I need to speed it up. Surprisingly I found that if I remove the ORDER BY clause it takes 2 seconds. Why the order by makes so massive difference and how to optimize it? I am using SQL server 2005. Many thanks.
我有一个查询，在 WHERE 子句中有很多条件连接 4 个表。该查询还包括数字列上的 ORDER BY 子句。返回需要 6 秒，这太长了，我需要加快速度。令人惊讶的是，我发现如果删除 ORDER BY 子句需要 2 秒。为什么 order by 会产生如此巨大的差异以及如何优化它？我使用的是 SQL Server 2005。非常感谢。
I cannot confirm that the ORDER BY makes big difference since I am clearing the execution plan cache. However can you shed light at how to speed this up a little bit? The query is as follows (for simplicity there is "SELECT *" but I am only selecting the ones I need).
由于我正在清除执行计划缓存，因此我无法确认 ORDER BY 有很大的不同。但是，您能否解释一下如何加快速度？查询如下（为简单起见，有“SELECT *”但我只选择了我需要的那些）。
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
回答by Quassnoi
Why the order by makes so massive difference and how to optimize it?
为什么 order by 会产生如此巨大的差异以及如何优化它？
The ORDER BYneeds to sort the resultset which may take long if it's big.
在ORDER BY需要进行排序，如果是大可能需要很长时间的结果集。
To optimize it, you may need to index the tables properly.
要优化它，您可能需要正确索引表。
The index access path, however, has its drawbacks so it can even take longer.
然而，索引访问路径有其缺点，因此它甚至可能需要更长的时间。
If you have something other than equijoins in your query, or the ranged predicates (like <, >or BETWEEN, or GROUP BYclause), then the index used for ORDER BYmay prevent the other indexes from being used.
如果您的查询中有除 equijoins 或范围谓词（如<, >or BETWEEN, orGROUP BY子句）以外的其他内容，则用于的索引ORDER BY可能会阻止使用其他索引。
If you post the query, I'll probably be able to tell you how to optimize it.
如果您发布查询，我可能会告诉您如何优化它。
Update:
更新：
Rewrite the query:
重写查询：
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 relation View_Product_Joined, as the name suggests, is probably a view.
View_Product_Joined顾名思义，这个关系可能是一个视图。
Could you please post its definition?
你能把它的定义贴出来吗？
If it is indexable, you may benefit from creating an index on View_Product_Joined (SiteName, Published, DocumentCulture, SKUEnabled, NodeOrder).
如果它是可索引的，您可能会受益于在 上创建索引View_Product_Joined (SiteName, Published, DocumentCulture, SKUEnabled, NodeOrder)。
