sql-server - 查询永远不会结束

Question

所以我有一个永远不会完成的报告的查询。我已经让它运行了超过 20 分钟而没有完成。这是查询：

DECLARE @start DATE, @end DATE

SET @start = '7-1-2019'
SET @end = '8-20-2019'

SELECT cg.*
    ,ba.std_StandardAcctNo AS bonus_acct
FROM [CustSalesTrend_Growth] cg
LEFT JOIN (
    SELECT DISTINCT std_standardacctno
    FROM [CustSalesTrend_Growth]
    WHERE eleph_Period_Date BETWEEN @Start
            AND @End
        OR ideal_Period_Date BETWEEN @Start
            AND @End
    ) ba ON ba.std_StandardAcctNo = cg.std_StandardAcctNo
    AND cg.Period_Date >= @Start
    AND cg.Period_Date <= @End
WHERE [Appearance_Count] <> 0
    AND Period_gp <> 0
ORDER BY cg.std_StandardAcctNo

我诊断此问题的第一步是在被引用的视图上执行一个简单的 select * ( CustSalesTrend_Growth)，它平均将在大约 30 秒内完成运行，拉取 12k 条记录。鉴于此，我对前面的查询如何为执行增加如此多的复杂性，以至于它永远不会完成感到有些困惑。在我看来，上面的查询相对简单，所以关于为什么会发生这种情况的任何想法？

编辑：查询 CustSalesTrend_Growth 视图

CREATE VIEW [dbo].[CustSalesTrend_Growth]
AS
WITH basedata
AS (
    SELECT DISTINCT a.order_num
        ,a.Period_Date
        ,a.year AS std_the_year
        ,a.Month AS std_the_month
        ,a.customer_alias AS std_StandardAcctNo
        ,b.SalesPerson_name
        ,isnull(ac.counter, 0) [Appearance_Count]
        ,isnull(b.[Year], 0) [The_Year]
        ,isnull(b.[Month], 0) [The_Month]
        ,isnull(b.Customer_Alias, 0) [CustomerName]
        ,sum(isnull(b.Gallons, 0)) [Gallon_Qty]
        ,sum(isnull(b.[Total Sale], 0)) [Total_Sale]
        ,sum(isnull(b.[Gross Profit], 0)) [Total_Gross_Profit]
    FROM (
        SELECT DISTINCT a.year
            ,a.month
            ,b.Customer_Alias
            ,convert(INTEGER, convert(VARCHAR(4), a.Year) + right('00' + convert(VARCHAR(2), a.month), 2)) AS order_num
            ,convert(DATE, convert(VARCHAR(2), a.Month) + '/01/' + convert(VARCHAR(4), a.Year)) AS Period_Date
        FROM Complete_Sales_V2 a
        JOIN (
            SELECT DISTINCT Customer_Alias
            FROM Complete_Sales_V2
            ) b ON 1 = 1
        ) a
    JOIN PDI_Warehouse_2049_01.dbo.Appearance_Count ac ON a.Customer_Alias = ac.customer_alias
    LEFT JOIN Complete_Sales_V2 b ON a.Customer_Alias = b.Customer_Alias
        AND a.Month = b.Month
        AND a.Year = b.Year
    GROUP BY a.order_num
        ,a.Period_Date
        ,a.year
        ,a.Month
        ,a.Customer_Alias
        ,b.SalesPerson_Name
        ,ac.counter
        ,b.[Year]
        ,b.[Month]
        ,b.Customer_Alias
    )
    ,saleslist
AS (
    SELECT DISTINCT SalesPerson_Name
        ,Appearance_Count
        ,Period_Date
        ,std_the_month
        ,std_the_year
        ,std_StandardAcctNo
        ,isnull(sum(Total_Gross_Profit), 0) Period_GP
    FROM basedata
    GROUP BY SalesPerson_Name
        ,Appearance_Count
        ,Period_Date
        ,std_StandardAcctNo
        ,std_the_month
        ,std_the_year
    )
    ,core_GP
AS (
    SELECT DISTINCT a.Customer_Alias
        ,convert(DATE, convert(VARCHAR(2), a.month) + '/01/' + convert(VARCHAR(4), a.year)) AS Period_Date
        ,sum(a.[Gross Profit]) AS Period_GP
    FROM Complete_Sales_V2 a
    JOIN PDI_Warehouse_2049_01.dbo.appearance_count ac ON ac.customer_alias = a.Customer_Alias
    GROUP BY counter
        ,convert(DATE, convert(VARCHAR(2), a.month) + '/01/' + convert(VARCHAR(4), a.year))
        ,a.Customer_Alias
    )
    ,GroupedData
AS (
    SELECT DISTINCT cgp.std_StandardAcctNo
        ,cgp.Period_Date
        ,sum(cgp.[Total_Gross_Profit]) AS Period_GP
        ,Appearance_Count
    FROM basedata cgp
    GROUP BY cgp.std_StandardAcctNo
        ,cgp.Period_Date
        ,Appearance_Count
    )
    ,GP_Grouping
AS (
    SELECT std_StandardAcctNo
        ,min(Period_Date) AS range_start
        ,max(Period_Date) AS range_end
        ,count(*) AS range_count
        ,GP_group
    FROM (
        SELECT std_StandardAcctNo
            ,Period_Date
            ,CASE 
                WHEN Period_GP = 0
                    THEN 0
                ELSE 1
                END AS GP_Group
            ,row_number() OVER (
                PARTITION BY std_StandardAcctNo
                ,CASE 
                    WHEN Period_GP = 0
                        THEN 0
                    ELSE 1
                    END ORDER BY Period_Date
                ) AS rn
            ,row_number() OVER (
                PARTITION BY std_StandardAcctNo
                ,CASE 
                    WHEN Period_GP = 0
                        THEN 0
                    ELSE 1
                    END ORDER BY Period_Date
                ) - row_number() OVER (
                PARTITION BY std_StandardAcctNo ORDER BY Period_Date
                ) AS grp
            ,row_number() OVER (
                PARTITION BY std_StandardAcctNo ORDER BY Period_Date
                ) AS grp2
        FROM GroupedData
        ) a
    GROUP BY std_StandardAcctNo
        ,grp
        ,GP_Group 
    )
    ,GP_Group2
AS (
    SELECT gd.*
        ,max(gpg_prev.range_end) AS last_zero_group -- , gpg.range_count  
    FROM GroupedData gd
    LEFT JOIN GP_Grouping gpg ON gd.std_StandardAcctNo = gpg.std_StandardAcctNo
        AND gd.Period_Date BETWEEN gpg.range_start
            AND gpg.range_end
    LEFT JOIN (
        SELECT *
        FROM GP_Grouping
        WHERE GP_Group = 0
            AND range_count >= 12
        ) gpg_prev ON gpg_prev.std_StandardAcctNo = gd.std_StandardAcctNo
        AND gpg.range_start > gpg_prev.range_end
    GROUP BY gd.std_StandardAcctNo
        ,Period_Date
        ,Period_GP
        ,Appearance_Count
        ,gpg.range_count 
    )
    ,GP_Group3
AS (
    SELECT gd.*
        ,Appearance_Cnt_Rel = CASE 
            WHEN gd.last_zero_group IS NULL
                THEN Appearance_Count
            ELSE ROW_NUMBER() OVER (
                    PARTITION BY gd.std_StandardAcctNo
                    ,gd.last_zero_group ORDER BY gd.Period_Date
                    )
            END
    FROM GP_Group2 gd
    )
    ,almost_done
AS (
    SELECT DISTINCT bd.order_num
        ,bd.Period_Date
        ,bd.std_the_year
        ,bd.std_the_month
        ,bd.std_StandardAcctNo
        ,CASE 
            WHEN bd.[Appearance_Count] > 0
                THEN bd.[Appearance_Count]
            WHEN isnull(c.Appearance_Count, 0) > 0
                THEN c.Appearance_Count + 1
            WHEN isnull(d.Appearance_Count, 0) > 0
                THEN d.Appearance_Count + 2
            WHEN isnull(e.Appearance_Count, 0) > 0
                THEN e.Appearance_Count + 3
            ELSE 0
            END AS Appearance_Count
        ,bd.[The_Year]
        ,bd.[The_Month]
        ,bd.[CustomerName]
        ,bd.[Gallon_Qty]
        ,bd.[Total_Sale]
        ,isnull(c.Appearance_Count, 0) AS Prev_Count
        ,isnull(d.Appearance_Count, 0) AS month2_Count
        ,isnull(e.Appearance_Count, 0) AS month3_Count
        ,CASE 
            WHEN bd.SalesPerson_Name IS NOT NULL
                THEN bd.SalesPerson_Name
            WHEN c.SalesPerson_Name IS NOT NULL
                THEN c.SalesPerson_Name
            WHEN d.SalesPerson_Name IS NOT NULL
                THEN d.SalesPerson_Name
            WHEN e.SalesPerson_Name IS NOT NULL
                THEN e.SalesPerson_Name
            ELSE 'NA'
            END [SalesPerson] 
        ,CASE 
            WHEN bd.[Appearance_Count] IS NULL
                AND c.[Appearance_Count] IS NULL
                AND d.[Appearance_Count] IS NULL
                THEN e.Period_GP
            ELSE 0
            END [Lost_Gross_Profit]
        ,CASE 
            WHEN bd.Appearance_Count = 1
                THEN bd.Total_Gross_Profit
            ELSE 0
            END AS 'New_Cust_GP'
        ,CASE 
            WHEN bd.Appearance_Count <= 12
                THEN bd.Total_Gross_Profit
            ELSE 0
            END AS 'Young_Cust_GP'
        ,CASE 
            WHEN bd.Appearance_Count > 12
                THEN bd.Total_Gross_Profit
            ELSE 0
            END AS 'Old_Cust_GP'
        ,ROW_NUMBER() OVER (
            PARTITION BY bd.std_StandardAcctNo
            ,bd.std_The_Year
            ,bd.std_The_Month ORDER BY (bd.std_StandardAcctNo) DESC
            ) AS UNI_Period
        ,bd.Total_Gross_Profit AS SalesP_GP
        ,isnull(cg.Period_gp, 0) AS Period_gp
        ,CASE 
            WHEN isnull(b_prev.Period_gp, 0) > 0
                THEN isnull(b_prev.Period_gp, 0)
            WHEN isnull(d.Period_gp, 0) > 0
                THEN isnull(d.Period_gp, 0)
            WHEN isnull(e.Period_gp, 0) > 0
                THEN isnull(e.Period_gp, 0)
            ELSE 0
            END AS Prev_Period_GP
        ,h.Mat_MoM_Shift
        ,CASE 
            WHEN isnull(b_prev.Period_gp, 0) > 0
                THEN isnull(b_prev.Period_gp, 0)
            WHEN isnull(d.Period_gp, 0) > 0
                THEN isnull(d.Period_gp, 0)
            WHEN isnull(e.Period_gp, 0) > 0
                THEN isnull(e.Period_gp, 0)
            ELSE 0
            END * h.Mat_MoM_Shift AS Expected_GP
        ,isnull(c.Period_gp, 0) AS True_Prev_GP
        ,isnull(d.Period_gp, 0) AS True_2month_GP
        ,isnull(e.Period_gp, 0) AS True_3month_GP
        ,ideal_candidate = CASE 
            WHEN ((isnull(c.Period_gp, 0) + isnull(d.Period_gp, 0) + isnull(bd.Total_Gross_Profit, 0)) / 3 >= 800)
                AND isnull(c.Period_gp, 0) >= 150
                AND isnull(d.Period_gp, 0) >= 150
                AND isnull(bd.Total_Gross_Profit, 0) >= 150
                THEN 'Y'
            ELSE 'N'
            END
        ,eleph_candidate = CASE 
            WHEN ((isnull(c.Period_gp, 0) + isnull(d.Period_gp, 0) + isnull(bd.Total_Gross_Profit, 0)) / 3 >= 5000)
                AND isnull(c.Period_gp, 0) >= 1000
                AND isnull(d.Period_gp, 0) >= 1000
                AND isnull(bd.Total_Gross_Profit, 0) >= 1000
                THEN 'Y'
            ELSE 'N
'
            END
    FROM basedata bd
    LEFT JOIN core_GP b_prev ON bd.std_StandardAcctNo = b_prev.Customer_Alias
        AND b_prev.Period_Date = dateadd(month, - 1, bd.Period_Date)
    LEFT JOIN saleslist c ON c.std_StandardAcctNo = bd.std_StandardAcctNo
        AND c.Period_Date = dateadd(month, - 1, bd.Period_Date)
        AND CASE 
            WHEN bd.SalesPerson_Name IS NOT NULL
                THEN bd.SalesPerson_Name
            ELSE c.SalesPerson_Name
            END = c.SalesPerson_Name
    LEFT JOIN saleslist d ON d.std_StandardAcctNo = bd.std_StandardAcctNo
        AND d.Period_Date = dateadd(month, - 2, bd.Period_Date)
        AND CASE 
            WHEN bd.SalesPerson_Name IS NOT NULL
                THEN bd.SalesPerson_Name
            WHEN c.SalesPerson_Name IS NOT NULL
                THEN c.SalesPerson_Name
            ELSE d.SalesPerson_Name
            END = d.SalesPerson_Name
    LEFT JOIN saleslist e ON e.std_StandardAcctNo = bd.std_StandardAcctNo
        AND e.Period_Date = dateadd(month, - 3, bd.Period_Date)
        AND CASE 
            WHEN bd.SalesPerson_Name IS NOT NULL
                THEN bd.SalesPerson_Name
            WHEN c.SalesPerson_Name IS NOT NULL
                THEN c.SalesPerson_Name
            WHEN d.SalesPerson_Name IS NOT NULL
                THEN d.SalesPerson_Name
            ELSE e.SalesPerson_Name
            END = e.SalesPerson_Name
    LEFT JOIN RicoCustom.dbo.[Rico_Global_Monthly] h ON h.month = bd.std_the_month
    LEFT JOIN core_GP cg ON bd.std_StandardAcctNo = cg.Customer_Alias
        AND cg.Period_Date = bd.Period_Date
    )
    ,get_ideal
AS (
    SELECT DISTINCT min(ad.Period_Date) AS ideal_Period_Date
        ,ad.std_StandardAcctNo
        ,rc.last_zero_group
    FROM almost_done ad
    LEFT JOIN GP_Group3 rc ON rc.Period_Date = ad.Period_Date
        AND rc.std_StandardAcctNo = ad.std_StandardAcctNo
        AND rc.Period_GP = ad.Period_gp
    WHERE ideal_candidate = 'Y'
        AND (
            rc.Appearance_Cnt_Rel BETWEEN 3
                AND 6
            ) 
    GROUP BY ad.std_StandardAcctNo
        ,rc.last_zero_group
    )
    ,get_elephant
AS (
    SELECT DISTINCT min(ad.Period_Date) AS eleph_Period_Date
        ,ad.std_StandardAcctNo
        ,rc.last_zero_group
    FROM almost_done ad
    LEFT JOIN GP_Group3 rc ON rc.Period_Date = ad.Period_Date
        AND rc.std_StandardAcctNo = ad.std_StandardAcctNo
        AND rc.Period_GP = ad.Period_gp
    WHERE eleph_candidate = 'Y'
        AND (
            rc.Appearance_Cnt_Rel BETWEEN 3
                AND 36
            ) 
    GROUP BY ad.std_StandardAcctNo
        ,rc.last_zero_group
    )
SELECT rc.Appearance_Cnt_Rel
    ,gi.ideal_Period_Date
    ,ge.eleph_Period_Date
    ,ad.*
FROM almost_done ad
LEFT JOIN GP_Group3 rc ON rc.Period_Date = ad.Period_Date
    AND rc.std_StandardAcctNo = ad.std_StandardAcctNo
    AND rc.Period_GP = ad.Period_gp
LEFT JOIN get_ideal gi ON ad.std_StandardAcctNo = gi.std_StandardAcctNo
    AND ad.Period_Date = gi.ideal_Period_Date
LEFT JOIN get_elephant ge ON ad.std_StandardAcctNo = ge.std_StandardAcctNo
    AND ad.Period_Date = ge.eleph_Period_Date
WHERE order_num > 201001

我的索引存在于源表 Complete_Sales_V2 上。如下：

索引说明： clustered, unique, primary key located on PRIMARY

索引键： Customer_Alias, SalesPerson_Name, year, month, Invoice_Number

标签： sql-servertsql

Answer 1

既然您说您可以查询视图，我建议在执行查询之前将您的视图选择到临时表中。这将简化查询计划，允许您对其进行检查。并有望加快速度。

我注意到在您的原始查询中，您正在根据连接条件中的 @Start 和 @End 过滤主表。我不认为你想那样做。我认为它应该在where子句中。

您也可以预先过滤临时表，只要您可以确定可能需要哪些记录（我不能，因为您比较了 3 个不同的日期）。

DECLARE @start DATE = '7-1-2019', @end DATE = '8-20-2019';

-- Best practice is to list the actual columns required
-- You may also be able to pre-filter here based on @start and @end
-- But as you compare them to 3 different columns in the query I don't know enough about your logic to know if this is possible or not
-- You can also add indexes to the temp table if they would speed things up
select *
into #cg
from [CustSalesTrend_Growth];

SELECT cg.*
    , ba.std_StandardAcctNo AS bonus_acct
FROM #cg cg
LEFT JOIN (
    SELECT DISTINCT std_standardacctno
    FROM #cg
    WHERE eleph_Period_Date BETWEEN @Start AND @End
    OR ideal_Period_Date BETWEEN @Start AND @End
) ba ON ba.std_StandardAcctNo = cg.std_StandardAcctNo
WHERE [Appearance_Count] <> 0
AND Period_gp <> 0
AND cg.Period_Date >= @Start
AND cg.Period_Date <= @End
ORDER BY cg.std_StandardAcctNo;

drop table #cg;