由于ORDERBY子句导致SQL查询性能不佳-BadperformanceofSQLqueryduetoORDERBYclause

8  

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

为什么订单会产生如此巨大的差异以及如何优化它？

The ORDER BY needs 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 BY clause), then the index used for ORDER BY may prevent the other indexes from being used.

如果在查询中有除equijoins以外的东西，或者有远程谓词（如<，>或BETWEEN或GROUP 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）上创建索引中受益。