使用LEFT JOIN和WHERE field IS NULL的SQL Server查询运行缓慢求助

Hey there, let's work through this slow query problem you're hitting in SQL Server 2012. Since you don't have access to execution plans right now, we can start with proven tweaks that often resolve this exact scenario—here's what I'd recommend:

1. 用NOT EXISTS替代LEFT JOIN + IS NULL #

The biggest culprit here is that LEFT JOIN followed by WHERE ... IS NULL forces the database to first create a massive intermediate result set (combining all matching and non-matching rows from your joins) before filtering out the matches. This is inefficient, especially with large tables.

Instead, switch to NOT EXISTS—it uses a semi-join that only checks for the existence of matching rows, no need to build that huge intermediate set. Here's how to rewrite your sample query:

Original (slow) pattern: #

SELECT columns 
FROM (SELECT column1, column2, a, c, d, e FROM table1 WHERE column3 = condition) AS a 
LEFT JOIN b ON a.a = b.b 
LEFT JOIN c ON a.c = c.c 
LEFT JOIN d ON a.d = d.d 
LEFT JOIN e ON a.e = e.e 
WHERE b.b IS NULL AND c.c IS NULL AND d.d IS NULL AND e.e IS NULL;

Rewritten with NOT EXISTS: #

SELECT column1, column2 
FROM table1 a 
WHERE a.column3 = condition
  AND NOT EXISTS (SELECT 1 FROM b WHERE b.b = a.a)
  AND NOT EXISTS (SELECT 1 FROM c WHERE c.c = a.c)
  AND NOT EXISTS (SELECT 1 FROM d WHERE d.d = a.d)
  AND NOT EXISTS (SELECT 1 FROM e WHERE e.e = a.e);

SQL Server's optimizer in 2012 handles NOT EXISTS very efficiently, often using index seeks instead of full scans to check for matching rows.

2. 展开嵌套子查询 #

Your original query wraps table1 in a subquery before joining. While sometimes necessary, this can limit the optimizer's ability to apply predicate pushing and index usage. Try removing the subquery and applying the column3 = condition filter directly on table1:

SELECT column1, column2 
FROM table1 a 
WHERE a.column3 = condition
  AND NOT EXISTS (SELECT 1 FROM b WHERE b.b = a.a)
  -- ... other NOT EXISTS clauses ...

This lets the optimizer prioritize filtering table1 first (using an index on column3 if available) before checking the other tables, reducing the number of rows it needs to process for the NOT EXISTS checks.

3. 添加针对性索引 #

Even without execution plans, we can guess at missing indexes that are slowing things down:

• For table1: Create an index on column3 that includes all the columns you need (the ones selected and used in joins). This is a covering index, so the database won't need to "look up" data from the main table:

  CREATE NONCLUSTERED INDEX IX_table1_column3_include ON table1 (column3)
  INCLUDE (column1, column2, a, c, d, e);
  

• For joined tables (b, c, d, e): Add indexes on the join columns (b.b, c.c, etc.). These let the NOT EXISTS checks run quickly via index seeks instead of full table scans:

  CREATE NONCLUSTERED INDEX IX_b_b ON b (b);
  CREATE NONCLUSTERED INDEX IX_c_c ON c (c);
  -- Repeat for d and e
  

4. 移除不必要的LEFT JOINs #

Double-check if all those LEFT JOINs are actually needed. If your final SELECT doesn't include columns from tables c, d, or e, and you're only filtering on their IS NULL values, you might be adding unnecessary overhead. The NOT EXISTS approach already handles the "no match" check without needing to join the tables at all.

5. 尝试EXCEPT运算符（可选） #

If your goal is to find rows in table1 that have no matches in all the other tables, the EXCEPT operator can sometimes be more efficient than multiple NOT EXISTS clauses. Here's an example:

SELECT column1, column2 FROM table1 WHERE column3 = condition
EXCEPT
SELECT t.column1, t.column2 
FROM table1 t
JOIN b ON t.a = b.b
JOIN c ON t.c = c.c
JOIN d ON t.d = d.d
JOIN e ON t.e = e.e
WHERE t.column3 = condition;

Note: EXCEPT automatically removes duplicates, so use this only if duplicate rows in your original result set aren't required.

Once you get access to execution plans, you can dig deeper—look for full table scans, key lookups, or expensive sort operations, which will point to other areas to optimize.

内容的提问来源于stack exchange，提问作者Alejandro