Stimmung einer Gruppe, die langsam läuft

Das folgende SQL dauert 5 Sekunden auf einer database mit der größten Tabelle PremiseProviderBillings mit 350.000 datasätzen. Aber auf derselben database mit 1,5 Millionen Platten dauert es eine Minute

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,'')='' 

Wenn ich dann den inneren SQL-Teil ausführe, der die Werte auf der kleineren database summiert, dauert es 2 Sekunden. Auf der größeren database dauert es 34 Sekunden, inneres SQL unten …

 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 

So ist klar, dass diese SQL einfach nicht skalierbar ist. Angesichts der Tatsache, dass die database wachsen wird, welche Technik sollte man sich bewerben, um dies zu verbessern?

Ich habe alle Joins überprüft, um sicherzustellen, dass es keine fehlenden Indizes gibt, um sicherzustellen, dass alle Joins Key-basiert und ok sind

Ich hätte gedacht, dass dieser Ansatz in Ordnung ist, aber sollte ich die Struktur des SQL ändern, ist dies nicht skalierbar und ineffizient?

Grüße

Wenn Sie die Abfrage oft genug verwenden und je nachdem, wie oft Sie auf die Tabellen schreiben, lohnt es sich, eine indizierte view dafür zu erstellen. Es ist jedoch bemerkenswert, dass dies Spekulation ist, und indizierte viewen kommen mit einem Trade aus, Ihre Lesungen werden schneller, aber Ihre Schriften werden langsamer sein.

 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 

Dann müssen Sie nur Ihre view mit dem Hinweis NOEXPAND um sicherzustellen, dass der Index verwendet wird.

 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,'') = ''; 

Wie bei jeder Abfrage Tuning Frage, sind Sie die einzige mit allen Informationen benötigt, um richtig zu beantworten. In meiner Erfahrung (die in erster Linie in Abrechnungssystemen) ist, sind indizierte viewen wie diese in der Regel in Ordnung mit Abrechnungsdaten, da die meisten Rechnungsläufe zyklisch sind, und so schreibt man in Batches statt kontinuierlich und liest auch dazu, schreibt, weil die data überwiegen ist static, sobald eine Rechnung erstellt ist, dann ist es sehr selten aktualisiert.

Versuchen Sie mit einem gemeinsamen Tabellenausdruck für Ihre innere Abfrage, könnte es die Dinge ein wenig beschleunigen.

 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,'')=''