MSSQL Join between big and empty tables
0
votes
1
answer
2210
views
I've got an issue with estimated cost and join predicate :
SELECT c.Id FROM Table_A a
LEFT JOIN Table_B b ON b.Id_A = a.Id
LEFT JOIN Table_C c ON c.Id_B = b.Id
WHERE a.Id = 2500
OPTION (RECOMPILE)
Table_A has 50k rows, Table_B is empty, Table_C has 2M rows
The PK and FK indexes exists and the stats are up to date.
But for some reason, SQL Server use a Clustred Index Scan on Table_C.
https://www.brentozar.com/pastetheplan/?id=r1G_LnpeU
This is a sample my problem, the prod tables are fare bigger and use too much estimated grant memory because of this issue.
Using a
FORCESEEK
or change the join to LEFT JOIN Table_C c ON c.Id_B = b.Id AND c.Id_B IS NOT NULL
resolve the issue but it's a query (mutliple queries in fact) generate by Entity Framework so I don't have so much control on it.
Is there a way to seek Table_C without changing the query here ?
Asked by Zobix
(3 rep)
Jan 16, 2020, 10:20 AM
Last activity: Jan 17, 2020, 10:07 AM
Last activity: Jan 17, 2020, 10:07 AM