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 ;-)
Print article | This entry was posted by Uwe Ricken on 08.01.08 at 21:33:26 . Follow any responses to this post through RSS 2.0. |