Mit dem nachfolgenden Artikel möchte ich aus dem „Daily Business“ berichten, in dem ich gebeten wurde, die Performance von Abfragen zu optimieren. Als Beispiel soll die folgende Datenstruktur (inklusive Indexes) verwendet werden. Zur Optimierung habe ich die Struktur einer Relation aus einer Kundenanwendung etwas vereinfacht – jedoch sind die Datentypen 1 zu 1 aus dem Datenmodell des Herstellers übernommen!

IF EXISTS (SELECT * FROM sys.tables WHERE object_id = OBJECT_ID('dbo.tbl_Companies', 'U')) 

    DROP TABLE dbo.tbl_Companies 

    GO

 

CREATE TABLE dbo.tbl_Companies 

( 

    Id          uniqueidentifier NOT NULL, 

    Name        nvarchar(max)    NOT NULL, 

    TaxNo       nvarchar(max)    NOT NULL, 

    CostCenter  nvarchar(max)    NOT NULL, 

    UpdateBy    nvarchar(max)    NOT NULL,

 

    CONSTRAINT pk_Customer PRIMARY KEY CLUSTERED (Id) 

);

Auf die obige Relation werden Abfragen der folgenden Art ausgeführt:

SELECT * FROM dbo.tbl_Companies WHERE CostCenter = 'Kostenstelle';

SELECT Name, TaxNo, CostCenter FROM dbo.tbl_Companies WHERE Name = 'Kundenname';

Wahl der optimalen Datentypen

Der Clustered Index wird durch das Attribut [Id] definiert. Da aktuell nur der Clustered Index festgelegt ist, mag man meinen, dass man jederzeit weitere Indexes erzeugen kann; das ist aber – bei obiger Struktur – ein böser Trugschluss. Microsoft SQL Server erlaubt es nicht, einen „MAX-Datentypen“ zu indizieren. Dies bedeutet in der Konsequenz, dass Abfragen auf [Name], [TaxNo], [CostCenter] und [UpdateBy] nicht in der Lage sind, als Schlüsselattribut in einem Index verwendet zu können. (siehe dazu: http://msdn.microsoft.com/de-de/library/ms188783.aspx).

Aus diesen Gründen sind die ausgewählten Datentypen unbrauchbar für einen indexierten Zugriff. Die Aus der obigen Datenstruktur erschließt sich mir nicht der Grund für die Verwendung des gewählten Datentypen. Wir haben also die maximale Datenlänge ermittelt und dabei folgende Erkenntnisse gewonnen:

  • Das Attribut [Name] beinhaltete Einträge, deren Länge zwischen 10 und 80 Zeichen variierte
  • Das Attribut [CostCenter] hatte eine feste Länge von 7 Zeichen
  • Das Attribut [TaxNo] variierte in der Länge zwischen 10 und 24 Zeichen

Auf Basis dieser Ergebnisse haben wir im ersten Schritt die Attribute der Relation [dbo].[tbl_Company] wie folgt angepasst:

CREATE TABLE dbo.tbl_Companies 

( 

    Id            uniqueidentifier   NOT NULL, 

    Name          nvarchar(128)      NOT NULL, 

    TaxNo         varchar(24)        NOT NULL, 

    CostCenter    char(7)            NOT NULL, 

    UpdateBy      varchar(20)        NOT NULL,

 

    CONSTRAINT pk_Customer PRIMARY KEY CLUSTERED (Id) 

);

Für das Attribut [Name] haben wir eine Begrenzung auf 128 Zeichen festgelegt um Spielraum für längere Unternehmensnamen zu gewährleisten. Den Datentypen nvarchar haben wir für die Attribute weitestgehend belassen, da auch Sonderzeichen berücksichtigt werden müssen. Die Steuernummern [TaxNo] kann nur Buchstaben und numerische Werte beinhalten. Aus diesem Grund wurde der Datentypen nvarchar durch varchar ersetzt und die Zeichenlänge auf eine feste Größe von 7 Zeichen begrenzt. Das Attribut [Kostenstellen] verwendet keine Sonderzeichen. Somit konnte der Datentyp von nvarchar zu char geändert werden. Die Entscheidungsgrundlage für [UpdateBy] orientierte sich eng an den Überlegungen für [Name]. Durch diese – zunächst marginalen – Änderungen wurde die Länge eines Datensatzes limitiert und die Attribute können später für Indexe verwendet werden!

Datentyp des Clustered Index

Ein weiterer Optimierungsbedarf könnte in der Wahl des Datentypen für den Clustered Index bestehen. Da der Clustered Index als uniqueidentifier definiert wurde, konsumiert der Clustered Key eine Länge von 16 Bytes. Ein weiteres Problem mag sein, dass der Wert zufällig ermittelt wird und somit der Index schnell fragmentiert wird.

Hier muss eine Abwägung durch die Entwickler gemacht werden. Wird das System primär zum Speichern neuer Daten benötigt durch mehrere Clients benötigt werden, kann der Clustered Key sogar positiv wirken, da keine “Hotspots” am Ende des Index gebildet werden. Wird jedoch ein weniger konkurrierendes System programmiert, sollte man den Datentypen zu Gunsten von Platz und Wartbarkeit ändern.

Hinweis: Man hat die Möglichkeit, in SQL-Server für den DEFAULT-Value neben NEWID() (generiert eine zufällige GUID auch die Funktion NEWSEQUENTIALID() zu verwenden; nur sollte man nicht unbedingt darauf bauen, dass auch wirklich eine neue „höhere“ GUID generiert wird.

Nachdem wir mit dem Kunden und dem Hersteller gemeinsam das maximale Datenvolumen besprochen haben (>= 3.000.000), haben wir gemeinsam beschlossen, den Datentypen GUID in den Datentypen INT zu wechseln. Die Definition der Relation wurde wie folgt angepasst:

CREATE TABLE dbo.tbl_Companies 

( 

    Id            int             NOT NULL    IDENTITY, 

    Name          nvarchar(128)   NOT NULL, 

    TaxNo         varchar(24)     NOT NULL, 

    CostCenter    char(7)         NOT NULL, 

    UpdateBy      varchar(20)     NOT NULL,

 

    CONSTRAINT pk_Customer PRIMARY KEY CLUSTERED (Id) 

);

 

Da das Attribut [Id] keine beschreibende Funktion hat (Surrogatschlüssel), wurde neben einem neuen Datentypen auch die Einschränkung IDENTITY hinzugefügt. Dadurch werden neue Datensätze automatisch am Ende der Relation eingefügt. Der Aufwand für den Clustered Index ist minimal, da neue Datensätze IMMER am Ende eingefügt werden, da ja die nächsthöhere Nummer verwendet wird.

Durch die obige Anpassung (und dabei wurden noch keine Index optimiert!) wurde der I/O signifikant reduziert. Die Messwerte vorher / nachher zeigen die Unterschiede auf (450.000 Datensätze!)

Ursprüngliche Datenstruktur

tbl_Companies-Tabelle.

    Scananzahl 1,

    logische Lesevorgänge 11913,

    physische Lesevorgänge 0,

    Read-Ahead-Lesevorgänge 0,

    logische LOB-Lesevorgänge 0,

    physische LOB-Lesevorgänge 0,

    Read-Ahead-LOB-Lesevorgänge 0. 

Neue Datenstruktur

tbl_Companies-Tabelle.

    Scananzahl 1,

    logische Lesevorgänge 7529,

    physische Lesevorgänge 0,

    Read-Ahead-Lesevorgänge 0,

    logische LOB-Lesevorgänge 0,

    physische LOB-Lesevorgänge 0,

    Read-Ahead-LOB-Lesevorgänge 0

Zusammenfassung

Die Auswahl geeigneter Datentypen ist ein erster – wichtiger – Schritt zu einer optimierten Anwendung. Durch die richtige Auswahl von Datentyp und Datengröße kann ein erster Erfolg erzielt werden, ohne dabei auch nur einen Index zu berücksichtigen. Zu den behandelten Schwerpunkten finden Sie nachfolgend weiterführende Links zu Webseiten, um das Thema zu vertiefen.

Herzlichen Dank fürs Lesen!

Thema Link
NEWID() http://msdn.microsoft.com/de-de/library/ms190348.aspx
NEWSEQUENTIALID() http://msdn.microsoft.com/de-de/library/ms189786.aspx
SQL Server Datentypen http://msdn.microsoft.com/de-de/library/ms187752.aspx
SQL Server Storage Engine SQL Server Storage Engine
Shares
Share This