优化适用于Access传递查询的SQL语句，提升执行效率

针对你的Access传递查询执行耗时过长的问题，以下是纯SQL优化方案，核心是用CTE替代临时表、消除重复子查询、扁平化嵌套结构，同时保持可直接作为传递查询执行的特性：

优化策略 #

• 用**CTE（公共表表达式）**替代临时表：在单条SQL内复用筛选后的CatCov关联数据，避免重复查询，且兼容Access传递查询（后端需为SQL Server）
• 预计算聚合值：将原SQL中重复出现的MAX(InternetActiveDate)、MAX(Request_Completed_Date)等聚合逻辑提前在CTE中计算，减少重复执行开销
• 过滤条件前置：将Season_Id = 'F24'、RD >=50等过滤逻辑提前到CTE，减少后续JOIN和聚合的数据量
• 简化冗余逻辑：合并重复的CASE表达式，移除不必要的DISTINCT和嵌套层级

优化后的SQL代码 #

SET NOCOUNT ON;

WITH CatCovFiltered AS (
    SELECT 
        a.mailyear,
        a.offer,
        a.description,
        a.InternetActiveDate,
        a.FirstReleaseMailed,
        a.season_id,
        a.offer_type,
        a.price_type,
        b.Prefix_Quarter,
        a.CompanyCode AS Brand_Code,
        CASE a.CompanyCode
            WHEN '1' THEN 'Company 1'
            WHEN '2' THEN 'Company 2'
            WHEN '3' THEN 'Company 3'
            WHEN '4' THEN 'Company 4'
            WHEN '5' THEN 'Company 5'
            WHEN '6' THEN 'Company 6'
            WHEN '7' THEN 'Company 7'
            WHEN '8' THEN 'Company 8'
            WHEN '9' THEN 'Company 9'
            ELSE a.CompanyCode
        END AS Brand_Name
    FROM CatCov a
    JOIN offer_placement b 
        ON a.Offer = b.offer 
        AND a.MailYear = b.offeryear
    WHERE 
        a.Description NOT LIKE '%test%'
        AND a.Description NOT LIKE '%Price%A%'
        AND a.Description NOT LIKE '%Price%B%'
        AND a.Description NOT LIKE '%Amazon.com%'
        AND a.Description NOT LIKE '%EB Discount%'
        AND a.Offer_Type IN ('Catalog', 'Insert', 'Kicker', 'Statement Insert', 'Bangtail', 'Onsert', 'Outside Ad', 'Blow In')
),
CatCovLatestIAD AS (
    SELECT 
        offer,
        mailyear,
        MAX(CAST(InternetActiveDate AS DATE)) AS LatestIAD
    FROM CatCovFiltered
    GROUP BY offer, mailyear
),
RetailChangeLatest AS (
    SELECT 
        [key],
        MAX(CAST(Request_Completed_Date AS DATETIME)) AS LatestCompletedDate
    FROM CIDRetailChangeTracking
    GROUP BY [key]
),
PackLastHigh AS (
    SELECT 
        a.PackNum,
        MAX(CAST(b.firstreleasemailed AS DATE)) AS Maildate,
        CASE 
            WHEN MAX(a.RetOne) >= MAX(a.ret2) AND MAX(a.RetOne) >= MAX(a.ORIGINALRETAIL) THEN MAX(a.RetOne)
            WHEN MAX(a.ret2) >= MAX(a.RetOne) AND MAX(a.ret2) >= MAX(a.ORIGINALRETAIL) THEN MAX(a.ret2)
            WHEN MAX(a.ORIGINALRETAIL) >= MAX(a.ret2) AND MAX(a.ORIGINALRETAIL) >= MAX(a.RetOne) THEN MAX(a.ORIGINALRETAIL)
        END AS LastHigh
    FROM pic704current a
    JOIN CatCovFiltered b 
        ON a.CatID = b.Offer 
        AND a.Year = b.MailYear
    WHERE 
        (CAST(b.firstreleasemailed AS DATE) <= GETDATE() OR b.FirstReleaseMailed IS NULL)
        AND b.Season_Id = 'F24'
    GROUP BY a.PackNum
),
PackRetailAgg AS (
    SELECT 
        b.PackNum,
        c.LatestIAD AS IAD,
        CASE 
            WHEN b.retone >= b.ret2 AND b.retone >= b.originalretail THEN b.Retone
            WHEN b.Ret2 >= b.RetOne AND b.Ret2 >= b.originalretail THEN b.Ret2
            WHEN b.Originalretail >= b.ret2 AND b.Originalretail >= b.RetOne THEN b.Originalretail
        END AS MaxRetail,
        CASE 
            WHEN b.retone >= b.ret2 AND b.retone >= b.originalretail THEN b.Retone
            WHEN b.Ret2 >= b.RetOne AND b.Ret2 >= b.originalretail THEN b.Ret2
            WHEN b.Originalretail >= b.ret2 AND b.Originalretail >= b.RetOne THEN b.Originalretail
        END AS MinRetail,
        lh.LastHigh
    FROM CatCovFiltered a
    JOIN PIC704Current b 
        ON a.offer = b.CatID 
        AND a.MailYear = b.year
    JOIN CatCovLatestIAD c 
        ON a.offer = c.offer 
        AND a.MailYear = c.mailyear
    JOIN PackLastHigh lh 
        ON b.PackNum = lh.PackNum
    WHERE 
        CAST(a.InternetActiveDate AS DATE) = c.LatestIAD
        AND a.Season_Id = 'F24'
),
PackFinalAgg AS (
    SELECT 
        a.PackNum,
        MAX(q.IAD) AS [Last High Date],
        MAX(q.MaxRetail) AS MaxRetail,
        MIN(q.MinRetail) AS MinRetail,
        q.LastHigh
    FROM PIC704Current a
    JOIN CatCovFiltered b 
        ON a.CatID = b.Offer 
        AND a.Year = b.MailYear
    JOIN CatCovLatestIAD c 
        ON b.offer = c.offer 
        AND b.MailYear = c.mailyear
    JOIN PackRetailAgg q 
        ON a.PackNum = q.PackNum 
        AND CAST(b.InternetActiveDate AS DATE) = q.IAD
    WHERE 
        a.RD >= 50
        AND CAST(b.InternetActiveDate AS DATE) = c.LatestIAD
    GROUP BY a.PackNum, q.LastHigh
)

SELECT 
    a.PackNum,
    a.Description,
    a.CatID,
    CONCAT(a.PackNum, a.CatID) AS [Key],
    CONCAT(a.rd, a.sfc) AS RDSFC,
    b.Season_Id,
    b.Brand_Name,
    b.Description AS CatCovDesc,
    b.FirstReleaseMailed AS MailDate,
    b.Prefix_Quarter,
    a.retone AS Retail,
    a.ret2 AS EBRetail,
    a.OriginalRetail,
    a.DiscountReasonCode AS DRC,
    hr.LastHigh AS LastHigh,
    ct.New_Retail1 AS New_Retail,
    ct.EB_High,
    ct.Catalog,
    ct.Original_Retail,
    ct.DRC AS New_DRC,
    ct.Requested,
    ct.Request_Completed_Date,
    ct.Notes,
    ct.Season,
    m.Merch AS MerchMgr,
    t.MerchAssistants
FROM PIC704Current a
JOIN CatCovFiltered b 
    ON a.CatID = b.Offer 
    AND a.year = b.mailyear
LEFT JOIN CIDRetailChangeTracking ct 
    ON a.PackNum = ct.Pack 
    AND a.CatID = ct.Catalog 
    AND b.Season_Id = ct.Season
LEFT JOIN RetailChangeLatest rcl 
    ON CONCAT(a.PackNum, a.CatID) = rcl.[key]
LEFT JOIN MerchantCodes m 
    ON a.TwoDigitMerchCode = m.MerchCode
JOIN SupplyChain_Misc.flex_tbl_merchcode t 
    ON m.MerchCode = t.MerchCode
LEFT JOIN PackFinalAgg hr 
    ON a.PackNum = hr.PackNum
WHERE 
    a.RD >= 50
    AND b.Season_Id = 'F24'
    AND (ct.Request_Completed_Date IS NULL OR CAST(ct.Request_Completed_Date AS DATETIME) = rcl.LatestCompletedDate)
    AND hr.MaxRetail <> hr.MinRetail
GROUP BY 
    a.PackNum,
    a.Description,
    a.CatID,
    a.rd,
    a.sfc,
    b.Season_Id,
    b.Brand_Name,
    b.Description,
    b.FirstReleaseMailed,
    b.Prefix_Quarter,
    hr.MaxRetail,
    hr.MinRetail,
    hr.LastHigh,
    a.retone,
    a.ret2,
    a.OriginalRetail,
    a.DiscountReasonCode,
    ct.New_Retail1,
    ct.EB_High,
    ct.Catalog,
    ct.Original_Retail,
    ct.DRC,
    ct.Requested,
    ct.Request_Completed_Date,
    ct.Notes,
    ct.Season,
    m.Merch,
    t.MerchAssistants
ORDER BY 
    a.Packnum,
    b.MailDate;

关键优化点说明 #

1. CTE复用逻辑：将原临时表#catcov1替换为CatCovFiltered CTE，同时新增CatCovLatestIAD、RetailChangeLatest等CTE预计算重复使用的聚合值，避免多次执行相同子查询
2. 过滤条件简化：将原CASE判断的过滤逻辑拆分为直接的NOT LIKE条件，提升可读性和执行效率
3. JOIN层级扁平化：原嵌套3层以上的子查询被拆分为多个独立CTE，逻辑更清晰，数据库优化器更容易生成高效执行计划
4. 替换FULL JOIN为LEFT JOIN：原部分FULL JOIN实际可简化为LEFT JOIN，减少不必要的笛卡尔积计算

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