Paging mit SQLGlobalCursor - Versionsgeschichte

Deko am 14. Mai 2020 um 13:38 Uhr

2020-05-14T13:38:02Z

Deko am 22. Januar 2020 um 11:32 Uhr

2020-01-22T11:32:24Z

Ansp am 3. November 2015 um 13:12 Uhr

2015-11-03T13:12:32Z

Ansp: Die Seite wurde neu angelegt: „==='''Problembeschreibung'''=== Sie als Entwickler kennen bestimmt das Problem, große Mengen an Daten in einer Applikation anzeigen zu müssen.
Es gibt…“

2015-10-01T08:06:54Z

Die Seite wurde neu angelegt: „==='''Problembeschreibung'''=== Sie als Entwickler kennen bestimmt das Problem, große Mengen an Daten in einer Applikation anzeigen zu müssen. Es gibt…“

Neue Seite

==='''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:
[[Datei: Paging_mit_SQLGlobalCursor.jpg|left|thumb|500px]]
 
Möchten Sie zum Beispiel die Kunden anzeigen, die Geburtstag im Herbst haben, so selektieren Sie die Daten mit folgender Abfrage:
<pre> SELECT *
FROM [dbo].[Kunde]
WHERE DATEPART( m, Geburtsdatum) BETWEEN 9 AND 11
ORDER BY Geburtsdatum, Name, Vorname
</pre>
Und erhalten als Ergebnis:
<pre>(87790 Zeile(n) betroffen)
</pre>
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:
<pre> 87790 / 50 = 1755,8 </pre>
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.
<pre>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
</pre>
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:
<pre> 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'
</pre>
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

[[Kategorie: Softwareentwicklung]]

@@ Zeile 1: / Zeile 1: @@
 ==='''Problembeschreibung'''===
 Sie als Entwickler kennen bestimmt das Problem, große Mengen an Daten in einer Applikation anzeigen zu müssen.<br />
-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.<br />
+Es gibt viele Controls, die das Paging unterstützen und die Daten selbstständig aufteilen und beim Blättern diese portionsweise anzeigen.<br />
 Sind Sie durch diese Rahmenbedingungen gezwungen, alle Daten zu holen, oder gibt es Alternativen?<br />
 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:
+Ihre Daten sind in eine MS SQL Server-Datenbank gespeichert. Nehmen wir an, in einer Tabelle „Kunde“<br />
 [[Datei: Paging_mit_SQLGlobalCursor.png|left|thumb|500px]]
 <br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br />
@@ Zeile 21: / Zeile 26: @@
 Also werden 1756 Seiten benötigt, um alle gefundenen Kunden anzuzeigen.<br />
 <br />
-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.
+Wenn Sie die Daten für jede Anzeigeseite einzeln holen möchten, benötigen Sie einen Mechanismus,<br />
-Zu diesem Zweck eignet sich sehr gut ein Globaler SQL-Cursor. Um diesen Cursor flexibel einzusetzen, verpacken wir ihn in eine SQL-gespeicherte Prozedur.
+der die Daten portionsweise ab einer bestimmten Position holt.
 <pre>CREATE PROCEDURE [dbo].[usp_GetResultPaging]
+	(
@@ Zeile 113: / Zeile 120: @@
 	@chrCursorOperation = N'OPEN'
 </pre>
-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.
+Als Ergebnis bekommen Sie 50 Datensätze von Kunden für die zweite Paging-Seite, die im Herbst geboren sind,<br />
-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.<br />
+sortiert nach Geburtsdatum, Name und Vorname.<br />
 Wichtig: Beim zweiten und jedem weiteren Zugriff muss 'READ' als @chrCursorOperation übergeben werden.<br />
 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
+[https://msdn.microsoft.com/de-de/library/ms180169(v=sql.120).aspx www.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.<br />
+Wenn Sie Fragen oder Anmerkungen zu diesem Artikel haben, melden Sie sich bitte bei uns:<br />
 Über m.a.x. Informationstechnologie AG: <br />
-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.<br />
+Als etabliertes Münchner Systemhaus zeichnen wir uns seit 1989 als verlässlicher IT-Partner mittelständischer und<br />
 === '''Tags''' ===
 MS SQL Server, T-SQL, Global Cursor, Datenbank, Paging
 [[Kategorie: Softwareentwicklung]]

@@ Zeile 6: / Zeile 6: @@
 ==='''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:
-[[Datei: Paging_mit_SQLGlobalCursor.jpg|left|thumb|500px]]
+[[Datei: Paging_mit_SQLGlobalCursor.png|left|thumb|500px]]
-<br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br />
+<br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br />
 Möchten Sie zum Beispiel die Kunden anzeigen, die Geburtstag im Herbst haben, so selektieren Sie die Daten mit folgender Abfrage:
 <pre>  SELECT *

Paging mit SQLGlobalCursor - Versionsgeschichte

Deko am 14. Mai 2020 um 13:38 Uhr

Deko am 22. Januar 2020 um 11:32 Uhr

Ansp am 3. November 2015 um 13:12 Uhr

Ansp: Die Seite wurde neu angelegt: „==='''Problembeschreibung'''=== Sie als Entwickler kennen bestimmt das Problem, große Mengen an Daten in einer Applikation anzeigen zu müssen. Es gibt…“

Ansp: Die Seite wurde neu angelegt: „==='''Problembeschreibung'''=== Sie als Entwickler kennen bestimmt das Problem, große Mengen an Daten in einer Applikation anzeigen zu müssen.
Es gibt…“