SQL-Abfrage, die zu lange dauert, gibt es irgendwelche Optimierungen möglich, müssen in die richtige Richtung gerichtet werden

Frage

Ich habe vor kurzem über CTE's gelernt und wie man sie für die Performance gewinnt, aber ich denke, ich überhöre sie in diesem Fall. Die untere Abfrage dauert etwa 1 Minute mit nur 1000 Prüfungen und ich bin sicher, dass es viel schneller gemacht werden kann. Bitte schlagen Sie Optimierungen oder Begriffe vor, die mich in die richtige Richtung zeigen können?

Kontext

Um die Abfrage zu verstehen, musst du einen Kontext haben, also hier ist es. In der database haben wir Prüfungen, die auf einem object durchgeführt werden (EG ein Stift muss geprüft werden, wenn es schreiben kann). Bevor eine Prüfung beginnen kann, muss eine Prüfung anerkannt werden, so dass das bestätigte date vorliegt . Natürlich sparen wir auch, wenn eine Untersuchung erstellt wird ( erstellt ). Wenn eine Prüfung fertig ist ( Ready Date ), muss es ein Ergebnis erhalten. Ein Ergebnis kann genehmigt ( isapproved ) oder abgelehnt werden (wird abgelehnt ). Die Bedeutung dieser Abfrage ist die Erstellung eines Graphen, der die 5 Prüfungsbedingungen pro Tag anzeigt.

Zählungen pro Tag:

  1. examinations_created_count
  2. examinations_acknowledged_count
  3. examinations_ready_count
  4. examinations_ready_and_approved_count
  5. examinations_ready_and_rejected_count

Tatsächliche Abfrage

DECLARE @ProjectID [bigint] = 3 DECLARE @LocationID [bigint] = 2 DECLARE @PersonID [bigint] = 1058 DECLARE @StartDate [datetime] = '2012-01-01' DECLARE @EndDate [datetime]= '2014-12-31' ;WITH ExaminationCTE(acknowledgedate, readydate, createdate, isrejected, isapproved) AS ( SELECT [acknowledgedate], [readydate], createdate, [isrejected], [isapproved] FROM [dbo].[Examination] LEFT JOIN [dbo].[Object] [o] ON [dbo].[Examination].[fk_objectid] = [o].[pk_objectid] LEFT JOIN [dbo].[ExaminationResults] [er] ON [dbo].[Examination].[fk_examinationid] = [er].[pk_examinationid] WHERE [fk_projectid] = @ProjectID AND [fk_locationid] = @LocationID AND [fk_personid] = @PersonID ), dates(Date) AS ( --Get all the dates between the selected range SELECT @StartDate as Date UNION ALL SELECT DATEADD(d,1,[Date]) FROM dates WHERE DATE < @EndDate ) -- Select all the counts from the examination CTE SELECT d.Date, (SELECT COUNT(*) FROM [ExaminationCTE] WHERE [createdate] <= [d].[Date]) AS 'examinations_created_count', (SELECT COUNT(*) FROM [ExaminationCTE] WHERE [acknowledgedate] <= [d].[Date]) AS 'examinations_acknowledged_count', (SELECT COUNT(*) FROM [ExaminationCTE] WHERE [readydate] <= [d].[Date]) AS 'examinations_ready_count', (SELECT COUNT(*) FROM [ExaminationCTE] WHERE [readydate] <= [d].[Date] AND [isapproved] = 1) AS 'examinations_ready_and_approved_count', (SELECT COUNT(*) FROM [ExaminationCTE] WHERE [readydate] <= [d].[Date] AND [isrejected] = 1) AS 'examinations_ready_and_rejected_count' FROM dates d OPTION (MAXRECURSION 0) 

Während die Abfrage arbeitet im Moment ist es nicht erfüllen meine performance Spezifikationen. Ich glaube, das kann schön gemacht werden.

UPDATE: pathen der Hilfe von @GordonLinoff bin ich fast da. Allerdings muss ich die NULL-Zeilen mit dem letzten möglichen Wert füllen.

Dieses Bild zeigt, was die Nullwerte sein müssen

Im image sehen Sie die Ergebnisse sind null die Pfeile, weist darauf hin, was der Wert sein sollte, für den ersten Satz von NULL Ich muss den Wert aus dem letzten verfügbaren date vor dem ausgewählten Startdatum zu bekommen.

Ja, das kannst du effizienter machen. Du machst eine ganze Reihe von korrelierten Nicht-Equijoins und zählst dann die Ergebnisse.

Nehmen Sie einen anderen Ansatz. Ein einfacher Ansatz wäre, die ExaminationCTE nach date zusammenzufassen, um die Ergebnisse zusammenzufassen. Allerdings ist in SQL server der CTE nicht materialisiert, so dass dies möglicherweise nicht die performance ändern.

Ein anderer path ist, die Zählung mit windowsfunktionen zu tun. Hier ist ein Beispiel für das erste Feld:

 from dates left outer join (select createdate, max(seqnum) as examinations_created_count from (select createdate, row_number() over (order by createdate) as seqnum from ExaminationCTE ) e group by createdate ) ecc on dates.date = ecc.createdate 

Die anderen würden ähnlich sein. In der Tat können Sie alle variables in einer Unterabfrage setzen.

Die Logik besteht darin, jeder Zeile eine laufende Nummer hinzuzufügen und dann die maximale Anzahl pro date zu nehmen. Das ist die Zählung.