时间戳

首页 > 解决方案 > SQL：我需要关于如何最好地完成复杂查询的建议

sql-server - SQL：我需要关于如何最好地完成复杂查询的建议

问题描述

我正在处理需要为报告生成的复杂查询。该查询给了我我需要的东西，但运行大约需要 5 分钟。对于报告，这是不可接受的，因此我想通过通宵作业将这些结果转储到表格中，并在每次运行之前截断表格。考虑到查询的结构，我遇到麻烦的地方是如何最好地完成此任务，因此我希望获得有关如何执行此操作的输入。我将尽我所能将查询分解为下面一英里高的视图：

CREATE TABLE #SALESDATA
(
...
)

INSERT INTO #SALESDATA
EXEC STAGING_DATA_PROC

WITH CTE1 AS 
(
PRELIMINARY DATA 1
JOINED WITH #SALESDATA
),

CTE2 AS
(
PRELIMINARY DATA 2
JOINED WITH #SALESDATA
)

所以我第一次尝试做的是试图绕过 STAGING_DATA_PROC 通过在它的位置进行查询，但它本身包含一些插入到临时表中，并且似乎不喜欢按该顺序嵌套插入。

对我如何解决这个问题有任何见解吗？

更新 1：这是运行大约 5 分钟的 SQL。从那以后，我已经将我的 SP 替换为将临时表馈送到一个可以立即给出结果的独立表。

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 as std_StandardAcctNo
        ,b.salesperson
        ,isnull(ac.counter,0) [Appearance_Count]
        ,isnull(b.[Year],0) [The_Year]
        ,isnull(b.[Month],0) [The_Month]
        ,isnull(b.customer,0) [CustomerName]
        ,sum(isnull(b.Gallons,0)) [Gallon_Qty]
        ,sum(isnull(b.sales,0)) [Total_Sale]
        ,sum(isnull(b.gm,0)) [Total_Gross_Profit]
from (select distinct a.year
                ,a.month
                ,b.customer
                ,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 IdealElephantSalesData a
    join (select distinct customer from IdealElephantSalesData) b on 1 = 1
        ) a
        join RicoCustom..Appearance_Count ac on a.customer = ac.customer_alias
    left join IdealElephantSalesData b on a.customer = b.customer and a.Month = b.Month and a.Year = b.Year
group by a.order_num
        ,a.Period_Date
        ,a.year
        ,a.Month
        ,a.customer
        ,b.salesperson
        ,ac.counter
        ,b.[Year]
        ,b.[Month]
        ,b.customer
)

, saleslist as (
select distinct salesperson
                ,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, Appearance_Count, Period_Date, std_StandardAcctNo,std_the_month,std_the_year

), core_GP as (
select distinct a.customer
                ,convert(date,convert(varchar(2),a.month) + '/01/' + convert(varchar(4),a.year)) as Period_Date
                ,sum(a.gm) as Period_GP
from IdealElephantSalesData a
join RicoCustom..Appearance_Count ac on ac.customer_alias = a.customer
group by counter, convert(date,convert(varchar(2),a.month) + '/01/' + convert(varchar(4),a.year)), a.customer


), 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 
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 is not null then bd.salesperson 
            when c.salesperson is not null then c.salesperson
            when d.salesperson is not null then d.salesperson
            when e.salesperson is not null then e.salesperson 
            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 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 is not null then bd.salesperson else c.salesperson end = c.salesperson
    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 is not null then bd.salesperson when c.salesperson is not null then c.salesperson else d.salesperson end = d.salesperson
    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 is not null then bd.salesperson when c.salesperson is not null then c.salesperson when d.salesperson is not null then d.salesperson else e.salesperson end = e.salesperson
    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 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 


GO

标签： sql-server

解决方案

您是否尝试过将复杂的查询分解为较小的查询，将数据加载到临时表中。您可以使用临时表来构建最终结果。使用临时表，您可以针对它们构建必要的索引以确保更好的性能。此外，您将使用较小的数据子集，这应该会带来更好的性能。

推荐阅读