通过运行速度较慢的方法调优组

Question

下面的SQL在最大表PremiseProviderBillings具有350,000条记录的数据库上耗时5秒。但在具有150万条记录的同一数据库上，它需要一分钟以上的时间

SELECT
   n.CustomerInvoiceNumberId as InvoiceNo,C.CustomerBillId,c.customerid, S.Volumetric, S.Fixed, S.VAT, S.Discount, C.Debit,c.EffectiveDate,c.TransactionDateTime,s.Consumption,r.CustomerCreditNoteId--,s.Volumetric + s.Fixed + s.Vat - s.discount - c.debit as variance
FROM
  CustomerPayments C 
INNER JOIN
  (SELECT
     CustomerBillId, SUM(a.VolumetricCharge) as Volumetric,SUM(a.FixedCharge) as Fixed,
     SUM(a.VAT) as VAT,SUM(a.Discount) as Discount,sum(a.EstimatedConsumption) as Consumption
   FROM
     PremiseProviderBillings a, PremiseProviderBills b
    WHERE a.PremiseProviderBillId = b.PremiseProviderBillId
   GROUP BY
     CustomerBillId) S
ON
  C.CustomerBillId = S.CustomerBillId 
  and debit <> 0 -- hide credit note lines, we mark these results with customerCreditNoteId to show they have been credited
INNER JOIN dbo.CustomerInvoiceNumbers n on c.CustomerBillId = n.CustomerBillId
left OUTER JOIN
           dbo.CustomerCreditNotes AS r ON c.CustomerPaymentId = r.CustomerPaymentId
where isnull(c.transactionDateTimeEnd,'')=''

如果我随后运行内部SQL部分，对较小的数据库上的值求和，则需要2秒。在更大的数据库上需要34秒，内部SQL在下面...

SELECT
     CustomerBillId, SUM(a.VolumetricCharge) as Volumetric,SUM(a.FixedCharge) as Fixed,
     SUM(a.VAT) as VAT,SUM(a.Discount) as Discount,sum(a.EstimatedConsumption) as Consumption
   FROM
     PremiseProviderBillings a, PremiseProviderBills b
    WHERE a.PremiseProviderBillId = b.PremiseProviderBillId
   GROUP BY
     CustomerBillId

所以很明显，这个SQL是不可伸缩的。考虑到数据库将会增长，应该采用什么技术来改进这一点？

我已经检查了所有连接，以确保没有丢失索引，好吧，以确保所有连接都是基于键的，并且没有问题。

我可能会认为这种方法是可以的，但是我是否应该改变SQL的结构，这是不是不可伸缩和低效的？

问候

Answer 1

如果您足够频繁地使用查询，并且根据您写入表的频率，为此创建一个indexed view可能是值得的。然而，值得注意的是，这只是一种猜测，索引视图确实是有权衡的，你的读取速度会更快，但你的写入速度会更慢。

CREATE VIEW dbo.CustomerBillingView
WITH SCHEMABINDING
AS
    SELECT  b.CustomerBillId,
            SUM(a.VolumetricCharge) AS Volumetric,
            SUM(a.FixedCharge) AS Fixed,
            SUM(a.VAT) AS VAT,
            SUM(a.Discount) AS Discount,
            SUM(a.EstimatedConsumption) AS Consumption,
            COUNT_BIG(*) AS Records -- REQUIRED TO CREATE INDEX
    FROM    dbo.PremiseProviderBillings a
            INNER JOIN dbo.PremiseProviderBills b
                ON a.PremiseProviderBillId = b.PremiseProviderBillId
    GROUP BY b.CustomerBillId;
GO

CREATE UNIQUE CLUSTERED INDEX UQ_CustomerBillingView__CustomerBillId
    ON dbo.CustomerBillingView (CustomerBillId);

GO

然后，您只需要使用带有提示NOEXPAND的视图，以确保使用索引。

SELECT  n.CustomerInvoiceNumberId as InvoiceNo,
        c.CustomerBillId,
        c.customerid, 
        s.Volumetric, 
        s.Fixed, 
        s.VAT, 
        s.Discount, 
        c.Debit,
        c.EffectiveDate,
        c.TransactionDateTime,
        s.Consumption,
        r.CustomerCreditNoteId
        --,s.Volumetric + s.Fixed + s.Vat - s.discount - c.debit as variance
FROM    CustomerPayments AS c 
        INNER JOIN dbo.CustomerBillingView AS s WITH (NOEXPAND)
            ON c.CustomerBillId = s.CustomerBillId 
            AND c.Debit <> 0 
            -- hide credit note lines, we mark these results with customerCreditNoteId to show they have been credited
        INNER JOIN dbo.CustomerInvoiceNumbers n 
            ON c.CustomerBillId = n.CustomerBillId
        LEFT OUTER JOIN dbo.CustomerCreditNotes AS r 
            ON c.CustomerPaymentId = r.CustomerPaymentId
WHERE   ISNULL(c.transactionDateTimeEnd,'') = '';

与每个查询调优问题一样，您是唯一一个拥有正确回答所需信息的人。根据我的经验(主要是在帐单系统中)，像这样的索引视图通常可以处理帐单数据，因为大多数发票运行是周期性的，所以写入是批量的，而不是连续的，而且读取的数据也往往超过写入，因为数据是静态的，一旦发票创建，它很少被更新。

Answer 2

试着为你的内部查询使用一个通用表表达式，它可能会让事情变得更快一些。

WITH CTE AS
(
    SELECT
         CustomerBillId, SUM(a.VolumetricCharge) as Volumetric,SUM(a.FixedCharge) as Fixed,
         SUM(a.VAT) as VAT,SUM(a.Discount) as Discount,sum(a.EstimatedConsumption) as Consumption
       FROM
         PremiseProviderBillings a, PremiseProviderBills b
        WHERE a.PremiseProviderBillId = b.PremiseProviderBillId
       GROUP BY
         CustomerBillId
)   

SELECT
   n.CustomerInvoiceNumberId as InvoiceNo,C.CustomerBillId,c.customerid, S.Volumetric, S.Fixed, S.VAT, S.Discount, C.Debit,c.EffectiveDate,c.TransactionDateTime,s.Consumption,r.CustomerCreditNoteId--,s.Volumetric + s.Fixed + s.Vat - s.discount - c.debit as variance
FROM
  CustomerPayments C 
INNER JOIN
  CTE S
ON
  C.CustomerBillId = S.CustomerBillId 
  and debit <> 0 -- hide credit note lines, we mark these results with customerCreditNoteId to show they have been credited
INNER JOIN dbo.CustomerInvoiceNumbers n on c.CustomerBillId = n.CustomerBillId
left OUTER JOIN
           dbo.CustomerCreditNotes AS r ON c.CustomerPaymentId = r.CustomerPaymentId
where isnull(c.transactionDateTimeEnd,'')=''