Sample Header Ad - 728x90

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