none
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 #ZeitRaumBloecke

    So 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



    Montag, 5. März 2018 10:09

Antworten

  • Hallo Vincent,

    ich würde dies als "Problem der Dateninseln" sehen und entsprechend lösen! 

    Siehe auch: Dateninseln partitionieren

    Hier der Code dazu, den ich minimal kommentiert habe. Zum Nachvollziehen am besten die große CTE mal ein Einzelteile zerlegen und dann die Zwischenergebnisse anschauen.

    HTH!

    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,'2016-01.01','2016-12-31', 1)
    		,(1,'2016-12-01','2016-12-31', 1)
    		,(1,'2016-01-01','2016-01-31', 1)
    		,(1,'2017-01-01','2017-06-30', 1)
    		,(1,'2018-01-03','2018-12-31', 1)
    		,(1,'2018-01-03','2018-12-31', 2)
    		,(2,'2016-01-01','2016-06-30', 1)
    		,(2,'2016-12-01','2016-12-31', 1)
    		,(3,'2016-01-01','2016-12-31', 1)
    		,(3,'2016-01-01','2016-07-31', 1)
    		,(4,'2016-01-01','2016-01-02', 1);
    
    Select *
    from #t;
    /*
    	Kalender	= Kalender vom 01.01.2016 bis 31.12.2018 (könnte man dynamisch gestalten anhand der Input-Daten)
    	Transponiert	= Cross Join mit Daten um für jeden Tag eine Zeile zu erhalten
    	Aufbereitung	= Berechnung einer Zahl um Inseln zu bilden. Das ist Differenz in Tagen zu einem fiktiven Datum
    	Inseln		= Berechnung der Inseln anhand der Differenz aus der Nummer und dem Dense_Rank. Hierüber wird später gruppiert!
    */
    With Kalender (Tag) as
    (
    	Select CAST('2016-01-01' AS date) AS [Tag]
    	UNION ALL
    	SELECT   DATEADD(dd, 1, [Tag])
        FROM     Kalender
        WHERE    DATEADD(dd, 1, [Tag] ) <= CAST('2018-12-31' as date)
    ),
    Transponiert as
    (
    Select t.PID, t.SchluesselNr, k.Tag
    from	Kalender k ,
    		#t	as t  
    where k.Tag between t.von and t.bis
    ),
    Aufbereitung as
    (
    Select DateDiff(DAY, '2015-01-01', Tag) as Nummer, PID, SchluesselNr, Tag
    from Transponiert t
    ),
    Inseln as
    (
    Select Nummer,
    		Nummer -  DENSE_RANK() OVER(PARTITION BY PID, SchluesselNr ORDER BY Nummer)	as Diff,
    		PID,
    		SchluesselNr, Tag
    from Aufbereitung
    )
    Select PID, SchluesselNr, min(Tag) as GroupedVon, max(Tag) as GroupedBis
    from Inseln
    group by PID, SchluesselNr, Diff
    order by PID, SchluesselNr, GroupedVon
    OPTION  ( MAXRECURSION 0 ) ;
    
    go
    drop table #t


    Einen schönen Tag noch, Christoph -- Data Platform MVP - http://www.insidesql.org/blogs/cmu


    Dienstag, 6. März 2018 07:34

Alle Antworten

  • Hallo Vincent,

    ich bin mir sehr sicher, dass das funktionert - habe so eine Lösung nämlich für einen Kunden realisiert, der eine ähnliche Aufgabenstellung hatte. Das Beispiel (siehe Code) basiert auf einer Aufgabenstellung, bei der über eine Webseite ein Intervall für bestimmte Aggregationen gebildet werden sollte.

    DECLARE	@start_date AS DATETIME = '20180101 14:00',
    		@end_date AS DATETIME = '20180106 12:00',
    		@intervall AS INT = 15;
    
    WITH I
    AS
    (
    	SELECT	@start_date	AS	DateValue,
    			@Intervall	AS	StepValue
    
    	UNION ALL
    
    	SELECT	DATEADD(MINUTE, I.StepValue, @start_date)	AS	DateValue,
    			I.StepValue + @Intervall					AS	StepValue
    	FROM	I
    	WHERE	DATEADD(MINUTE, I.StepValue, @start_date) <	DATEADD(MINUTE, 1, @end_date)
    )
    SELECT * FROM I
    OPTION (MAXRECURSION 0);
    

    Ich kann nicht den ganzen Code posten aber das obige Codefragment wird als CTE in den JOIN mit eingebunden. Die CTE ermittelt von @start_date bis @end_date jedes Datum/Uhrzeit mit dem angegebenen Intervall.

    Der Kunde hat das ebenfalls mit Hilfe einer SP und Cursor gelöst und als Job alle 10 Minuten ausführen lassen. Die Routine hatte ca. 4 - 6 Minuten gedauert. Durch eine Set-basierte Lösung habe ich die Ausführung auf <1 Sekunde reduziert.

    Der obige Code soll Dir als Idee dienen, auf der Du weiter aufbauen kannst.


    Uwe Ricken (Blog | Twitter)
    Microsoft Certiied Master - SQL Server 2008
    Microsoft Certified Solution Master - CHARTER Data Platform
    Microsoft Certified Solution Expert - Data Platform
    db Berater GmbH
    Microsoft SQL Server Blog (german only)

    Dienstag, 6. März 2018 00:31
  • Hallo Uwe,

    danke für die Anregung. Ich schaue mir das mal an und melde mich.

    Dienstag, 6. März 2018 05:48
  • Hallo Vincent,

    ich würde dies als "Problem der Dateninseln" sehen und entsprechend lösen! 

    Siehe auch: Dateninseln partitionieren

    Hier der Code dazu, den ich minimal kommentiert habe. Zum Nachvollziehen am besten die große CTE mal ein Einzelteile zerlegen und dann die Zwischenergebnisse anschauen.

    HTH!

    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,'2016-01.01','2016-12-31', 1)
    		,(1,'2016-12-01','2016-12-31', 1)
    		,(1,'2016-01-01','2016-01-31', 1)
    		,(1,'2017-01-01','2017-06-30', 1)
    		,(1,'2018-01-03','2018-12-31', 1)
    		,(1,'2018-01-03','2018-12-31', 2)
    		,(2,'2016-01-01','2016-06-30', 1)
    		,(2,'2016-12-01','2016-12-31', 1)
    		,(3,'2016-01-01','2016-12-31', 1)
    		,(3,'2016-01-01','2016-07-31', 1)
    		,(4,'2016-01-01','2016-01-02', 1);
    
    Select *
    from #t;
    /*
    	Kalender	= Kalender vom 01.01.2016 bis 31.12.2018 (könnte man dynamisch gestalten anhand der Input-Daten)
    	Transponiert	= Cross Join mit Daten um für jeden Tag eine Zeile zu erhalten
    	Aufbereitung	= Berechnung einer Zahl um Inseln zu bilden. Das ist Differenz in Tagen zu einem fiktiven Datum
    	Inseln		= Berechnung der Inseln anhand der Differenz aus der Nummer und dem Dense_Rank. Hierüber wird später gruppiert!
    */
    With Kalender (Tag) as
    (
    	Select CAST('2016-01-01' AS date) AS [Tag]
    	UNION ALL
    	SELECT   DATEADD(dd, 1, [Tag])
        FROM     Kalender
        WHERE    DATEADD(dd, 1, [Tag] ) <= CAST('2018-12-31' as date)
    ),
    Transponiert as
    (
    Select t.PID, t.SchluesselNr, k.Tag
    from	Kalender k ,
    		#t	as t  
    where k.Tag between t.von and t.bis
    ),
    Aufbereitung as
    (
    Select DateDiff(DAY, '2015-01-01', Tag) as Nummer, PID, SchluesselNr, Tag
    from Transponiert t
    ),
    Inseln as
    (
    Select Nummer,
    		Nummer -  DENSE_RANK() OVER(PARTITION BY PID, SchluesselNr ORDER BY Nummer)	as Diff,
    		PID,
    		SchluesselNr, Tag
    from Aufbereitung
    )
    Select PID, SchluesselNr, min(Tag) as GroupedVon, max(Tag) as GroupedBis
    from Inseln
    group by PID, SchluesselNr, Diff
    order by PID, SchluesselNr, GroupedVon
    OPTION  ( MAXRECURSION 0 ) ;
    
    go
    drop table #t


    Einen schönen Tag noch, Christoph -- Data Platform MVP - http://www.insidesql.org/blogs/cmu


    Dienstag, 6. März 2018 07:34
  • Hallo Christop,

    ich habe Dein Skript heute ausgibigt getestet. Ich gebe schonmal zu, dass ich allein nicht auf diese Lösung gekommen wäre. Ich habe das Thema CTE immer etwas Stiefmütterlich behandelt ;(

    Spätestens nach Uwes Tipp, habe ich mir aber geschworen das zu ändern!

    Die Ausführungszeit ist bis zu 800tsd Datensätzen traumhaft (<3 Sekunden!). Ab 900tsd habe ich die Abfrage bei 11 Minuten abgebrochen. Ob das am Arbeitspeicher liegt, kann ich gerade nicht sagen, aus PerfMon habe ich noch keine verwertbaren Infos ermitteln können.

    Ich werde nun versuchen die gesammte Abfrage auf die PIDs aufzuteilen, oder das Ergebnis in 3-4 Blöcken zu erhalten.

    Ich wollte mich nur schon mal gemeldet haben.

    Danke! Ich melde mich in Kürze.

    Gruß

    Vincent

    Dienstag, 6. März 2018 12:47
  • Hat die Quelltabelle einen Index über PID+SchluesselNr ?

    Einen schönen Tag noch, Christoph -- Data Platform MVP - http://www.insidesql.org/blogs/cmu

    Dienstag, 6. März 2018 14:24
  • Hallo Christoph,

    auf meiner Quelltabelle war tatsächlich nur ein PK auf der PID, Asche auf mein Haupt.
    Die Tests dauern bei der Datenmenge leider immer etwas länger, daher bitte ich um Nachsicht.
    Der von Dir empfohlene Index hat leider nicht den erwünschten Erfolg gebracht, bzw. wird nicht genutzt. Allerdings hat der Ausführungsplan eine Empfehlung gegeben.

    Der Vollständigkeit halber möchte ich noch hinzufügen, dass meine aktuelle Arbeitstabelle eigentlich nur ein "von" Datum hat. Das Ende ist immer das Monatsende des "von" Datums. Um nicht über die errechnete "bis"-Spalte in eine Falle zu tappen, habe ich eine berechnete Spalte (Funktion: "EOMONTH(von)") zur Tabelle hinzugefügt. So sollte der SQL-Server diese optimal nutzen können. Der Auführungsplan empfielt den u.g. Index.

    -- Hier erstmal der von Dir empfohlende Index
    -- 1 Mio DS - habe ich nach 8 Minuten abgebrochen
    CREATE NONCLUSTERED INDEX #t
    ON #t (PID,SchluesselNr)
    INCLUDE (von)


    -- empfohlener Index inklusive berechneter Spalte "bis"
    -- 3 Mio DS in 52Sekunden
    -- 33 Mio DS bei 90% Prozessorlast bisher ca. 9 Minuten !!Bei fehlerhafter gruppierung (siehe unten)!!
    CREATE NONCLUSTERED INDEX #t
    ON #t (von,bis)
    INCLUDE (PID,SchluesselNr)

    Leider bekomme ich beim Select eine OutOfMemoryExeption und musste daher die Ausgabe in eine Temp umleiten (trotz 128Gb RAM).
    Grund: Es werden von 32.619.964 DS aus der Originaldatei 32.088.126 gruppierte Datensätze ausgegeben?!
    Das ist sehr seltsam, weil ich bei den Top 3Mio nur 623.844 gruppierte Ergebnisdatensätze erhalte. Die Top Einschränkung habe ich im CTE "Transponiert" gemacht. Dasselbe Phänoment hat man bei TOP 100 PERCENT.

    Ich teste gerade noch mit einem zusätzlichen ORDER BY nach PID, SchluesselNr und VON. Ich melde mich dann später mit den Ergebnissen.

    Für weitere Ideen bin ich offen.

    Gruß

    Vincent

     
    Mittwoch, 7. März 2018 08:18
  • Bitte ignoriert erstmal meine Meldung bzgl. der falschen Gruppierung. Ich glaube das Problem sitzt gerade vor dem Bildschirm. Ich teste noch etwas und melde mich.
    Mittwoch, 7. März 2018 09:33
  • Hallo zusammen,

    mir ein Fehler bei der PID unterlaufen. Die Gruppierung stimmt jetzt wieder.
    Aktuell nutze ich den Index über VON und BIS von oben.

    Mit der Laufzeit komme ich jetzt klar. Reicht zwar nicht für AdHoc-Abfragen, ist aber in den meisten Fällen auch nicht nötig. Wenn keiner mehr weitere Ideen bzgl. der Performance hat, werde ich das wie in Christophs Lösung umsetzen.

    Hier noch einige Testergebnisse:
    Anzahl Datensätze im Original: 32.619.964
    Dauer (CTE): Prozessorzeit 60-70% > 59 Minuten > Anzahl gruppierte Datensätze: 1.271.608
    Dauer (Schleife): Prozessorzeit 13% > nach 4 Stunden abgebrochen > Anzahl gruppierte Datensätze: ?

    Anzahl Datensätze im Original: 70 Mio
    Dauer (CTE): Prozessorzeit 50-70% > 1:59 Minuten > Anzahl gruppierte Datensätze: 3.655.734


    Vielen Dank an Uwe und Christoph für die Unterstützung.

    Gruß

    Vincent

    Donnerstag, 8. März 2018 12:40