Index scan when more than 35 correlated subqueries are used with default cardinality estimation
4
votes
1
answer
448
views
Recently, we updated the compatibility level of our SQL Server from 2012 to 2016, but after updating the compatibility level we ran into performance issues when a lot of sub queries are used. Especially when more than 35 subqueries are used.
Here is a query with which I can reproduce it:
But for every additional subquery over 35 an index scan is used:
Does anybody have any explanation why this happens? If Legacy Cardinality Estimation is enabled, the query is fast and doesn't have this issue, but we want to disable that.
I already tried to rebuild indexes and update the statistics but that doesn't make any difference.
SELECT
[PK_R_ASSEMBLYCOSTING],
(SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
(SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
(SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
(SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
(SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
(SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
(SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
(SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
(SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
(SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
(SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
(SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
(SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
(SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
(SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
(SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
(SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
(SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
(SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
(SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
(SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
(SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
(SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
(SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
(SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
(SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
(SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
(SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
(SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
(SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
(SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
(SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
(SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
(SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
(SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
(SELECT SUM([PRICEEXMARKUP]) FROM [R_ASSEMBLYCOSTINGITEM] WHERE [FK_ASSEMBLYCOSTING] = [PK_R_ASSEMBLYCOSTING]),
[PK_R_ASSEMBLYCOSTING]
FROM [R_ASSEMBLYCOSTING]
WHERE [FK_ASSEMBLY] = 309961
When there are less than 35 subqueries, the query plan shows it is using index seeks for the subqueries:


Asked by urk_forever
(143 rep)
Mar 7, 2025, 04:27 PM
Last activity: Mar 10, 2025, 04:53 PM
Last activity: Mar 10, 2025, 04:53 PM