sql-server - SQL Server：While 循环需要很长时间（12 秒）才能仅处理 2000 条记录

Question

我必须使用 while 循环遍历一组记录。但是使用 while 循环，仅 2000 条记录大约需要 12 秒。

我创建了一个临时表#tempped并使用查询的结果集填充它，该查询使用 while 循环来计算截至当前日期的每个客户的待定分期付款金额和总短期金额。

create table #tempped (
 SalesInvoiceID int,
 CustomerID int,
 Name varchar(50),
 Mobile varchar(11),
 Guarantor varchar(50),
 Mobile1 varchar(11),
 Category varchar(100),
 CompanyID varchar(50),
 ModelNo varchar(30),
 TotalBill int,
 TotalPaidAmount int,
 TotalDiscountAmount int,
 Region varchar(60),
 EmpName varchar(50),
 Type varchar(50),
 EmployeeID int,
 Installment int,
 ShortAmount int);

DECLARE @site_value INT;
DECLARE @counter INT;
SET
   @site_value = (select count(Distinct SalesInvoiceID) from InstallmentPaymentHistory )
set   @counter=0
WHILE @site_value>@counter 
BEGIN
SET
   @counter = @counter+1;

INSERT INTO #tempped


select
   a.SalesInvoiceID,
   a.CustomerID,
   a.Name,
   a.Mobile,
   a.Guarantor,
   a.Mobile1,
   a.Category,
   a.CompanyID,
   a.ModelNo,
   a.TotalBill,
   a.TotalPaidAmount,
   a.TotalDiscountAmount,
   a.Region,
   e.EmpName,
   a.Type,
   a.EmployeeID,
   CASE
      WHEN
         a.SpecialInsttPlan = 'No' 
      then
         a.Installment 
      else
      ( 
         select
            sip.Amount 
         from
            SpecialInsttPlan sip 
         where
            a.Installment = sip.InsttNo 
            and sip.SalesInvoiceID = a.SalesInvoiceID) 
   end
   as Installment,
   ISNULL(a.ShortAmount, 0) as ShortAmount 
         from
            (
               select Distinct
                  s.DiscountApplied,
                  s.SalesInvoiceID,
                  s.DateSold,
                  cust.CustomerID,
                  cust.Name,
                  cust.Mobile1 as Mobile,
                  g.Name as Guarantor,
                  g.Mobile1,
                  cat.Category,
                  p.CompanyID,
                  p.ModelNo,
                  s.SpecialInsttPlan,
                  s.TotalBill,
                  c.EmployeeID,
                  cust.Region,
                  c.Type,
                  s.DiscountAtEnd,
                  CASE
                     WHEN
                        s.SpecialInsttPlan = 'No' 
                     THEN
                        (s.TotalBill - s.Advance) / s.Installments 
                     ELSE
                    ( 
                        select
                           i.InsttNo 
                        from
                           InstallmentPaymentHistory i 
                        where
                           i.DD = 
                           (
                              select
( 
                                 CASE
                                    WHEN
                                       a.d1 != '' 
                                    THEN
                                       a.d1 
                                    ELSE
                                       case
                                          when
                                             b.d2 != '' 
                                          then
                                             b.d2 
                                          else
                                             c.d3 
                                       end
                                 END
) 
                              from
                                 (
                                    select
                                       max(iph.DD)as d1 
                                    from
                                       InstallmentPaymentHistory iph 
                                    where
                                       iph.Status = 'Pending' 
                                       and iph.SalesInvoiceID = @counter 
                                       and iph.DD <= '2020-12-31' 
                                 )
                                 as a,
                                 (
                                    select
                                       max(iph.DD) as d2 
                                    from
                                       InstallmentPaymentHistory iph 
                                    where
                                       iph.Status = 'Up Coming' 
                                       and iph.SalesInvoiceID = @counter 
                                       and iph.DD <= '2020-12-31' 
                                 )
                                 as b,
                                 (
                                    select
                                       max(iph.DD) as d3 
                                    from
                                       InstallmentPaymentHistory iph 
                                    where
                                       iph.Status = 'Partial' 
                                       and iph.SalesInvoiceID = @counter 
                                       and iph.DD <= '2020-12-31' 
                                 )
                                 as c 
                           )
                           and i.SalesInvoiceID = @counter ) 
                  END
                  as Installment,
                  s.Advance + sum(iph.Amount) as TotalPaidAmount,
                  case
                     when
                        s.DiscountApplied = 'Yes' 
                     then
                        ISNULL(s.DiscountAtEnd, 0) + ISNULL(sum(iph.Discount), 0) 
                     else
                        ISNULL(sum(iph.Discount), 0) 
                  end
                  as TotalDiscountAmount, 
                  case
                     when
                        s.DiscountApplied = 'Yes' 
                     then
                     (
                        SELECT
                           b.amount - a.amount - ISNULL(c.DiscountAtEnd, 0) - ISNULL(a.InsttDiscount, 0) AS ShortAmount 
                        FROM
                           (
                              SELECT
                                 Sum(amount) AS Amount,
                                 ISNULL(sum(Discount), 0) as InsttDiscount 
                              FROM
                                 installmentpaymenthistory 
                              WHERE
                                 salesinvoiceid = @counter 
                           )
                           AS a, 
                           (
                              select
                                 sum(Amount) as Amount 
                              from
                                 SpecialInsttPlan 
                              where
                                 salesinvoiceid = @counter 
                                 and '2020-12-31' >= DD 
                           )
                           as b, 
                           (
                              select
                                 DiscountAtEnd 
                              from
                                 SalesInvoice 
                              where
                                 salesinvoiceid = @counter 
                           )
                           as c) 
                        else
                       ( 
                           SELECT
                               (b.amount - a.amount - a.InsttDiscount ) AS ShortAmount 
                           FROM
                              (
                                 SELECT
                                    Sum(amount) AS Amount,
                                    ISNULL(sum(Discount), 0) as InsttDiscount 
                                 FROM
                                    installmentpaymenthistory 
                                 WHERE
                                    salesinvoiceid = @counter 
                              )
                              AS a, 
                              (
                                 select
                                    sum(Amount) as Amount 
                                 from
                                    SpecialInsttPlan 
                                 where
                                    salesinvoiceid = @counter 
                                    and '2020-12-31' >= DD 
                              )
                              as b) 
                  end
                  as ShortAmount 
                           from
                              SalesInvoice s 
                              inner join
                                 InstallmentPaymentHistory iph 
                                 on iph.SalesInvoiceID = s.SalesInvoiceID 
                              left join
                                 Customer cust 
                                 on s.CustomerID = cust.CustomerID 
                              left join
                                 Guarantor g 
                                 on cust.Guarantor1ID = g.GuarantorID 
                              left join
                                 SalesInvoiceProductsList sipl 
                                 on sipl.SalesInvoiceID = s.SalesInvoiceID 
                              left join
                                 Product p 
                                 on sipl.ProductID = p.ProductID 
                              left join
                                 Category cat 
                                 on p.CatID = cat.CatID 
                              left join
                                 Commission c 
                                 on s.SalesInvoiceID = c.SalesInvoiceID 
                           where
                              iph.SalesInvoiceID = @counter 
                           group by
                              s.SalesInvoiceID,
                              s.DateSold,
                              s.TotalBill,
                              s.Installments,
                              s.Discount,
                              s.DiscountAtEnd,
                              s.Advance,
                              cust.Name,
                              cust.Mobile1,
                              g.Name,
                              g.Mobile1,
                              cust.CustomerID,
                              s.TotalBill,
                              s.SpecialInsttPlan,
                              p.CompanyID,
                              p.ModelNo,
                              cat.Category,
                              cust.Region,
                              c.EmployeeID,
                              c.Type,
                              s.DiscountAtEnd,
                              s.DiscountApplied 
            )
            as a 
            left join
               Employee e 
               on a.EmployeeID = e.EmployeeID 


END

select * from #tempped order by SalesInvoiceID
drop table #tempped

如何改进查询？请使用任何建议的方法等修改我的查询以产生相同recursive cte的cursor结果

输出

标签： sql-server