Donnerstag, 14. Februar 2013

SELECT [*] – sinnvoll oder sinnlos

Die obige Fragestellung mag provokativ sein, denoch sehe ich immer wieder in Abfragen, Views und Stored Procedures diese Konstruktion. Im deutschsprachigen Raum wurden jedoch die Vor- und/oder Nachteile noch nie wirklich von allen Facetten beleuchtet. Der nachfolgende Artikel beschäftigt sich mit diesem Thema in Bezug auf verschiedene Merkmale.

Ausgangsobjekte für die Untersuchung

Für die nachfolgende Untersuchung werden drei Objekte benötigt, die mit dem Script [Create database and structures.sql] aus der Beispielsolution erstellt werden.

CREATE TABLE dbo.tbl_DataStructure
(
    Id
      int         NOT NULL  IDENTITY (1, 1),
    col1   
char(64)    NOT NULL  DEFAULT ('Value in col1'),
    col2
    char(64)    NOT NULL  DEFAULT ('Value in col2'),
    col3
    char(64)    NOT NULL  DEFAULT ('Value in col3'),
    col4   
char(64)    NOT NULL  DEFAULT ('Value in col4'),
    col5
    char(64)    NOT NULL  DEFAULT ('Value in col5'),
    col6
    char(64)    NOT NULL  DEFAULT ('Value in col6'),
    col7
    char(64)    NOT NULL  DEFAULT ('Value in col7'),
    col8
    char(64)    NOT NULL  DEFAULT ('Value in col8'),
    col9
    char(64)    NOT NULL  DEFAULT ('Value in col9'),

    CONSTRAINT pk_tbl_DataStructure PRIMARY KEY CLUSTERED (Id)
);

-- Ein einfacher Index wird zu Demonstrationszwecken auf col1 angelegt
CREATE INDEX ix_tbl_DataStructure_col1 ON dbo.tbl_DataStructure (col1);
GO

-- View
CREATE VIEW dbo.view_DataStructure
AS
    SELECT * FROM dbo.tbl_DataStructure;
GO

-- Prozedur
CREATE PROC dbo.proc_DataStructure
AS
    SET NOCOUNT ON;

    SELECT * FROM dbo.tbl_DataStructure;
    SELECT * FROM dbo.view_DataStrucuture;

    SET NOCOUNT OFF;
GO

Was bedeutet SELECT [*] FROM…?

Das Synonym SELECT [*] definiert alle Attribute aus allen Relationen und Views, die in der FROM-Klausel angegeben werden. Die Attribute werden wie in der FROM-Klausel angegeben nach Relationen oder Views sortiert sowie in der Reihenfolge zurückgegeben, in der sie in der Relation vorhanden sind.

Ein SELECT [*] impliziert deutlich mehr Netzwerkverkehr als ein SELECT mit dedizierter Angabe von auszugebenen Attributen. Dieses Thema soll jedoch hier nicht vertieft werden!

Wie ermittelt SQL Server bei SELECT [*] die auszugebenden Attribute?

Wird ein SELECT [*] an den Server geschickt, muss SQL Server zunächst alle Metadaten der Objekte in der FROM-Klausel ermitteln. Als Metadaten einer Relation beschreibt man die Attribute und deren Datentypen. Um die Metadaten eines Objekts abzurufen, wird für Relationen / Views die Systemrelation sys.all_columns verwendet.

-- Metadaten der Relation dbo.tbl_DataStructure
SELECT * FROM sys.all_columns WHERE object_id = OBJECT_ID('dbo.tbl_DataStructure');

-- Metadaten der View dbo.view_DataStructure
SELECT * FROM sys.all_columns WHERE object_id = OBJECT_ID('dbo.view_DataStructure');

-- Metadaten der Prozedur dbo.proc_DataStructure
SELECT * FROM sys.all_columns WHERE object_id = OBJECT_ID('dbo.proc_DataStructure');

Während für Relationen und Views die Ausgabeattribute (Metadaten) über die Systemrelation ausgegeben werden kann, ist dies für Prozeduren nicht möglich.

Um zu verstehen, wie SQL Server bei der Evaluierung der auszugebenen Daten vorgeht, muss die Reihenfolge bekannt sein, in der SQL Server die Daten bei Ausführung einer SELECT-Anweisung vorgeht. Hier gilt die folgende Reihenfolge verbindlich:

FROM Zunächst wird ermittelt, von welchen Relationen Informationen abgerufen werden
ON Ermittlung weiterer Relationen für JOIN-Verbindungen
JOIN JOIN-Klausel für die Verbindung von Relationen untereinander
WHERE Einschränkung der Daten
 
SELECT Auswahl der Informationen (Abrufen der Metadaten der FROM / ON – Objekte

Auf Basis dieser Informationen kann das Ergebnis der Ausführung der zuvor erstellten Objekte eigentlich nicht überraschen. Führt man das nachfolgende Script unter Berücksichtigung des Ausführungsplans aus, ist erkennbar, dass alle Varianten identisch sind.

SET NOCOUNT ON
SET STATISTICS IO ON;
SET STATISTICS PROFILE ON;

SELECT * FROM dbo.tbl_DataStructure;
SELECT * FROM dbo.view_DataStructure;
EXEC dbo.proc_DataStructure;

SET STATISTICS PROFILE OFF;
SET STATISTICS IO OFF;
SET NOCOUNT OFF;

Basierend auf der obigen Ausführung (ist unter dem Scriptnamen [Retrieve data.sql] im Download vorhanden) wird der nachfolgende Ausführungsplan ausgegeben:

ExecutionPlan 01

Auch der gemessene IO ist für alle Abfragevarianten identisch:

tbl_DataStructure-Tabelle. Scananzahl 1, logische Lesevorgänge 7723, ...
tbl_DataStructure-Tabelle. Scananzahl 1, logische Lesevorgänge 7723, ...
tbl_DataStructure-Tabelle. Scananzahl 1, logische Lesevorgänge 7723, ...
tbl_DataStructure-Tabelle. Scananzahl 1, logische Lesevorgänge 7723, ...

Alle Varianten (Abfrage der Relation, Abfrage der View und Ausführung in Prozedur) führen identische Ausführungsplane aus. Es ergibt sich also zunächst kein messbarer Unterschied zwischen den einzelnen Verfahren bei reiner Betrachtung der Ausführung.

Änderung der Datenstruktur

Datenmodelle unterliegen der permanenten Veränderung. Sei es aus Gründen der gestiegenen Anforderungen durch den Kunden oder aber auf Grund von Optimierungsbedarf. Dieser Punkt der Betrachtung kann zu erheblichen Problemen bei der Verwendung von SELECT * führen, wie das nachfolgende Beispiel (im Download als [modify table.sql] enthalten) zeigt.

Die Relation dbo.tbl_DataStructure wird um ein weiteres Attribut erweitert und anschließend werden die drei Objekte erneut abgefragt / ausgeführt.

ALTER TABLE dbo.tbl_DataStructure ADD col10 char(64) NULL;

Nun wird erneut sowohl die Relation und View abgefragt und das Ergebnis verwundert ein wenig:

SELECT * FROM dbo.tbl_DataStructure;
SELECT * FROM dbo.view_DataStructure;

Resulttable 01

Während für die Abfrage auf die Relation dbo.tbl_DataStructure das neu hinzugefügte Attribut [col10] ausgegeben wird, scheint für die View dieses neue Attribut nicht bekannt zu sein. Die Ursache für dieses Phänomen liegt in der Vorgehensweise von SQL Server begründet, wie Meatdaten ermittelt werden (siehe Reihenfolge oben).

Wird eine View erstellt, werden vor der Speicherung alle auszugebenen Attribute mit Namen, Datentyp und Datengröße ermittelt. Diese Informationen werden dann für das zu speichernde Objekt in den Metadaten hinterlegt. Für das Objekt dbo.view_DataStructure sind keine Änderungen durchgeführt worden; demzufolgen ist in sys.all_columns für dieses Objekt kein Update ausgeführt worden.

view old structure

Die obige Abbildung zeigt die Metadaten für die View und man kann deutlich erkennen, dass das neu hinzugefügte Attribut [col10] nicht in den Metadaten gespeichert wurde. Um die View “auf den aktuellen Stand” zu bringen, gibt es zwei Möglichkeiten:

  • Neuerstellung der View
  • ALTER VIEW
  • sp_refreshview

Von einer Neuerstellung der View ist dringend abzuraten, da durch eine Neuerstellung alle Berechtigungen verlorgen gehen und unter Umständen erst wieder mühsam ermittelt und angepaßt werden müssen.ALTER VIEW ist vorzuziehen, da in diesem Fall die Berechtigungen nicht verloren gehen. Alternativ kann die Prozedur sp_refreshview ausgeführt werden, um die Metadaten zu aktualisieren.

EXEC sp_refreshview @viewname = 'dbo.view_DataStructure';

Sobald die View aktualisiert wurde, sind die Ausgabeattribute wieder “synchron”

Wie sieht es jedoch aus, wenn ein Attribut nicht hinzugefügt sondern entfernt wird. In diesem Fall wird die View nicht mehr arbeiten können, da dann beim Abrufen der Metadaten überprüft wird, ob die angeforderten Attribute in der Relation noch vorhanden sind. Das folgende Beispiel verdeutlicht diesen Umstand:

ALTER TABLE dbo.tbl_DataStructure DROP COLUMN col10;
SELECT * FROM dbo.view_DataStructure;

Nachdem das Attribut col10 von der Relation dbo.tbl_DataStructure entfernt wurde, wird bei Ausführung der View die folgende – selbsterklärende – Fehlermeldung ausgegeben:

Meldung 4502, Ebene 16, Status 1, Zeile 2
Für die Sicht oder Funktion 'dbo.view_DataStructure' sind mehr Spaltennamen angegeben, als Spalten definiert sind.

Da die View noch nicht aktualisiert ist, ist in der Liste der verfügbaren Attribute noch das Attribut col10 in den Metadaten vorhanden. Diese Information wird bei Ausführung versucht, abzurufen. Da dieses Attribut in der Relation selbst nicht mehr vorhanden ist, wird dier obige Fehler ausgegeben. Eine erneute Ausführung von

EXEC sp_refreshview @viewname = 'dbo.view_DataStructure';

behebt diesen Fehler!

SELECT * erzeugt höheres IO?

Häufig liest man in Foren, dass SELECT * höchst unperformant sei, da diese Methode ein höheres IO erzeugt. Diese Aussage ist Unsinn wenn nicht durch die Selektion definierter Attribute ein optimierter Index verwendet werden kann. Das nachfolgende Beispiel soll denn Unsinn dieser Aussage belegen:

SET NOCOUNT ON;
SET STATISTICS IO ON;

1: SELECT * FROM dbo.tbl_DataStructure;
2: SELECT Id, Col1 FROM dbo.tbl_DataStructure;
3: SELECT Id, Col2 FROM dbo.tbl_DataStructure;

SET STATISTICS IO OFF;
SET NOCOUNT OFF;

Das IO-Ergebnis zeigt auf, wie viele Seiten gelesen werden müssen, um die Daten zu liefern:

1: tbl_DataStructure-Tabelle. Scananzahl 1, logische Lesevorgänge 15416, ...
2: tbl_DataStructure-Tabelle. Scananzahl 1, logische Lesevorgänge 1031, ...
3: tbl_DataStructure-Tabelle. Scananzahl 1, logische Lesevorgänge 15416, ...

Wie das Ergebnis zeigt, erzeugt Abfrage 1 den gleichen IO wie Abfrage 3 während Abfrage 2 über 95% weniger IO erzeugt. Scheinbar sehen aber Abfrage 2 und Abfrage 3 bis auf die Auswahl eines anderes Attributs identisch aus. Warum also dieser Unterschied?

Zunächst muss man für das Verständnis dieser Werte wissen, welche Ausführungsstrategie SQL Server bei der Ausführung der Abfragen wählt.

ExecutionPlan 02

Während Abfrage 1 und Abfrage 3 einen identischen Ausführungsplan wählen (müssen) der den Clustered Index einbezieht, kann die Abfrage 2 auf den deutlich performanteren Index ix_tbl_DataStructure_Col1 zurückgreifen, da dieser Index den Clustered Key und das Attribut [col1] vollständig abdeckt.

Abfrage 3 erzeugt den identischen IO zu Abfrage 1, da – unabhängig von den auszugebenen Attributen – für alle Datensätze ALLE Datenpages durchlaufen werden müssen (15416 Datenseiten).

Der IO wird nicht durch die Limitierung der auszugebenden Attribute reduziert (horizontal) sonder nur durch Einschränkung der Daten selbst (vertikal) mittels WHERE-Klausel.

Würde Abfrage 2 wie folgt geändert, wird wieder ein Clustered Index Scan durchgeführt. Die hardcodierte Verwendung des “optimalen” Index würde die Performance sogar vollständig zerstören:

SELECT Id, Col1, Col2 FROM dbo.tbl_DataStructure;
SELECT Id, Col1, Col2 FROM dbo.tbl_DataStructure WITH (INDEX (ix_tbl_DataStructure_Col1));

ExecutionPlan 03

Fazit für Auswahl von Daten und Verwendung in Views / Prozeduren

Die Ausführung von SELECT [*] bietet keine nennenswerten Vorteile, wenn man die folgenden Aspekte berücksichtigt:

  1. SELECT [*] ist schlecht wartbar, da ein Programmierer sich immer erst einen Überblick über die Relation machen muss, die dem “*” zu Grunde liegt.
  2. SELECT [*] erzeugt mehr Netzwerkverkehr, da mehr Daten als unbedingt notwendig an die Anwendung geliefert werden muss.
  3. SELECT [*] ist bei Anwendung in Views riskant, wenn Strukturänderungen in den Relationen durchgeführt werden müssen. Insbesondere beim Entfernen von Attributen aus den Relationen müssen die Views, die von diesen Relationen abhängig sind, neu erstellt oder aktualisiert werden.
  4. SELECT [*] ist bei Relationen mit einer großen Anzahl von Attributen die unperformantestes Lösung, da in der Regel kein sinnvoller Index für die Auswahl von Daten verwendet werden kann. Es wird zwangsläufig immer ein Clustered Index Scan oder ein Table Scan (Heap) ausgeführt.
  5. SELECT [Spaltenauswahl] bringt keinen Vorteil gegenüber SELECT [*], wenn nicht ein für die Auswahl vorhandener Index verwendet werden kann!

Mein persönliches Fazit! Vermeiden Sie SELECT [*] schon aus Rücksicht auf Ihre Kollegen, die später mal Ihren Code überarbeiten müssen. Sollten Sie keine solche Kollegen haben, denken Sie an sich, wenn Sie den Code in ein paar Jahren wegen Anpassungen wieder anfassen müssen.

Desweiteren ist SELECT [*] so gut wie überhaupt nicht in der Lage, sinnvolle Indexstrategien einzusetzen, da – bis auf den Clustered Index – kaum ein Index ALLE Attribute einer Relation berücksichtigen kann.

Herzlichen Dank für’s Lesen.

Beispieldaten http://www.db-berater.de/files/blog/SELECT_ALL.zip
sys.all_columns http://msdn.microsoft.com/de-de/library/ms177522.aspx
sp_refreshview http://msdn.microsoft.com/de-de/library/ms187821.aspx
Clustered Key internals http://db-berater.blogspot.de/2012/11/optimierung-von-datenbankmodellen_11.html

1 Kommentar :

  1. richtig gut erklärt - es fehlt aber noch etwas:
    auch beim bloßen Hinzufügen von Spalten zu einer verwendete Tabelle kann es zu Problemen bei "SELECT *"-VIEW in Form einer Feldverschiebung kommen. Beispiel: "SELECT a.*, b.feld from ... " wird ein Feld in Tabelle a hinzugefügt, so wird das bei einer Ausführung der VIEW nicht erkannt und es kommt zu einer Feldverschiebung (!), die im besten Fall noch mit einem Typ-Fehler quittiert wird (im schlechten Fall merkt man es nicht und bekommt falsche Daten in den Feldern übermittelt). Sinnvoll wäre, wenn der SQL-Server die Änderungszeiten der VIEW mit denen der Tabellen vergleichen und ein Refresh dann selber vornehmen würde !

    AntwortenLöschen