Paging mit SQLGlobalCursor

Problembeschreibung

Sie als Entwickler kennen bestimmt das Problem, große Mengen an Daten in einer Applikation anzeigen zu müssen.
Es gibt viele Controls, die das Paging unterstützen und die Daten selbstständig aufteilen und beim Blättern diese portionsweise anzeigen. Diese benötigen als DataSource aber die gesamte Datenmenge! Dabei ist es sehr oft gar nicht nötig, wirklich alle Daten zu holen, da der Benutzer kein Interesse an allen Daten hat und sich nur die für ihn interessanten Daten oder nur die ersten Seiten anschaut. Wichtig ist aber, die Gesamtanzahl der Daten zu kennen und anzuzeigen.
Sind Sie durch diese Rahmenbedingungen gezwungen, alle Daten zu holen, oder gibt es Alternativen?
Aus unserer Praxis kennen wir eine Lösung für den MS SQL Server, die wir in diesem Artikel vorstellen möchten.

Lösung

Ihre Daten sind in eine MS SQL Server-Datenbank gespeichert. Nehmen wir an, in einer Tabelle „Kunde“ sind 1.200.000 Datensätze gespeichert Die Tabelle hat folgende Struktur:

Möchten Sie zum Beispiel die Kunden anzeigen, die Geburtstag im Herbst haben, so selektieren Sie die Daten mit folgender Abfrage:

  SELECT *
  FROM [dbo].[Kunde]
     WHERE DATEPART( m, Geburtsdatum) BETWEEN 9 AND 11
 ORDER BY Geburtsdatum, Name, Vorname    

Und erhalten als Ergebnis:

(87790 Zeile(n) betroffen)

Jetzt möchten Sie aber nicht alle Daten, sondern nur 50 Stück auf einer Seite anzeigen. Die Anzahl der Seiten lässt sich ausrechnen:

    87790 / 50 = 1755,8 

Also werden 1756 Seiten benötigt, um alle gefundenen Kunden anzuzeigen.

Wenn Sie die Daten für jede Anzeigeseite einzeln holen möchten, benötigen Sie einen Mechanismus, der die Daten portionsweise ab einer bestimmten Position holt. Zu diesem Zweck eignet sich sehr gut ein Globaler SQL-Cursor. Um diesen Cursor flexibel einzusetzen, verpacken wir ihn in eine SQL-gespeicherte Prozedur.

CREATE PROCEDURE [dbo].[usp_GetResultPaging] 	
	(
	@intAbsPos		Int = 1, 		-- ab welcher Position soll gelesen werden
	@chrFromTable		VARCHAR(Max) = '',	-- aus Tabelle/Sicht	
	@chrWHERE		VARCHAR(Max) = '', 	-- Where Bedingung
	@countPerPage		int = 30,		-- Anzahl Daten zum Abrufen
	@chrPKColumnName	VARCHAR(150), 		-- Primary Key in Tabelle/Sicht
	@chrOrderBY		VARCHAR(500), 		-- Sortierbedingung
	@chrSELECT		VARCHAR(Max),		-- Auswahlbedingung
	@chrCursorName		VARCHAR(200),		-- Name des Cursors
	@chrCursorOperation	varChar(10) = 'READ'  -- Operation(OPEN; READ; CLOSE)

)
AS
SET QUOTED_IDENTIFIER OFF
	SET NOCOUNT ON	
	DECLARE @i			Int
	DECLARE @chrAbsPos		VARCHAR(12)
	DECLARE @chrKeys		VARCHAR(8000)
	DECLARE @chrBuffer		VARCHAR(100)

	
	-- Berechnungen (absolute Position)
	SET @chrAbsPos = CONVERT(VARCHAR(10), @intAbsPos) 
	
	SET @chrKeys = ''
	set @chrBuffer = ''

-- Erste Operation muss OPEN sein. Hier wird der Cursor angelegt
IF @chrCursorOperation = 'OPEN' 
BEGIN
	EXECUTE('DECLARE '+ @chrCursorName +' CURSOR GLOBAL SCROLL READ_ONLY KEYSET FOR SELECT ' +  @chrPKColumnName + ' FROM ' + @chrFromTable  + @chrWHERE + @chrOrderBY)				
	EXECUTE('OPEN '+ @chrCursorName +'')
END

-- Danach folgt bei OPEN (Daten sollen ja auch schon beim ersten Aufruf kommen) 
-- und READ die Ausführung des Cursors.
IF @chrCursorOperation = 'OPEN' OR @chrCursorOperation = 'READ'
BEGIN 

DECLARE @sql nvarchar(100)
DECLARE @sqlNext nvarchar(100)
SET @sql = N'FETCH ABSOLUTE @intAbsPos FROM '+ @chrCursorName +' INTO @chrBuffer'
SET @sqlNext = N'FETCH NEXT FROM '+ @chrCursorName +' INTO @chrBuffer'
		
		-- Statement mit Cursor ausführen und @chrBuffer zurückgeben
		EXEC sp_executesql @sql, N'@intAbsPos int, @chrBuffer VARCHAR(100) OUTPUT', @intAbsPos, @chrBuffer OUTPUT

		SET @i = 1
	
		SET @chrKeys = '''' + @chrBuffer + ''','
		WHILE (@@FETCH_STATUS <> -1)  AND (@i < @countPerPage)
		BEGIN
		  -- Statement NEXT mit Cursor ausführen und @chrBuffer zurückgeben
		  EXEC sp_executesql @sqlNext, N'@chrBuffer VARCHAR(100) OUTPUT', @chrBuffer OUTPUT
		  IF @@FETCH_STATUS <> - 1 BEGIN
			SET @chrKeys = @chrKeys + '''' + @chrBuffer + ''','
				SELECT @i = @i + 1
			END
		  END
		SET @chrKeys = '(' + Left(@chrKeys, Len(@chrKeys) - 1) + ')'
	
-- Abfrage ausführen und die gefundenen Datensätze zurückgeben 
EXECUTE(@chrSELECT + ',  CONVERT(VARCHAR(20), ' +  @@CURSOR_ROWS + ')  as Anzahl FROM ' + @chrFromTable +  ' WHERE ' + @chrPKColumnName + ' IN ' + @chrKeys + @chrOrderBY) 

END

IF @chrCursorOperation = 'CLOSE' 
BEGIN
--Cursor schließen und freigeben
	EXECUTE('CLOSE '+ @chrCursorName+'  DEALLOCATE '+ @chrCursorName+'')
END

SET NOCOUNT OFF
RETURN

Diese gespeicherte Prozedur kann aus jeder Datenbank-Tabelle/Sicht Daten portionsweise auslesen, gibt aber zusätzlich die gesamte Anzahl der Treffer zurück.

Beispiel, wie Sie diese gespeicherte Prozedur aufrufen können:

 EXEC	[dbo].[usp_ GetResultPaging]
	@intAbsPos = 51,
	@chrFromTable = N'dbo.[Kunde]',
	@chrWHERE = N' WHERE DATEPART( m, Geburtsdatum) BETWEEN 9 AND 11 ',
	@countPerPage = 50,
	@chrPKColumnName = N'Id',
	@chrOrderBY = N' ORDER BY Name, Vorname ',
	@chrSELECT = N'Select *',
	@chrCursorName = N'PagingCursor',
	@chrCursorOperation = N'OPEN'

Als Ergebnis bekommen Sie 50 Datensätze von Kunden für die zweite Paging-Seite, die im Herbst geboren sind, sortiert nach Geburtsdatum, Name und Vorname. Da der CURSOR in der gespeicherten Prozedur als GLOBAL CURSOR angelegt wurde, bleibt er in einer geöffneten Verbindung zur SQL-Datenbank aufrecht erhalten und kann beliebig oft verwendet werden.
Wichtig: Beim zweiten und jedem weiteren Zugriff muss 'READ' als @chrCursorOperation übergeben werden.
Sobald Sie den CURSOR nicht mehr benötigen, schließen Sie Ihn durch Aufruf der gespeicherten Prozedur mit 'CLOSE' als @chrCursorOperation.

Links und Quellen

https://msdn.microsoft.com/de-de/library/ms180169(v=sql.120).aspx

Kontakt

Wenn Sie Fragen oder Anmerkungen zu diesem Artikel haben, melden Sie sich bitte bei uns: mailto:techcorner@max-it.de.

Über m.a.x. Informationstechnologie AG:
Als etabliertes Münchner Systemhaus zeichnen wir uns seit 1989 als verlässlicher IT-Partner mittelständischer und großer Unternehmen aus. Unser Portfolio reicht von IT- Services über individuelle Softwareentwicklung bis hin zur ERP-Beratung.

Tags

MS SQL Server, T-SQL, Global Cursor, Datenbank, Paging