SQL Count Abfrage beschleunigen
Allgemeine Diskussion
Alle Antworten
-
Hallo Matthias,
Dein Problem ist, dass Du keinen geeigneten Index für eine SEEK-Operation hast. Dein LIKE ist nicht problematisch, da Du keine Platzhalter verwendest. Der "Nachteil", wie von Benjamin Hoch angedeutet, ist hier zu vernachlässigen, da beide Varianten einen performanten INDEX SEEK verwenden. Das gilt aber nur so lange, wie die Verteilung der Daten möglichst gleichmässig ist (würde zu Deinem Problem aber deutlich zu weit führen).
Andreas hat Dich schon auf den Weg von Columnstore gewiesen; Nachteil dieser Lösung ist, dass es ein "ENTERPRISE" Feature ist und erst ab SQL 2014 updateable ist. Das halte ich für einen wichtigen Punkt, da Du ja schreibst, dass Du noch Daten eintragen willst
Die Lösung Deines Problems liegt in der richtigen Indexierung. Wenn Du ein COUNT(*) anwendest, sucht Microsoft SQL Server IMMER nach dem kleinsten Index. Der Query Optimizer ist cost based und berechnet die Optimierung unter anderem aus dem zu generierenden IO und den Kosten für die einzusetzenden Operatoren.
Für ein besseres Verständnis des Vorgehens habe ich eine Testtabelle mit insgesamt 50 Messungen á 7500 Datensätze erstellt. Diese Tabelle hat einen - für das Beispiel - trivialen Aufbau:
/* Tabelle mit Clustered Index erstellen */ CREATE TABLE dbo.Messungen ( Id int NOT NULL IDENTITY (1, 1), MessNr char(5) NOT NULL, MessWert int, Col1 char(300) NOT NULL DEFAULT ('Nur ein Lückenfüller') ); GO CREATE UNIQUE CLUSTERED INDEX cix_Messungen_Id ON dbo.Messungen (Id) WITH DROP_EXISTING; GODie Tabelle besitzt einen Clustered Index auf ID; sie ist also nach der ID sortiert. Insgesamt gibt es nur diesen EINEN Index, der die Tabelle selbst ist. Wird also ein Datensatz gelesen, so muss immer die komplette Datenzeile gelesen warden (daher der Hinweis von Andreas auf Columnstore). Wie Du unschwer aus der obigen Struktur entnehmen kannst, hat ein Datensatz eine "Daten"-Lange von 313 Bytes +5 Bytes als Overhead = 318 Bytes.
Auf eine Datenseite können 8.060 Bytes gespeichert warden; somit gehen 25 Datensätze auf eine Datenseite. Die Daten warden also auf ca. 15.000 Datenseiten gespeichert!
Mit der nachfolgenden Abfrage suche ich nach der Messung '00001'
SET STATISTICS IO ON; GO SELECT Id, MessNr FROM dbo.Messungen WHERE MessNr = '00001';
Wenn Du dir das Ergebnis anschaust, werden die grob berechneten 15.000 Datenseiten vollständig durchsucht. Gleiches gilt natürlich, wenn Du statt Einzelwerte einen COUNT auf die Datenmenge ausführst!
SET STATISTICS IO ON; GO SELECT MessNr, COUNT_BIG(*) AS Messungen FROM dbo.Messungen WHERE MessNr = '00001'
GROUP BY MessNr
OPTION (QUERYTRACEON 9130);Die Ursache ist relative einfach zu erklären. In beiden Fallen muss Microsoft SQL Server die vollständige Tabelle durchsuchen, da der EINZIGE Index auf der ID liegt und nicht der MessNr. (Ist so, als ob Du in einem Telefonbuch nach dem Vornamen suchst)
Microsoft SQL Server muss für die Gruppierung erneut die vollständige Tabelle durchsuchen. Fügst Du also weitere Messungen hinzu, vergrößert sich das Volumen der zu durchsuchenden Datenmenge (Es müssen mehr Datenseiten durchsucht werden). Je "breiter" Deine Tabelle ist, umso weniger Datensätze passen auf eine Datenseite und NOCH MEHR Datenseiten müssen gelesen werden.
Also, was macht man in diesem Fall? Man verwendet einen Index, der "schmaler" ist und somit weniger Datenseiten lessen muss. Idealerweise legt man diesen Index auf ein Attribut, nach dem man regelmäßig sucht (in diesem Beispiel also die MEssNr). Ein möglicher Index könnte wie folgt aussehen:
CREATE NONCLUSTERED INDEX ix_Messungen_MessNr ON dbo.Messungen(MessNr); GO
Durch die Erstellung des obigen Index habe ich zwei Fliegen mit einer Klappe geschlagen:
- Auf einer Datenseite können nun MEHR Indexsätze gespeichert werden: 11 Bytes / Datensatz ~ 730 Datensätze
- Das Feld, nach dem ich suche, ist indiziert und ein teurer SCAN kann durch einen SEEK ersetzt werden
Führst Du die obige Abfrage nach der Anzahl der Messungen erneut aus, erhältst Du folgenden Ausführungsplan:
Mit Hilfe eines geeigneten Index hast Du sowohl den Suchalgorithmus als auch das Datenvolumen eingeschränkt. Wäre in dem obigen Beispiel nicht nur ein Clustered Index vorhanden sondern noch ein Index auf dem Feld Col1, so hätte Microsoft SQL Server sich für den Index auf COL1 im ersten Beispiel entschieden, da ja 300 Bytes kleiner sind als 318 Bytes :)
Ich habe zu diesem Thema mal geblogged. Vielleicht hilft Dir das Beispiel im Artikel etwas weiter:
http://db-berater.blogspot.de/2013/06/aggregationsfunktionen-beschleunigen.html
Zumindest zeigt er die Zusammenhänge etwas detaillierter als es diese Antwort vermag!
MCM - SQL Server 2008
MCSE - SQL Server 2012
db Berater GmbH
SQL Server Blog (german only)
