Zeitblöcke bilden Komplex - Ist das ohne CURSOR möglich?
Frage
-
Moin zusammen,
ich stehe gerade vor einem scheinbar unlösbaren Problem.
Ich möchte die im Beispielskript durchgeführte Schleife nach Möglichkeit ohne Cursor realisieren. Hintergrund ist, dass diese Schleife je nach performance täglich (oder besser noch als Adhoc) aus 33Mio Datensätzen Zeitblöcke bilden soll. Die Lösung über diese Schleife bringt mich zwar zum Ziel, ist aber hoffentlich performanter möglich?!
Für 10Mio Datensätze braucht er ca. 6:30 Minuten. Ziel wären 30Mio unter 6 Minuten!
Ich habe bereits mit Funktionionen wie LAG und wilden Gruppierungen getestet, scheitere aber immer daran, das es für die Mischung an Zeiträumen keine festen Regeln gibt. Es können wirklich alle Konstellationen vorgkommen.
Für die Eindeutigkeit je Zeitblock nutze ich die PersonID (PID) in Kombination mit einem Schlüsselwert. Um die IF Bedingungen zu vereinfachen, verbinde ich diese beiden Werte zu einem Eindeutigen varchar.
Hier mal meine Schleife mit Testdaten:
SET NOCOUNT ON CREATE TABLE #t (ID int identity (1,1),PID int, SchluesselNr int, von datetime, bis datetime) INSERT INTO #t (PID, von, bis, SchluesselNr) VALUES (1,'1.1.2016','31.12.2016', 1) ,(1,'1.12.2016','31.12.2016', 1) ,(1,'1.1.2016','31.1.2016', 1) ,(1,'1.1.2017','30.06.2017', 1) ,(1,'3.1.2018','31.12.2018', 1) ,(1,'3.1.2018','31.12.2018', 2) ,(2,'1.1.2016','30.6.2016', 1) ,(2,'1.12.2016','31.12.2016', 1) ,(3,'1.01.2016','31.12.2016', 1) ,(3,'1.06.2016','31.7.2016', 1) ,(4,'1.01.2016','02.01.2016', 1) CREATE TABLE #ZeitRaumBloecke(ID int identity(1,1), GroupedID varchar(100), PID bigint, Schl int, GroupedVon datetime, GroupedBis datetime) DECLARE @KeyGroupID varchar(100) DECLARE @KeyGroupIDSave varchar(100) DECLARE @KeyPID bigint DECLARE @KeySchl int DECLARE @Beginn datetime DECLARE @Ende datetime DECLARE @EndeSave datetime DECLARE @InsertID bigint = -1 DECLARE my_cursor2 CURSOR --LOCAL FORWARD_ONLY STATIC READ_ONLY --FAST_FORWARD LOCAL FAST_FORWARD FOR SELECT CONVERT(VARCHAR(100),PID) + '_' + CONVERT(VARCHAR(100), SchluesselNr) , PID, SchluesselNr, von, bis FROM #t ORDER BY PID, von, bis desc -- Wichtig für die Abarbeitung OPEN my_cursor2 FETCH NEXT FROM my_cursor2 INTO @KeyGroupID, @KeyPID, @KeySchl, @Beginn, @Ende WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRANSACTION Test IF @KeyGroupID <> @KeyGroupIDSave OR @KeyGroupIDSave IS NULL BEGIN -- neue KeyID = Neue Berechnung der Tage SET @KeyGroupIDSave = @KeyGroupID SET @EndeSave = @Ende INSERT INTO #ZeitRaumBloecke (GroupedID,GroupedVon,GroupedBis, PID, Schl) VALUES (@KeyGroupID,@Beginn,@Ende,@KeyPID,@KeySchl) SET @InsertID = SCOPE_IDENTITY() END ELSE BEGIN IF DATEDIFF(DAY,@EndeSave,@Beginn) = 1 BEGIN -- Anschlusszeit UPDATE #ZeitRaumBloecke SET GroupedBis = @Ende WHERE ID = @InsertID SET @EndeSave = @Ende END ELSE IF @EndeSave >= @Ende -- Enthalten (kleineres Beginndatum wegen Sortierung ausgeschlossen) BEGIN -- Gespeichertes Ende ist bereits größer (nichts machen) SET @EndeSave = @EndeSave ---CONTINUE --(funktioniert warum auch immer nicht?!) END ELSE IF @EndeSave < @Ende AND @Beginn < @EndeSave BEGIN -- Enthalten aber Ende später (kleineres Beginndatum wegen Sortierung ausgeschlossen) UPDATE #ZeitRaumBloecke SET GroupedBis = @Ende WHERE ID = @InsertID SET @EndeSave = @Ende END ELSE BEGIN -- Keine Anschlusszeit daher neue Zeitberechnung (erstmal alten Zeitraum ausgeben) INSERT INTO #ZeitRaumBloecke (GroupedID,GroupedVon,GroupedBis, PID, Schl) VALUES (@KeyGroupID,@Beginn,@Ende, @KeyPID, @KeySchl) SET @InsertID = SCOPE_IDENTITY() SET @EndeSave = @Ende END END FETCH NEXT FROM my_cursor2 INTO @KeyGroupID, @KeyPID, @KeySchl, @Beginn, @Ende END COMMIT TRANSACTION Test CLOSE my_cursor2 DEALLOCATE my_cursor2 SELECT * FROM #t SELECT * FROM #ZeitRaumBloecke DROP TABLE #t DROP TABLE #ZeitRaumBloeckeSo sieht das Ergebnis aus: (oben alle Werte, unten die Zeitblöcke)
Würde mich freuen, wenn es hier ein Lösung gäbe. Wenn nicht, hätte ich wenigstens alles versucht.
Vielen Dank im Voraus
Gruß
Vincent
- Bearbeitet Vincent-Vega Montag, 5. März 2018 10:15
