Freitag, 7. November 2014

Rangfolge von Datentypen – Auswirkung auf Abfragen

In dem zuvor geschriebenen Artikel “Warum korrekte Datentypen für WHERE-Klauseln wichtig sind” wurde beschrieben, wie wichtig es ist, dass in den WHERE-Klauseln immer die richtigen Datentypen verwendet werden. Dieser Artikel zeigt, dass neben der Wahl der korrekten Datentypen bei Abfragen auch die Datentypen selbst eine wichtige Rolle für eine optimale Ausführung spielen.

Wer bei der Abfrage von Daten nicht den richtigen Datentypen beachtet, kann recht schnell in die Verlegenheit kommen, dass ein performanter INDEX SEEK zu einem INDEX SCAN werden kann. Das Beispiel zeigt, wie die Rangfolge der verwendeten Datentypen die Ausführung von Abfragen in Verbindung mit falschen Datentypen für den Abfragewert beeinflusst.

Testumgebung

Für die Demonstration des oben beschriebenen Themas wird eine Tabelle mit 1.000 Datensätzen erstellt. Anschließend wird ein non clustered Index auf dem Attribut [c2] erstellt, um die unterschiedlichen Ausführungsszenarien zu untersuchen.

-- Testtabelle
CREATE TABLE dbo.foo
(
    Id INT          NOT NULL    IDENTITY (1, 1),
    c1 MONEY        NOT NULL    DEFAULT (100),
    c2 VARCHAR(20)  NOT NULL
);
GO
 
-- Eintragen von 1.000 Datensätzen
DECLARE @i INT = 1;
DECLARE @sn INT;
WHILE @i <= 1000
BEGIN
    SET @sn = RAND() * 1000
    INSERT INTO dbo.foo (c2) VALUES (CAST(@sn AS VARCHAR(20)));
    SET @i += 1;
END
GO
 
-- Erstellung von Index für Datenzugriff
CREATE INDEX ix_foo_c2 ON dbo.foo (c2) INCLUDE (c1);
GO

Sobald die Tabelle erstellt und die Daten eingetragen sind, werden die beiden folgenden Abfragen ausgeführt...



SELECT c1, c2 FROM dbo.foo WHERE c2 = 790;
SELECT c1, c2 FROM dbo.foo WHERE c2 = '790';
GO

Es wird der folgende Ausführungsplan für die Abfragen generiert:


EXECUTIONPLAN_01


Scheinbar gleiche Abfragen erzeugen unterschiedliche Ausführungspläne, dessen ursprünglicher Grund bereits in dem zuvor angegebenen Artikel beschrieben wurde. Die erste Abfrage muss einen – kostspieligen – INDEX SCAN durchführen, da jeder Wert im Attribut [c2] explizit in den Datentypen [INT] umgewandelt werden muss; somit ist eine gezielte Suche im Index nicht möglich. In der zweiten Abfrage wurde der korrekte Datentyp verwendet und somit kann ein effizienter INDEX SEEK angewendet werden. Wie schaut es aber aus, wenn der korrekte Datentyp kein String sondern ein numerischer Wert ist?


Um diese Frage zu beantworten, wird der Datentyp von Attribut [c2] in einen “INT” umgewandelt. Damit der Datentyp geändert werden kann, dürfen keine Objekte auf das Attribut verweisen. Aus diesem Grund muss der Index [ix_foo_c2] gelöscht werden. Nach der Änderung des Datentypen kann der Index erneut erstellt werden.



-- Löschen des Index auf Attribut [c2]
DROP INDEX ix_foo_c2 ON dbo.foo;
GO
 
-- Änderung des Datentypen von varchar zu int
ALTER TABLE dbo.foo ALTER COLUMN c2 INT NOT NULL;
 
-- Erstellung des Index ix_foo_c2
CREATE INDEX ix_foo_c2 ON dbo.foo (c2) INCLUDE (c1);
GO

Sobald die Änderungen abgeschlossen sind, können die Abfragen erneut ausgeführt werden. Tatsächlich hat sich auf Grund der Änderung der Datentypen der Ausführungsplan für den “falschen” Datentypen geändert; er führt TROTZ falschen Datentypen einen performanten INDEX SEEK aus.


EXECUTIONPLAN_02


Verrückte Welt, oder? Warum wird (entgegen der Erwartungen) trotz eines falschen Datentypen ein INDEX SEEK ausgeführt. Die Antwort liegt in den Details des Ausführungsplans versteckt.


EXECUTION_PROPERTIES_01


Interessant ist die Analyse des SEEK-Prädikats. Es ist erkennbar, dass im SEEK-Prädikat der Datentyp des zu suchenden Wertes konvertiert wird und nicht die Werte im Attribut [c2].


Dieser Umstand führt dazu, dass Microsoft SQL Server einen performanten INDEX SEEK durchführen kann. Es muss nicht jeder Wert in [c2] konvertiert werden sondern der “Suchwert” wird zum Datentypen konvertiert, der dem Datentypen von [c2] entspricht. Dieses Verhalten ist der Rangfolge der Datentypen geschuldet, die von Microsoft SQL Server unterstützt werden.


Allgemein werden Textdatentypen immer nachrangig zu numerischen Datentypen behandelt; somit müssen sich diese Datentypen einer Konvertierung unterwerfen, wenn sie mit Werten zusammenarbeiten müssen, die in der Rangfolge über dem eigenen Datentypen stehen. Die von Microsoft SQL Server verwendete Rangfolge stellt sich wie folgt dar:


1.  user-defined data types (highest)
...
5.  datetime2
...
10. float
...
15. int
...
19. ntext
...
23. uniqueidentifier
24. nvarchar (including nvarchar(max) )
26. varchar (including varchar(max) )
...
29. binary (lowest)


Die vollständige Liste der Rangfolge für Datentypen findet sich hier “Data Type Precedence (Transact-SQL)”. Für das angeführte Beispiel ist erkennbar, dass der Datentyp [varchar] nachrangig zum Datentypen [int] ist; er muss sich “unterordnen” und wird – sofern möglich – zu einem numerischen Datentypen konvertiert.


Herzlichen Dank fürs Lesen!

Keine Kommentare :

Kommentar veröffentlichen