INNER JOIN OR条件的查询优化

I have the following query that runs really slowly and I've used the estimated execution plan to narrow down the problem to the final INNER JOIN's OR condition.

我有以下查询运行得非常慢，我已经使用估计的执行计划将问题缩小到最终的INNER JOIN的OR条件。

SELECT 
    TableE.id
FROM 
    TableA WITH (NOLOCK)
    INNER JOIN TableB WITH (NOLOCK)
        ON TableA.[bid] = TableB.[id]
    LEFT JOIN TableC WITH (NOLOCK)
        ON TableB.[cid] = TableC.[id]
    LEFT JOIN TableD WITH (NOLOCK)
        ON TableA.[did] = TableD.[id]
    INNER JOIN TableE WITH (NOLOCK)
        ON TableD.[eid] = TableE.[id] 
            OR TableE.[numericCol] = TableB.[numericCol] -- commenting out this OR statement leads to large performance increase
WHERE 
    TableA.[id] = @Id

I have the following index on TableB:

我在TableB上有以下索引：

CREATE UNIQUE NONCLUSTERED INDEX [IX_TableB_numericCol_id] ON [dbo].[TableB] 
(
    [numericCol] ASC,
    [id] ASC
)

and on TableE:

在TableE上：

CREATE NONCLUSTERED INDEX [IX_TableE_numericCol] ON [dbo].[TableE] 
(
    [numericCol] ASC
)

Any ideas?

有任何想法吗？

1 个解决方案

#1

See my answer here:

在这里看到我的答案：

What goes wrong when I add the Where clause?

添加Where子句时出了什么问题？

You say that you tried adding a covering index on TableE, covering both numericCol and id. However, I suspect that the query didn't use it. That is why you see a Table Spool.

您说您尝试在TableE上添加覆盖索引，涵盖numericCol和id。但是，我怀疑查询没有使用它。这就是你看表Spool的原因。

You want to force the query to use the covering index, either by making it the only index on the table, or by including a query hint. That should eliminate the Table Spool and speed up the Nested Loop.

您希望强制查询使用覆盖索引，方法是使其成为表中唯一的索引，或者包含查询提示。这应该消除表Spool并加速嵌套循环。

Same for TableB. If the index being used is only on Id, then it is not helping the Nested Loop which is on NumericCol. Force the issue either by getting rid of the index on Id, or with a query hint.

对于TableB也是如此。如果正在使用的索引仅在Id上，则它不会帮助NumericCol上的嵌套循环。通过删除Id上的索引或查询提示来强制解决问题。

#1