Y
das spool
The main difference between the two queries is the presence of an https://www.erikdarlingdata.com/starting-sql/starting-sql-eager-index-spools/ .
From the article:
Eager index spools can occur on the inner side of Nested Loops joins
to reduce the amount of work that needs to be done there, by creating
a more opportune index for the loop to work off of.
That all sounds very good, but there are some problems:
The index gets created single-threaded
The way data is loaded into the spool is very inefficient
The spool is disposed of when the query is finished, It’ll get built over and over again on later executions
There’s no missing index request for the spool anywhere
But in your case, since the spool is quite small, it works in your favor.
slowpoke
In the slow query, you have a nested loops join against tbl_src_ArticlesCategories which executes ~7k times, but doesn't have a useful index, so the entire table is scanned for each execution.
The scan:
The details:
You end up reading ~53 million rows in total when all is said and done, because you scan ~736k rows ~7k times.
fastpoke
In the fast plan, you get this instead:
The scan and spool (with seek):
The details
The optimizer for this plan decided to create a good index for you, so it has a more suitable structure to use to locate matching rows in ID_ARTICLE.
You do ~7k seeks, which is much more efficient given the circumstances.
equalizer
You could potentially get equal performance from both queries by adding this index:
CREATE /*UNIQUE?*/ INDEX spool_b_gone
ON [dbo].[tbl_src_ArticlesCategories]
(
[ID_ARTICLE]
)
INCLUDE
(
[ID_LINE],
[ID_GROUP],
[ID_CLASS],
[ID_FAM]
);
Though sometimes the optimizer is foolish and https://www.erikdarlingdata.com/sql-server/index-spools-when-you-have-an-index/ even when you have the right index in place.
differences?
The immediate difference that I see is that in the slower execution, you generate the row number over a ton more columns across different tables:
But I'm a bit short on time, so there may be other things contributing to the choice between spool/no spool.
