Schlagwort-Archive: GUID

Sichere Logging-Tabellen trotz @@identity

In meinem letzten Blog-Eintrag hatte ich geschrieben, man solle möglichst scope_identity() statt @@identity verwenden.

Ich hatte bei einem Kunden genau den Fall, dass wir nachträglich in einer bestehenden Datenbank-Applikation über Trigger Protokollierungen erstellen wollten, um genauere Informationen über ein Fehlverhalten zu bekommen.

Deshalb legten wir eine Logging-Tabelle an, die die entsprechenden Informationen aufnehmen sollte und einen Trigger auf bestimmte Tabellen, um diese Informationen zu sammeln und wegzuschreiben.

Leider hatte der damalige Entwickler @@identity und nicht scope_identity() verwendet, so dass eine Identity-Column in unserer Logging-Tabelle zu Fehlern führte: Beim INSERT erhält das aufrufende Programm eine falsche ID. Hoffentlich ist das Programm sonst sauber programmiert, dann wird man dies wahrscheinlich über krachende Foreign Key-Beziehungen erfahren.

Wir kann man so ein Problem aber lösen, wenn man nichts von dem zugrunde liegenden Programm weiß?

Man  könnte die Identity-Column in der Logging-Tabelle entfernen und stattdessen Unique Identifiers verwenden. Ich hatte ja schon einiges über die Nachteile von Unique Identifiers geschrieben.

In diesem Fall scheint es mir aber gerechtfertigt. Natürlich muss man die Spalte mit newSequentialId() initialisieren, also etwa so:

CREATE TABLE [dbo].[Logging](
    [LogID] [uniqueidentifier]  NOT NULL default(newSequentialId()),
    [Timestamp] [datetime] NOT NULL default(getdate()),
    [Logtext] [nvarchar](max) NULL,
CONSTRAINT [PK_Logging] PRIMARY KEY CLUSTERED
(
    [LogID] ASC
)
)

Dann ist die Änderung unschädlich für den uns unbekannten Code, der möglicherweise @@identity verwendet.

Performance und GUIDs

In einem Kunden-Projekt sollte ich die Performance der SQL-Zugriffe einer .NET-Applikation verbessern.

Hier beschreibe ich die Ergebnisse, da sie sich auch auf andere Szenarien verallgemeinern lassen.

Die erste Veränderung war, keine GUIDs als clustered primary keys zu verwenden. Stattdessen setzen wir nun ints (mit Identity) ein. Dies führt zu einer immensen Beschleunigung bei den INSERTs in die Datenbank. Dies ist sehr gut nachvollziehbar, da nun die INSERTs immer am Ende der Tabelle statttfinden, so dass kein zeitaufwändiges Umorganisieren der Seiten innerhalb der Tabelle notwendig wird.

Leider ging es aber in meiner Aufgabe um die SELECT-Performance und nicht um die INSERT-Performance. Aber auch diese verbesserte sich durch die Verwendung der integer-Werte deutlich (Abfragezeit ungefähr halbiert). Als Test verwendete ich die Abfrage von 500 Datensätzen, die ich zu Beginn zufällig ausgewählt hatte. Vor jeder Abfrage wurde natürlich der Cache geleert 🙂 Die Steigerung lässt sich dadurch erklären, dass alle Indizes nun vom Platzbedarf viel kleiner wurden (1 int = 4 byte, 1 guid = 16 byte –> ca. 4x so viele Daten gehen auf eine Index-Page [natürlich abhängig von den weiteren Feldern des Index]). Außerdem ist ein Zähler besser verteilt als eine Guid.

Als nächste Verbesserung verwendete ich in m:n-Tabellen als clustered primary key nicht einen Zähler, sondern einen zusammengesetzten Schlüssel aus den beiden referenzierten Tabellen (+ ein weiteres Feld, um die Eindeutigkeit sicherzustellen). Dabei verwendete ich als erstes Feld das Feld der beiden, das in den meisten Abfragen bekannt ist. (Auf der umgekehrten Reihenfolge lag natürlich auch ein Index). Dadurch muss beim Standard-Zugriff nicht mehr über einen non-clustered Index zugegriffen werden, wodurch ein Zugriff eingespart wird. Dies brachte eine weitere Halbierung der Zugriffszeit.

ALs letztes gab es spezielle Szenarien, in denen nach Texten gesucht werden musste – ein Beispiel: Man möchte alle Aufträge ermitteln, die in einer Position einen bestimmten Positionsfreitext enthalten. Dann wird folgendes SQL-Statement abgesetzt:

SELECT * FROM Auftrag a INNER JOIN Auftragsposition pos on pos.AuftragID = a.AuftragID WHERE pos.Positionsfreitext like ‚Test%‘

Natürlich war auf der Auftragspositions-Tabelle ein Index auf Positionsfreitext. Ich erweiterte diesen Index um die AuftragID. Dadurch kann der Join direkt über den Index abgewickelt werden und ein Zugriff auf die Tabelle wird eingespart, was für diesen Spezialfall ebenfalls eine deutliche Performance-Steigerung einbrachte.

SQL Server: zufällige Auswahl von n Zeilen einer Tabelle

Bei einer Aufgabe zur Performance-Steigerung einer SQL Server 2008-Applikation (über die ich später berichten werde), war die erste Aufgabe, ein Test-Szenario aufzubauen, anhand dessen die Abfragezeiten verglichen werden konnten.

Der erste Schritt war dazu die Auswahl 100 beliebiger Sätze aus einer Tabelle. Dabei zeigte mir ein Kollege folgende super einfache Möglichkeit:

select top 100 * from sysobjects order by newid()

[Natürlich muss sysobjects durch die entsprechende Applikations-Tabelle ersetzt werden 🙂 ]