CTE sehr langsam, wenn er Kam

Ich habe etwas ähnliches vor, aber ich nähere mich dieser aus einer anderen Richtung jetzt also öffnete ich eine neue Frage. Ich hoffe, das ist OK.

Habe ich die Arbeit mit einem Allgemeinen Tabellenausdruck erstellt, die Summe der Gebühren auf Basis eines Elternteils Kostenlos. Die SQL und die details können hier eingesehen werden:

CTE-Index-Empfehlungen auf mehrere freigestellte Tabelle

Glaube ich nicht, ich bin etwas fehlt auf der CTE, aber ich bin immer ein problem, wenn ich es mit einer großen Datentabelle (3.5 Mio Zeilen).

Den Tisch tblChargeShare enthält einige andere Informationen, die ich brauche, wie ein InvoiceID, so dass ich legte meine CTE in einer Ansicht vwChargeShareSubCharges und trat an den Tisch.

Abfrage:

Select t.* from vwChargeShareSubCharges t
inner join 
tblChargeShare  s 
on t.CustomerID = s.CustomerID 
and t.MasterChargeID = s.ChargeID 
Where  s.ChargeID = 1291094

Gibt ein Ergebnis zurück, in ein paar ms.

Abfrage:

Select ChargeID from tblChargeShare Where InvoiceID = 1045854

Liefert 1, Zeile:

1291094

Aber die Abfrage:

Select t.* from vwChargeShareSubCharges t
inner join 
tblChargeShare  s 
on t.CustomerID = s.CustomerID 
and t.MasterChargeID = s.ChargeID 
Where  InvoiceID = 1045854

Dauert 2-3 Minuten zu laufen.

Ich gespeichert, den Ausführungsplänen und lud Sie in SQL Sentry. Der Baum für die schnelle Abfrage sieht wie folgt aus:

Den plan aus dem slow-query ist:

Ich habe versucht, reindexing, läuft die Abfrage durch tuning advisor und verschiedenen Kombinationen von sub-queries. Wenn der join enthält nichts anderes als die PK, die Abfrage ist langsam.

Hatte ich eine ähnliche Frage hier:

SQL Server-Abfrage-Timeout-je nach Where-Klausel

Die verwendeten Funktionen zu tun, die summimg der untergeordneten Zeilen statt einer CTE. Dies ist das umschreiben mit CTE, um zu versuchen und zu vermeiden, das gleiche problem hab ich jetzt erleben. Ich habe gelesen, die Antworten, die Antwort aber ich bin nicht klüger - ich Lesen Sie einige Informationen, Hinweise und Parameter, aber ich kann nicht damit es funktioniert. Ich hatte gedacht, dass das umschreiben mit einem CTE würde mein problem lösen. Die Abfrage ist schnell bei der Ausführung auf einem tblCharge mit ein paar tausend Zeilen.

Getestet, in beiden SQL 2008 R2 und SQL 2012

Edit:

Ich kondensiert die Abfrage in einer einzigen Anweisung, aber das gleiche Problem weiterhin besteht:

WITH RCTE AS
(
SELECT  ParentChargeId, s.ChargeID, 1 AS Lvl, ISNULL(TotalAmount, 0) as TotalAmount,  ISNULL(s.TaxAmount, 0) as TaxAmount,  
ISNULL(s.DiscountAmount, 0) as DiscountAmount, s.CustomerID, c.ChargeID as MasterChargeID
from tblCharge c inner join tblChargeShare s
on c.ChargeID = s.ChargeID Where s.ChargeShareStatusID < 3 and ParentChargeID is NULL

UNION ALL

SELECT c.ParentChargeID, c.ChargeID, Lvl+1 AS Lvl, ISNULL(s.TotalAmount, 0),  ISNULL(s.TaxAmount, 0),  ISNULL(s.DiscountAmount, 0) , s.CustomerID 
, rc.MasterChargeID 
from tblCharge c inner join tblChargeShare s
on c.ChargeID = s.ChargeID
INNER JOIN RCTE rc ON c.PArentChargeID = rc.ChargeID and s.CustomerID = rc.CustomerID  Where s.ChargeShareStatusID < 3 
)

Select MasterChargeID as ChargeID, rcte.CustomerID, Sum(rcte.TotalAmount) as TotalCharged, Sum(rcte.TaxAmount) as TotalTax, Sum(rcte.DiscountAmount) as TotalDiscount
from RCTE inner join tblChargeShare s on rcte.ChargeID = s.ChargeID and RCTE.CustomerID = s.CustomerID 
Where InvoiceID = 1045854
Group by MasterChargeID, rcte.CustomerID 
GO

---

Bearbeiten:
Spielt mehr,ich weiß einfach nicht verstehen.

Diese Abfrage ist sofortige (2ms):

Select t.* from
vwChargeShareSubCharges t
Where  t.MasterChargeID = 1291094

In der Erwägung, dass das dauert 3 Minuten:

DECLARE @ChargeID int = 1291094

Select t.* from
vwChargeShareSubCharges t
Where  t.MasterChargeID = @ChargeID

Auch wenn ich den Haufen von zahlen in einem "In", die Abfrage kommt immer noch instant:

Where  t.MasterChargeID in (1291090, 1291091, 1291092, 1291093,  1291094, 1291095, 1291096, 1291097, 1291098, 1291099, 129109)

---

Edit 2:

Kann ich replizieren diese von Grund auf neu mit diesem Beispiel Daten:

Ich habe einige dummy-Daten zu replizieren, das Problem. Es ist nicht so signifikant, wie ich nur Hinzugefügt, 100.000 Zeilen, sondern die schlechte Ausführung plan noch passiert (laufen im SQLCMD-Modus):

CREATE TABLE [tblChargeTest](
[ChargeID] [int] IDENTITY(1,1) NOT NULL,
[ParentChargeID] [int] NULL,
[TotalAmount] [money] NULL,
[TaxAmount] [money] NULL,
[DiscountAmount] [money] NULL,
[InvoiceID] [int] NULL,
CONSTRAINT [PK_tblChargeTest] PRIMARY KEY CLUSTERED 
(
[ChargeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,     ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO

Insert into tblChargeTest
(discountAmount, TotalAmount, TaxAmount)
Select ABS(CHECKSUM(NewId())) % 10, ABS(CHECKSUM(NewId())) % 100, ABS(CHECKSUM(NewId())) % 10
GO 100000

Update tblChargeTest
Set ParentChargeID = (ABS(CHECKSUM(NewId())) % 60000) + 20000
Where ChargeID = (ABS(CHECKSUM(NewId())) % 20000)
GO 5000

CREATE VIEW [vwChargeShareSubCharges] AS
WITH RCTE AS
(
SELECT  ParentChargeId, ChargeID, 1 AS Lvl, ISNULL(TotalAmount, 0) as TotalAmount,   ISNULL(TaxAmount, 0) as TaxAmount,  
ISNULL(DiscountAmount, 0) as DiscountAmount,  ChargeID as MasterChargeID
FROM tblChargeTest Where ParentChargeID is NULL

UNION ALL

SELECT rh.ParentChargeID, rh.ChargeID, Lvl+1 AS Lvl, ISNULL(rh.TotalAmount, 0),    ISNULL(rh.TaxAmount, 0),  ISNULL(rh.DiscountAmount, 0) 
, rc.MasterChargeID 
FROM tblChargeTest rh
INNER JOIN RCTE rc ON rh.PArentChargeID = rc.ChargeID --and rh.CustomerID =  rc.CustomerID 
)

Select MasterChargeID,  ParentChargeID, ChargeID, TotalAmount, TaxAmount, DiscountAmount , Lvl
FROM  RCTE r 
GO

Dann laufen diese zwei Abfragen:

--Slow Query:
Declare @ChargeID int = 60900

Select *
from [vwChargeShareSubCharges]
Where MasterChargeID = @ChargeID

--Fast Query:
Select *
from [vwChargeShareSubCharges]
Where MasterChargeID = 60900