CODEWITHSUNDEEP
sql-serverindexing
ashishduh
ashishduh

Reputation: 6699

Query optimization for INNER JOIN OR condition

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.

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:

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

and on TableE:

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

Any ideas?

Upvotes: 0

Views: 87

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31775

See my answer here:

What goes wrong when I add the Where clause?

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.

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.

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.

Upvotes: 1

Related Questions