优化重复EXISTS子查询的SQL性能与复用问题咨询

Got it, let's tackle this performance nightmare and code duplication issue head-on. Your problem is classic: repeated correlated subqueries are killing performance because they run once per row for each of your 10 subqueries—leading to that brutal 45-minute runtime. We can fix both the efficiency and the repetition with two key strategies: precompute all potential matches in one pass and reuse that precomputed data for all your date range checks.

1. Replace Repeated EXISTS with a Single Precomputation #

Instead of running 10 separate subqueries, we'll first generate all valid "repeat" pairs (and calculate the days between them) in a single CTE or temporary table. Then we can use conditional aggregation to check for matches across all your required date ranges in one go.

Step 1: Precompute Repeat Pairs (CTE Version) #

First, we create a CTE that captures all pairs of records that meet your core filtering rules, plus the number of days between the main record and the potential repeat:

WITH RepeatPairs AS (
    SELECT
        a_main.x AS main_x,
        a_main.date AS main_date,
        a_main.col_c AS main_col_c,
        b_main.col_a AS main_col_a,
        b_main.col_b AS main_col_b,
        DATEDIFF(day, a_sub.date, a_main.date) AS days_between
    FROM
        a a_main
        INNER JOIN b b_main ON a_main.x = b_main.x
        -- Join to find historical a records that match col_c and exclude col_d=7
        INNER JOIN a a_sub ON 
            a_main.col_c = a_sub.col_c
            AND (a_main.col_d IS NULL OR a_main.col_d <> 7)
            AND (a_sub.col_d IS NULL OR a_sub.col_d <> 7)
            AND a_main.date > a_sub.date  -- Ensure we're looking at older records
        -- Join to match b records with same col_a but different col_b
        INNER JOIN b b_sub ON 
            a_sub.x = b_sub.x
            AND b_main.col_a = b_sub.col_a
            AND b_main.col_b <> b_sub.col_b
)

Step 2: Use Conditional Aggregation for All Date Ranges #

Now we can left-join this CTE to our main query and use MAX(CASE...) to check if any matches exist for each date range. This replaces all 10 subqueries with a single join and aggregation:

SELECT
    a.col_a,
    a.col_b,
    a.col_c,
    b.col_a,
    b.col_b,
    -- Check for repeats in each date range
    CASE WHEN MAX(CASE WHEN days_between <= 28 THEN 1 ELSE 0 END) = 1 THEN 'Yes' ELSE 'No' END AS IsRepeat28,
    CASE WHEN MAX(CASE WHEN days_between <= 21 THEN 1 ELSE 0 END) = 1 THEN 'Yes' ELSE 'No' END AS IsRepeat21,
    CASE WHEN MAX(CASE WHEN days_between <= 14 THEN 1 ELSE 0 END) = 1 THEN 'Yes' ELSE 'No' END AS IsRepeat14,
    CASE WHEN MAX(CASE WHEN days_between <= 7 THEN 1 ELSE 0 END) = 1 THEN 'Yes' ELSE 'No' END AS IsRepeat7,
    CASE WHEN MAX(CASE WHEN days_between <= 1 THEN 1 ELSE 0 END) = 1 THEN 'Yes' ELSE 'No' END AS IsRepeat1,
    -- Adjust this logic to match your specific HasRepeat requirements
    CASE WHEN COUNT(days_between) > 0 THEN 'Yes' ELSE 'No' END AS HasRepeat
FROM
    a
    INNER JOIN b ON a.x = b.x
    LEFT JOIN RepeatPairs ON 
        a.x = RepeatPairs.main_x
        AND a.date = RepeatPairs.main_date
        AND a.col_c = RepeatPairs.main_col_c
        AND b.col_a = RepeatPairs.main_col_a
        AND b.col_b = RepeatPairs.main_col_b
GROUP BY
    a.col_a, a.col_b, a.col_c, b.col_a, b.col_b;

2. Reduce Code Duplication #

By moving all your core filtering logic (the col_d checks, col_c matching, and b table conditions) into the RepeatPairs CTE, you eliminate the need to repeat those rules across 10 subqueries. If you ever need to adjust these rules (like changing the col_d exclusion or adding a new filter), you only have to modify the CTE once instead of updating every subquery.

Bonus: Boost Performance with Temporary Tables (For Large Datasets) #

If your tables are massive, a temporary table with indexes can outperform a CTE (since CTEs are often re-evaluated multiple times in some SQL engines). Here's how to adapt the above approach:

-- Create temporary table to store repeat pairs
SELECT
    a_main.x AS main_x,
    a_main.date AS main_date,
    a_main.col_c AS main_col_c,
    b_main.col_a AS main_col_a,
    b_main.col_b AS main_col_b,
    DATEDIFF(day, a_sub.date, a_main.date) AS days_between
INTO #RepeatPairs
FROM
    a a_main
    INNER JOIN b b_main ON a_main.x = b_main.x
    INNER JOIN a a_sub ON 
        a_main.col_c = a_sub.col_c
        AND (a_main.col_d IS NULL OR a_main.col_d <> 7)
        AND (a_sub.col_d IS NULL OR a_sub.col_d <> 7)
        AND a_main.date > a_sub.date
    INNER JOIN b b_sub ON 
        a_sub.x = b_sub.x
        AND b_main.col_a = b_sub.col_a
        AND b_main.col_b <> b_sub.col_b;

-- Add an index to speed up the left join in the main query
CREATE CLUSTERED INDEX IX_RepeatPairs_MainKeys ON #RepeatPairs (main_x, main_date, main_col_c, main_col_a, main_col_b);

-- Main query (same as CTE version, just referencing the temp table)
SELECT
    a.col_a,
    a.col_b,
    a.col_c,
    b.col_a,
    b.col_b,
    CASE WHEN MAX(CASE WHEN days_between <=28 THEN 1 ELSE 0 END)=1 THEN 'Yes' ELSE 'No' END AS IsRepeat28,
    CASE WHEN MAX(CASE WHEN days_between <=21 THEN 1 ELSE 0 END)=1 THEN 'Yes' ELSE 'No' END AS IsRepeat21,
    CASE WHEN MAX(CASE WHEN days_between <=14 THEN 1 ELSE 0 END)=1 THEN 'Yes' ELSE 'No' END AS IsRepeat14,
    CASE WHEN MAX(CASE WHEN days_between <=7 THEN 1 ELSE 0 END)=1 THEN 'Yes' ELSE 'No' END AS IsRepeat7,
    CASE WHEN MAX(CASE WHEN days_between <=1 THEN 1 ELSE 0 END)=1 THEN 'Yes' ELSE 'No' END AS IsRepeat1,
    CASE WHEN COUNT(days_between)>0 THEN 'Yes' ELSE 'No' END AS HasRepeat
FROM
    a
    INNER JOIN b ON a.x = b.x
    LEFT JOIN #RepeatPairs ON 
        a.x = #RepeatPairs.main_x
        AND a.date = #RepeatPairs.main_date
        AND a.col_c = #RepeatPairs.main_col_c
        AND b.col_a = #RepeatPairs.main_col_a
        AND b.col_b = #RepeatPairs.main_col_b
GROUP BY
    a.col_a, a.col_b, a.col_c, b.col_a, b.col_b;

-- Clean up the temporary table
DROP TABLE #RepeatPairs;

Key Performance Tips #

• Add Indexes: Create composite indexes on a(col_c, date, x, col_d) and b(x, col_a, col_b) to speed up the joins in the precomputation step.
• Avoid Implicit Conversions: Ensure a.date and a_sub.date are the same date type to prevent the database from skipping indexes.
• Adjust for Your SQL Engine: If you're using PostgreSQL, replace DATEDIFF(day, ...) with DATE_PART('day', a_main.date - a_sub.date). For MySQL, use DATEDIFF(a_main.date, a_sub.date).

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