0

I try to improve my query plan

SELECT top 100
    cr1.Id, 
    cr1.ArticleId, 
    cr1.CrossArticleId, 
    cr1.CrossesStatusId, 
    cr1.CrossesSourceId
FROM Detail.Crosses AS cr1
INNER JOIN Web.Detail.Article AS a1 ON a1.Id = cr1.ArticleId
INNER JOIN Web.Detail.Article AS a2 ON a2.Id = cr1.CrossArticleId

Plan enter link description here

I see that the second join is not optimal. 100 of 201

I have two indexes

CREATE UNIQUE NONCLUSTERED INDEX [UX_CrossesReverse] ON [Detail].[Crosses]( [CrossArticleId] ASC,   [ArticleId] ASC )

CREATE UNIQUE NONCLUSTERED INDEX [UX_Crosses] ON [Detail].[Crosses] ( [ArticleId] ASC,  [CrossArticleId] ASC )

Please advise me what I can be to improve this query plan

Artur Makvetsyan
  • 1
  • 2
  • 4
    Using `TOP` without `ORDER BY` is usually nonsensical. Please add an `ORDER BY` clause to your query. – Tim Biegeleisen May 23 '22 at 14:04
  • 2
    To share execution plans, upload the actual execution plan to [Paste The Plan](https://www.brentozar.com/pastetheplan/) and add the link to your question. Images don't tell the whole story. – Dan Guzman May 23 '22 at 14:08
  • 1
    100 of 201 just means that the estimate was 201 rows and only 100 were read (because of the `top`) I doubt there is any actual improvement to be had from this plan – Charlieface May 23 '22 at 14:13
  • 1
    @SeanLange Firstly that is most likely *less* efficient. And `IN` is like `OR` so would be equivalent to two `LEFT JOIN`s and checking at least one returned. OP wants two `INNER JOIN`s. Also – Charlieface May 23 '22 at 14:15
  • https://www.brentozar.com/pastetheplan/?id=r1ITLzYw5 – Artur Makvetsyan May 23 '22 at 14:15
  • The elapsed times shown in the plan are all `0.000s` - why isn't that satisfactory? – Martin Smith May 23 '22 at 14:19
  • Can you see this plan without top? https://www.brentozar.com/pastetheplan/?id=ByP9dftw5 – Artur Makvetsyan May 23 '22 at 14:24
  • @Charlieface Thx, I understand. I'd rathe use union? – Artur Makvetsyan May 23 '22 at 14:29
  • 2
    No why would you? Both plans seem exceedingly efficient, I can't imagine any way to improve them. They are doing small seeks on each table, and the indexes all cover the query. So what is the problem with these plans? – Charlieface May 23 '22 at 14:33
  • Actually I have a large query that takes a very long time to complete. Here's what I'm trying to figure out. In any case, thank you all for your help. @Charlieface – Artur Makvetsyan May 24 '22 at 07:26
  • 1
    So if you are trying to look into a problem with Query A why have you asked a question about a different query B that does not reproduce the performance issue? – Martin Smith May 24 '22 at 07:54
  • Perhaps delete this question and create a new one for this other query? – Charlieface May 24 '22 at 08:19
  • @MartinSmith It's foundation query. It isn't other query. I get it by answer Charlieface, that IN is like OR so would be equivalent to two LEFT JOIN – Artur Makvetsyan May 24 '22 at 13:07
  • I thougth for a long time why the second join not use a nonclustered index – Artur Makvetsyan May 24 '22 at 13:11
  • Why should it use a non-clustered index? It doesn't cover the query so it would need Key Lookups anyway. And it can't use both. Plus, while it may be worth using one or other index to do a Merge Join in some instances, with only 100 rows it's unlikely to be worthwhile. So there isn't actually any issue with the current plan. – Charlieface May 24 '22 at 13:46

0 Answers0