Tücken bei der Verwendung eines Cursors in einem "clustered index vom Typen (n)varchar"

Obwohl der Autor ein Gegner von Cursor ist, gibt es Situationen, in denen es einfach nicht möglich ist, eine andere Variante der Datenmanipulation zu favorisieren.

Das Cursor nicht gerade performant sind im Verhältnis zur mengenorientierten Datenmanipulation, sollte jedem erfahrenen SQL-Server Entwickler klar sein. Leider haben Cursor noch andere "Schwachstellen" an die der Autor unfreiwillig geraten ist.

Zur Demonstration soll das folgende Szenario dienen:

In eine Tabelle sollen per Batchjob Kundendaten eingetragen werden. Erst nachdem die Kundendaten eingetragen sind, sollen fortlaufenden Kundennummern vergeben werden. Vom Prozess her ist das eine recht triviale Angelegenheit.

  • Daten eintragen
  • Cursor öffnen
  • Neue Kundennummer evaluieren
  • Neue Kundennummer schreiben
  • Zum nächsten Datensatz springen
  • Cursor schließen

Schauen wir uns genau diese Variante mal genauer in der Praxis an. Das DDL für die Relation sieht wie folgt aus:

USE TEMPDB
GO

IF EXISTS (SELECT * FROM dbo.sysObjects WHERE name = 'CustomerData' AND type = 'U')
	DROP TABLE dbo.CustomerData
	GO

CREATE TABLE dbo.CustomerData
(
  Id		int		NOT NULL IDENTITY(1, 1),
  CustomerNumber	varchar(10)	NOT NULL DEFAULT '#####'),
  CustomerName	varchar(50)	NOT NULL,
  ProcessId	uniqueidentifier	NOT NULL
  InsertUser	sysname		NOT NULL DEFAULT suser_sname(),
  InsertDate	datetime		NOT NULL DEFAULT getdate(),

  CONSTRAINT pk_CustomerData PRIMARY KEY NONCLUSTERED (Id)
)
GO

Die Relation besitzt einen Autowert, der auch gleichzeitig der Primärschlüssel ist. Desweiteren wird das Attribut [ProcessId] für eine eindeutige Zuordnung eines Batchlaufs verwendet. Zunächst also nichts Besonderes.

Schauen wir uns also mal den Prozess in Form einer Stored Procedure an. Das DDL-Listing für die Prozedur sieht wie folgt aus:

IF EXISTS (SELECT * FROM dbo.sysObjects WHERE name = 'proc_app_InsertCustomerData' AND type = 'P')
	DROP PROC dbo.proc_app_InsertCustomerData
	GO

CREATE PROC dbo.proc_app_InsertCustomerData
AS
	SET NOCOUNT ON

	DECALRE	@ProcessId	uniqueidentifier
	DECLARE	@CustomerNo	int
	DECLARE	@Fetch_Id	int

	SET		@ProcessId	=	newid()

	-- Eintragen von ein paar Daten für die Tests
	INSERT INTO dbo.CustomerData
	(CustomerName, ProcessId)
	SELECT	'Ich AG', @ProcessId
	UNION
	SELECT	'Du GmbH', @ProcessId
	UNION
	SELECT	'Er KG', @ProcessId
	UNION
	SELECT	'Sie GmbH & Co. KG', @ProcessId
	UNION
	SELECT	'Es OHG', @ProcessId
	UNION
	SELECT	'Wir GbR', @ProcessId
	UNION
	SELECT	'Ihr KG a. A.', @ProcessId
	UNION
	SELECT	'Sie NIX', @ProcessId

	-- Nachdem die Daten eingetragen sind, muss nun mit einem Cursor
	-- die Kundennummer für jeden neu eingetragenen Datensatz ermittelt
	-- und eingetragen werden
	SET	@CustomerNo	=	1

	DECLARE c CURSOR
	FOR
		SELECT	Id
		FROM	dbo.CustomerData
		WHERE	ProcessId = @ProcessId
		FOR UPDATE OF CustomerNumber

	OPEN c

	-- Ersten Datensatz einlesen und dann kann es losgehen
	FETCH NEXT FROM c INTO @Fetch_Id
	WHILE (@@FETCH_STATUS = 0)
	BEGIN
		UPDATE	dbo.foo
		SET	Number = CONVERT(varchar, @CustomerNo)
		WHERE	CURRENT OF c

		-- Und die nächste freie Nummer vergeben
		SET	@CustomerNo = @CustomerNo + 1

		-- Nächsten Datensatz holen
		FETCH NEXT FROM c INTO @Fetch_Id
	END

	CLOSE c
	DEALLOCATE c

	SET NOCOUNT OFF
GO

Mit diesen beiden Objekten (und noch einem kleinen gemeinen Zusatz können wir nun sehr schön das "seltsame" Verhalten nachbilden.

Wenn Sie das obige Script laufen lassen, erhalten Sie als Ergebnis in der Relation dbo.CustomerData folgende Daten:

SELECT * FROM dbo.CustomerData

Das Ergebnis ist wie erwartet und man kann sehr deutlich erkennen, dass die Kundennummern korrekt vergeben wurden. Kommen wir nun zum "Problem"chen in diesem Fall - wir fügen der Tabelle nun einen geclusterten Index auf das Attribut [CustomerNumber] hinzu und lassen das Script erneut laufen.

Fügen Sie dazu im obigen Script einfach die nachfolgenden Zeilen nach der Erstellung der Tabelle ein:

IF EXISTS (SELECT * FROM dbo.sysObjects WHERE name = 'idx_cls_CustomerData' AND type = '?')
	DROP INDEX dbo.idx_cls_CustomerData
	GO

CREATE CLUSTERED INDEX dbo.idx_cls_CustomerData ON dbo.CustomerData (CustomerNumber)

Lassen Sie das Script erneut laufen und …

staunen Sie.

Plötzlich beginnen alle Kundennummern nicht mehr bei 1 sondern erst bei 10. Was ist hier passiert? Man kann eindeutig sagen, dass dieses Verhalten durch den geclusterten Index hervorgerufen wird. Aber was passiert da genau?

Der Aufruf im Cursor ist "unsortiert", das bedeutet, dass die Daten sortiert nach CustomerNumber (Cluster) und Id (Primärschlüssel) sortiert im Cursor vorliegen, da alle Kundennummern einheitlich sind, liegen die Daten wie folgt vor:

Sobald nun die erste Kundennummer mit dem Wert "1" gefüllt wird, greift der "Clustered Index" und sortiert diesen Wert zunächst einmal ganz nach unten (das '#' liegt in der Sortierung vor numerischen Werten!). Das ganze passiert so lange wie der @@FETCH_STATUS = 0 ist (es wurde erfolgreich eine Zeile eingelesen!) Was passiert aber nun, wenn ein Datensatz geändert wurde?

Nun, bei jeder Neusortierung werden die Daten neu vom Cursor abgerufen und somit findet immer wieder ein erfolgreiches Abrufen von Daten statt.

Das bedeutet, dass nach der Vergabe der letzten Kundennummer ("8") die Daten erneut sortiert abgerufen werden und für den Datensatz mit der Kundennummer "1" eine neue Nummer "9" vergeben wird. Tja, "9" ist größer als "8" und somit wird der Datensatz an das Ende "sortiert" und…

der Cursor ruft die Daten erneut ab und der @@FETCH_STATUS ist wieder 0 (erfolgreiches Abrufen eines Datensatzes). Nun steht im Cursor der Kunde mit der Nummer "2" und der erhält die nächste Kundennummer "10".

Diese unglückliche Verkettung wird erst durchbrochen, wenn nach Änderung eines Wertes die neu abgerufenen Daten nicht mehr neu sortiert vorliegen - der Cursor also die Daten in der gleichen Sortierung vorfindet wie sie vor dem Abrufen waren.

Die "10" ist aber kleiner als "2" und somit verbleibt diese Kundennummer immer oben!

Und nun kann die Kette durchbrochen werden, wenn nicht mehr als 10 Datensätze geändert werden. Nach der "10" folgt die "11" die wiederum größer ist als die 10. Dieser Wert wird als zweiter Wert eingetragen.

Sie glauben mir nicht ;-)

Nun, machen Sie doch mal Folgendes:

Fügen Sie in der Stored Procedure nach der Aktualisierung der Kundennummer folgendes Statement ein:

SELECT * FROM dbo.CustomerData

Führen Sie das Script erneut aus und untersuchen Sie mal die Ergebnismenge. Sie können sehr deutlich erkennen, dass nach jeder Aktualisierung der geänderte Datensatz ans Ende verschoben/sortiert wird.

Erst, wenn die Kundennummern den Wert "10" erreicht haben, wird die Sortierung gestoppt und die Daten verbleiben an ihrer Position (selbstverständlich werden die Daten auch weiterhin korrekt sortiert; nur an ihrer Position ändert sich nichts mehr!)

Dieses obige Phänomen ist eigentlich kein Phänomen sondern folgt logischen Gesetzen.

Aufgrund des Datentyps für den geclusterten Index waren diese Phänomene bereits vorbestimmt.

Sie können auch sehr schön beobachten, welche die kleinste Kundennummer ist, wenn Sie mehr als 10 Kunden gleichzeitig eintragen - der Wert ist auf jeden Fall dreistellig.

Um nicht in dieses seltsame Verhalten zu steuern, sollten Sie die abzurufenden Daten in einem Cursor immer mit einer Sortierung abrufen.

Ein

	DECLARE c CURSOR
	FOR
		SELECT	Id
		FROM	dbo.CustomerData
		WHERE	ProcessId = @ProcessId
		ORDER BY Id
		FOR UPDATE OF CustomerNumber

hätte niemals zu dem von mir beobachteten Verhalten geführt und ich hätte sicherlich 2 - 4 Stunden entspannter arbeiten können ;-)