由于 ORDER BY 子句导致 SQL 查询性能不佳 [英] Bad performance of SQL query due to ORDER BY clause

问题描述

我有一个查询，在 WHERE 子句中有很多条件连接 4 个表.该查询还包括数字列上的 ORDER BY 子句.返回需要 6 秒，这太长了，我需要加快速度.令人惊讶的是，我发现如果删除 ORDER BY 子句需要 2 秒.为什么 order by 会产生如此巨大的差异以及如何优化它?我使用的是 SQL Server 2005.非常感谢.

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.

我无法确认 ORDER BY 有很大的不同，因为我正在清除执行计划缓存.但是，您能否解释一下如何加快速度?查询如下(为简单起见，有SELECT *"但我只选择了我需要的那些).

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).

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

推荐答案

为什么 order by 会产生如此巨大的差异以及如何优化它?

Why the order by makes so massive difference and how to optimize it?

ORDER BY 需要对结果集进行排序，如果结果集很大，可能需要很长时间.

The ORDER BY needs to sort the resultset which may take long if it's big.

要优化它，您可能需要正确索引表.

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.

如果您的查询中有除 equijoins 或范围谓词(如 <、> 或 BETWEEN，或GROUP BY 子句)，那么用于 ORDER BY 的索引可能会阻止其他索引被使用.

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

如果您发布查询，我可能会告诉您如何优化它.

If you post the query, I'll probably be able to tell you how to optimize it.

更新:

重写查询:

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

关系View_Product_Joined，顾名思义，可能是一个视图.

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

你能把它的定义贴出来吗?

Could you please post its definition?

如果它是可索引的，您可能会受益于在 View_Product_Joined(SiteName、Published、DocumentCulture、SKUEnabled、NodeOrder) 上创建索引.

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

这篇关于由于 ORDER BY 子句导致 SQL 查询性能不佳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！