大表三角连接优化：CID间关联查询性能提升方案咨询

Hey there, I see you're hitting performance snags when calculating correlations between CIDs using a triangular join on full datasets—totally makes sense, since that approach blows up the intermediate row count pretty quickly. Let's break down why the original query is slow and how we can fix it.

Why the Original Query Struggles #

Your current query joins every matching row between CID pairs first, then computes correlation stats during grouping. For example, if you have 1000 CIDs sharing the same currency and dates, that's 1000*999 = 999,000 CID pairs, each with potentially hundreds of date rows. That's a massive intermediate dataset that drags down execution time, even with indexes.

The Fix: Aggregate Early, Join Late #

Correlation only needs a handful of aggregated values per CID pair (and shared currency/dates):

• Count of shared dates (n)
• Sum of cross products (sum_xy = sum of LOG_VAL from CID1 * LOG_VAL from CID2 for shared dates)
• Sum of LOG_VAL for each CID (sum_x, sum_y)
• Sum of squared LOG_VAL for each CID (sum_x2, sum_y2)

Instead of joining all raw rows first, we'll aggregate the necessary stats as soon as possible to minimize the data we process.

Optimized SQL Code #

-- First, aggregate stats directly for each CID pair with shared dates/currency
WITH cid_pair_stats AS (
    SELECT
        b.CID AS cid1,
        c.CID AS cid2,
        b.CURRENCY,
        COUNT(*) AS n,
        SUM(b.LOG_VAL * c.LOG_VAL) AS sum_xy,
        SUM(b.LOG_VAL) AS sum_x,
        SUM(c.LOG_VAL) AS sum_y,
        SUM(b.LOG_VAL * b.LOG_VAL) AS sum_x2,
        SUM(c.LOG_VAL * c.LOG_VAL) AS sum_y2
    FROM @tbl b
    JOIN @tbl c 
        ON b.CID < c.CID -- Avoid duplicate pairs (e.g., (1,2) and (2,1))
        AND b.CURRENCY = c.CURRENCY
        AND b.MDATE = c.MDATE
    GROUP BY b.CID, c.CID, b.CURRENCY
)
-- Calculate correlation using pre-aggregated stats
SELECT
    cid1,
    cid2,
    CURRENCY,
    CASE
        -- Handle cases where variance is zero (correlation is undefined)
        WHEN (n * sum_x2 - POWER(sum_x, 2)) = 0 
            OR (n * sum_y2 - POWER(sum_y, 2)) = 0
            THEN NULL
        ELSE
            (n * sum_xy - sum_x * sum_y) 
            / 
            (SQRT(n * sum_x2 - POWER(sum_x, 2)) 
             * SQRT(n * sum_y2 - POWER(sum_y, 2)))
    END AS correl
FROM cid_pair_stats

Key Improvements #

1. Cut duplicate pairs: Using b.CID < c.CID instead of b.CID <> c.CID halves the number of pairs we process, since we don't need both (CID1, CID2) and (CID2, CID1) for correlation.
2. Aggregate immediately: We group the joined rows right into CID pairs, so we never carry around thousands of unnecessary raw date rows through the query.
3. Handle edge cases: Added a CASE statement to avoid division by zero when a CID has no variance in LOG_VAL (correlation is undefined here).

Bonus: Index Optimization (For Physical Tables) #

If @tbl is a physical table instead of a table variable, add this covering index to speed up the join and aggregation:

CREATE NONCLUSTERED INDEX IX_tbl_Currency_MDate_CID_LogVal
ON tbl (CURRENCY, MDATE, CID)
INCLUDE (LOG_VAL);

This index lets the database quickly find all CIDs for a given currency and date, avoiding expensive full table scans.

Why This Works #

The original query's intermediate dataset is number of CID pairs * number of shared dates. This optimized version cuts that down to just number of CID pairs—a massive reduction in data volume that translates to way faster execution on full datasets. Testing with your sample data will give you identical correlation results, but with a fraction of the runtime.

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