SQL视图多表关联优化：避免重复扫描实现高效查询

Great question! The key here is to leverage window functions to process Table2 in a single scan, which eliminates the repeated subquery hits and simplifies the status logic. Let's break this down with a complete, optimized view:

CREATE VIEW MyView AS
WITH tbl2_processed AS (
    SELECT
        t.FK_TABLE1,
        t.ZZ,
        -- Assign row number per Table1 group, sorted by Type ascending
        ROW_NUMBER() OVER (PARTITION BY t.FK_TABLE1 ORDER BY t.Type ASC) AS rn,
        -- Capture first and last status FKs for the group
        FIRST_VALUE(t.FK_Status) OVER (PARTITION BY t.FK_TABLE1 ORDER BY t.Type ASC) AS first_status_fk,
        LAST_VALUE(t.FK_Status) OVER (
            PARTITION BY t.FK_TABLE1 
            ORDER BY t.Type ASC 
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
        ) AS last_status_fk,
        -- Capture first and last status names (avoid re-joining STATUS later)
        FIRST_VALUE(s.StatusName) OVER (PARTITION BY t.FK_TABLE1 ORDER BY t.Type ASC) AS first_status_name,
        LAST_VALUE(s.StatusName) OVER (
            PARTITION BY t.FK_TABLE1 
            ORDER BY t.Type ASC 
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
        ) AS last_status_name
    FROM Table2 t
    LEFT JOIN STATUS s ON t.FK_Status = s.PK
),
tbl2_pivoted AS (
    SELECT
        FK_TABLE1,
        -- Pivot top 3 ZZ values into columns
        MAX(CASE WHEN rn = 1 THEN ZZ END) AS ZZ1,
        MAX(CASE WHEN rn = 2 THEN ZZ END) AS ZZ2,
        MAX(CASE WHEN rn = 3 THEN ZZ END) AS ZZ3,
        -- Determine final status: use last if it's 'X', else first
        CASE WHEN last_status_name = 'X' THEN last_status_name ELSE first_status_name END AS CurrentStatus
    FROM tbl2_processed
    GROUP BY 
        FK_TABLE1, 
        first_status_name, 
        last_status_name
)
SELECT
    t1.PK,
    t1.XX,
    t1.YY,
    tp.ZZ1,
    tp.ZZ2,
    tp.ZZ3,
    tp.CurrentStatus
FROM Table1 t1
LEFT JOIN tbl2_pivoted tp ON t1.PK = tp.FK_TABLE1;

Why this works: #

• Single Table2 scan: All window functions (ROW_NUMBER(), FIRST_VALUE(), LAST_VALUE()) are computed in a single pass over Table2, no repeated subquery scans.
• Efficient pivoting: Using CASE with MAX() converts the top 3 sorted ZZ values into columns without multiple subqueries.
• Status logic handled upfront: We capture the first/last status names directly in the CTE, so we don't need to re-join the STATUS table later. The LAST_VALUE() uses ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING to ensure we get the final row in each group (the default window range stops at the current row).

Performance Tips: #

• Add a composite index on Table2 to speed up the window function grouping/sorting:

  CREATE NONCLUSTERED INDEX IX_Table2_FKTable1_Type 
  ON Table2 (FK_TABLE1, Type) 
  INCLUDE (ZZ, FK_Status);
  

• Verify the execution plan: You should see Table2 scanned only once, with no repeated lookups. Compare logical reads to your original view to confirm the improvement.

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