Edit Question - KnowledgeHub

Title Body

I use Microsoft SQL Server 2008 (SP1, x64). I have two queries that do the same, or so I think, but they are have completely different query plans and performance. Query 1: SELECT c_pk FROM table_c WHERE c_b_id IN (SELECT b_id FROM table_b WHERE b_z = 1) OR c_a_id IN (SELECT a_id FROM table_a WHERE a_z = 1) Query 2: SELECT c_pk FROM table_c LEFT JOIN (SELECT b_id FROM table_b WHERE b_z = 1) AS b ON c_b_id = b_id LEFT JOIN (SELECT a_id FROM table_a WHERE a_z = 1) AS a ON c_a_id = a_id WHERE b_id IS NOT NULL OR a_id IS NOT NULL Query 1 is fast as I would expect, whereas query 2 is very slow. The query plans look quite different. I would like query 2 to be as fast as query 1. I have software that uses query 2, and I cannot change that into query 1. I can change the database. Some questions: why are the query plans different? can I "teach" SQL Server somehow that query 2 is equal to query 1? All tables have (clustered) primary keys and proper indexes on all columns: CREATE TABLE table_a ( a_pk int NOT NULL PRIMARY KEY, a_id int NOT NULL UNIQUE, a_z int ) GO CREATE INDEX IX_table_a_z ON table_a (a_z) GO CREATE TABLE table_b ( b_pk int NOT NULL PRIMARY KEY, b_id int NOT NULL UNIQUE, b_z int ) GO CREATE INDEX IX_table_b_z ON table_b (b_z) GO CREATE TABLE table_c ( c_pk int NOT NULL PRIMARY KEY, c_a_id int, c_b_id int ) GO CREATE INDEX IX_table_c_a_id ON table_c (c_a_id) GO CREATE INDEX IX_table_c_b_id ON table_c (c_b_id) GO The tables are not modified after filling initially. I'm the only one querying them. They contains millions of records (table_a: 5M, table_b: 4M, table_c: 12M), but using only 1% gives similar results. Edit: I tried adding FOREIGN KEYs for c_a_id and <cod

Tags (comma-separated) Cancel