Short and Lousy
This is one of the most frustrating things I’ve seen from the optimizer in quite a while.
Here are a couple tables, with a foreign key between them:
CREATE TABLE dbo.ct(id int PRIMARY KEY, dt datetime); CREATE TABLE dbo.ct_fk(id int PRIMARY KEY, dt datetime); ALTER TABLE dbo.ct ADD CONSTRAINT ct_c_fk FOREIGN KEY (id) REFERENCES dbo.ct_fk(id);
When we use the EXISTS clause, join elimination occurs normally:
SELECT COUNT_BIG(*) AS [?] FROM dbo.ct AS c WHERE EXISTS ( SELECT 1/0 FROM dbo.ct_fk AS cf WHERE cf.id = c.id );
But when we use NOT EXISTS, it… doesn’t.
SELECT COUNT_BIG(*) AS [?] FROM dbo.ct AS c WHERE NOT EXISTS ( SELECT 1/0 FROM dbo.ct_fk AS cf WHERE cf.id = c.id );
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
- SQL Server 2017 CU 30: The Real Story With SelOnSeqPrj Fixes
- Getting The Top Value Per Group With Multiple Conditions In SQL Server: Row Number vs. Cross Apply With MAX
- Getting The Top Value Per Group In SQL Server: Row Number vs. Cross Apply Performance
- Multiple Distinct Aggregates: Still Harm Performance Without Batch Mode In SQL Server