Performance von TOP 1
Frage
Antworten
-
Hallo Jörg,
wenn ein Index auf [aktANPLFDNR] liegt, dann wird - zurecht - die vollständige Tabelle gescannt (leider hast Du keinen Execution Plan beigefügt!).
Ich habe mal versucht, Dein Beispiel nachzuvollziehen (auch hier wären ein paar Beispieldaten wünschenswert gewesen :) ).
USE tempdb; GO CREATE TABLE dbo.demo ( Id INT NOT NULL IDENTITY (1, 1), AnsprechPartner TINYINT NOT NULL, aktANPLFDNR INT NOT NULL, C1 CHAR(1000), aktDatum DATETIME NOT NULL DEFAULT (DATEADD(DAY, CAST(RAND() * 365 * -1 AS INT), GETDATE())) ); GO SET NOCOUNT ON; DECLARE @AnsprechPartner TINYINT; DECLARE @aktANPLFDNR INT; DECLARE @C1 CHAR(1000); DECLARE c CURSOR FORWARD_ONLY FAST_FORWARD FOR SELECT severity, language_id, CAST(text AS CHAR(1000)) FROM sys.messages; OPEN c FETCH NEXT FROM c INTO @AnsprechPartner, @aktANPLFDNR, @C1 WHILE @@FETCH_STATUS != -1 BEGIN INSERT INTO dbo.demo (AnsprechPartner, aktANPLFDNR, C1) VALUES (@AnsprechPartner, @aktANPLFDNR, @C1); FETCH NEXT FROM c INTO @AnsprechPartner, @aktANPLFDNR, @C1 END GO CLOSE c; DEALLOCATE c; GO
Mit dem obigen Skript erzeuge ich eine Tabelle mit ca. 280.000 Datensätzen mit einem willkürlichen Daten für jeden Eintrag. Zusätzlich lege ich - so, wie ich Deine Anforderungen lese - mal zwei Indexe an. Zum einen C.I. und zum anderen den von Dir genannten Index auf aktANPLFDNR.
Der Datentyp ist hier zunächst irrelevant, da ein (VAR)CHAR sich dem INT "beugen" muss - ist also für die Ausführung der Abfrage zunächst irrelevant.
CREATE UNIQUE CLUSTERED INDEX cuix_demo_id ON dbo.demo(Id); CREATE NONCLUSTERED INDEX nix_demo_aktANPLFDNR ON dbo.demo (aktANPLFDNR)
Mehr Informationen gibt Dein Sachverhalt aktuell nicht her.
Wenn ich nun folgende Abfrage ausführe, erhalte daraus den nachfolgenden Ausführungsplan:
SELECT TOP (1) aktDatum FROM dbo.demo WHERE aktANPLFDNR = '1044' ORDER BY aktDatum DESC OPTION (QUERYTRACEON 9130); GO
Die Erwartung ist, dass zunächst mit einem INDEX-SEEK das Prädikat gesucht wird und anschließend das letzte Datum mit einem Eintrag.
Das Ergebnis ist "erschreckend" da die komplette Tabelle (CLUSTERED INDEX SCAN) zunächst durchsucht wird und in einem FILTER erst auf die Teilmenge der gesuchten aktANPLFDNR reduziert wird. Das hängt damit zusammen, dass Microsoft SQL Server - bedingt durch den TOP-Operator - den INDEX nicht effizient verwenden kann, da dann aktDATUM (relevant für den SORT-Operator) nicht kennt. Für den Filter müssen bereits aktANPLFDNR und aktDATUM bereits vorhanden sein, um später zu sortieren! Somit scheidet die Verwendung des gesetzten Operators aus.
Nachdem - in meinem Beispiel - 280.000 Datensätze geliefert wurden, kann nach der LFDNR gesucht werden. Diese Teilmenge wird dann sortiert und der TOP-Operator bricht automatisch nach dem erhalt des ersten Datensatzes die Operation ab und die Abfrage wird beendet.
Dieses Problem kannst Du am einfachsten umgehen, wenn aktDATUM Bestandteil Deines Index ist.
CREATE NONCLUSTERED INDEX nix_demo_aktANPLFDNR ON dbo.demo ( aktANPLFDNR, aktDatum ) WITH DROP_EXISTING;
Die gleiche Abfrage mit dem oben implementierten Index ist deutlich performanter. Die Sortierung von aktDATUM ist vollkommen irrelevant für Microsoft SQL Server. ist das Attribut DESC sortiert, kann SQL Server gleich den ersten Datensatz verwenden; ist das Attribut ASC sortiert, nimmt man halt den letzten Datensatz.
Bezüglich der Variationen in der Laufzeit können noch zwei weitere Punkte relevant sein.
- Der neue Query Estimator in Verbindung mit dem ASCENDING KEY Problem
- READ COMMITTED SNAPSHOT ISOLATION und offene Transaktionen
Eine vollständige Beschreibung dieser Problematik wäre ein Blog-Eintrag wert und würde den Sinn und Zweck dieses Forums sprengen. Eine gute Anlaufquelle wäre aber z. B. hier:
https://www.sqlshack.com/ascending-key-and-ce-model-variation/
Über den Zusammenhang mit lang laufenden Abfragen in Zusammenhang mit RCSI habe ich selbst geblogged, da ich bei einem Kunden ein solches Problem vorgefunden habe:
Gerne würde ich hier weiterhelfen - ohne Metadaten und genauer Datenmenge / -struktur ist das aber nur bedingt möglich. Vielleicht helfen die Anregungen aber ein wenig weiter.
Uwe Ricken (Blog | Twitter)
Microsoft Certiied Master - SQL Server 2008
Microsoft Certified Solution Master - CHARTER Data Platform
Microsoft Certified Solution Expert - Data Platform
db Berater GmbH
Microsoft SQL Server Blog (german only)- Als Antwort markiert SchneiderJoerg Mittwoch, 19. September 2018 15:23
Alle Antworten
-
SQL-Server die Konstante in den Feldtyp konvertiert und nicht das Feld in den Typ der Konstante
Das ist nicht korrekt, was konvertiert wird, wird nach den Data type precedence (Transact-SQL) geregelt.
Beispiel:
select * from sys.objects where Name = {d N'2018-09-19'};Ergebnis:
Das varchar Feld wird nach Datetime konvertiert, weil dieses eine höhere Rangfolge hat.
Olaf Helper
[ Blog] [ Xing] [ MVP]- Bearbeitet Olaf HelperMVP Mittwoch, 19. September 2018 12:50
-
Hallo Jörg,
wenn ein Index auf [aktANPLFDNR] liegt, dann wird - zurecht - die vollständige Tabelle gescannt (leider hast Du keinen Execution Plan beigefügt!).
Ich habe mal versucht, Dein Beispiel nachzuvollziehen (auch hier wären ein paar Beispieldaten wünschenswert gewesen :) ).
USE tempdb; GO CREATE TABLE dbo.demo ( Id INT NOT NULL IDENTITY (1, 1), AnsprechPartner TINYINT NOT NULL, aktANPLFDNR INT NOT NULL, C1 CHAR(1000), aktDatum DATETIME NOT NULL DEFAULT (DATEADD(DAY, CAST(RAND() * 365 * -1 AS INT), GETDATE())) ); GO SET NOCOUNT ON; DECLARE @AnsprechPartner TINYINT; DECLARE @aktANPLFDNR INT; DECLARE @C1 CHAR(1000); DECLARE c CURSOR FORWARD_ONLY FAST_FORWARD FOR SELECT severity, language_id, CAST(text AS CHAR(1000)) FROM sys.messages; OPEN c FETCH NEXT FROM c INTO @AnsprechPartner, @aktANPLFDNR, @C1 WHILE @@FETCH_STATUS != -1 BEGIN INSERT INTO dbo.demo (AnsprechPartner, aktANPLFDNR, C1) VALUES (@AnsprechPartner, @aktANPLFDNR, @C1); FETCH NEXT FROM c INTO @AnsprechPartner, @aktANPLFDNR, @C1 END GO CLOSE c; DEALLOCATE c; GO
Mit dem obigen Skript erzeuge ich eine Tabelle mit ca. 280.000 Datensätzen mit einem willkürlichen Daten für jeden Eintrag. Zusätzlich lege ich - so, wie ich Deine Anforderungen lese - mal zwei Indexe an. Zum einen C.I. und zum anderen den von Dir genannten Index auf aktANPLFDNR.
Der Datentyp ist hier zunächst irrelevant, da ein (VAR)CHAR sich dem INT "beugen" muss - ist also für die Ausführung der Abfrage zunächst irrelevant.
CREATE UNIQUE CLUSTERED INDEX cuix_demo_id ON dbo.demo(Id); CREATE NONCLUSTERED INDEX nix_demo_aktANPLFDNR ON dbo.demo (aktANPLFDNR)
Mehr Informationen gibt Dein Sachverhalt aktuell nicht her.
Wenn ich nun folgende Abfrage ausführe, erhalte daraus den nachfolgenden Ausführungsplan:
SELECT TOP (1) aktDatum FROM dbo.demo WHERE aktANPLFDNR = '1044' ORDER BY aktDatum DESC OPTION (QUERYTRACEON 9130); GO
Die Erwartung ist, dass zunächst mit einem INDEX-SEEK das Prädikat gesucht wird und anschließend das letzte Datum mit einem Eintrag.
Das Ergebnis ist "erschreckend" da die komplette Tabelle (CLUSTERED INDEX SCAN) zunächst durchsucht wird und in einem FILTER erst auf die Teilmenge der gesuchten aktANPLFDNR reduziert wird. Das hängt damit zusammen, dass Microsoft SQL Server - bedingt durch den TOP-Operator - den INDEX nicht effizient verwenden kann, da dann aktDATUM (relevant für den SORT-Operator) nicht kennt. Für den Filter müssen bereits aktANPLFDNR und aktDATUM bereits vorhanden sein, um später zu sortieren! Somit scheidet die Verwendung des gesetzten Operators aus.
Nachdem - in meinem Beispiel - 280.000 Datensätze geliefert wurden, kann nach der LFDNR gesucht werden. Diese Teilmenge wird dann sortiert und der TOP-Operator bricht automatisch nach dem erhalt des ersten Datensatzes die Operation ab und die Abfrage wird beendet.
Dieses Problem kannst Du am einfachsten umgehen, wenn aktDATUM Bestandteil Deines Index ist.
CREATE NONCLUSTERED INDEX nix_demo_aktANPLFDNR ON dbo.demo ( aktANPLFDNR, aktDatum ) WITH DROP_EXISTING;
Die gleiche Abfrage mit dem oben implementierten Index ist deutlich performanter. Die Sortierung von aktDATUM ist vollkommen irrelevant für Microsoft SQL Server. ist das Attribut DESC sortiert, kann SQL Server gleich den ersten Datensatz verwenden; ist das Attribut ASC sortiert, nimmt man halt den letzten Datensatz.
Bezüglich der Variationen in der Laufzeit können noch zwei weitere Punkte relevant sein.
- Der neue Query Estimator in Verbindung mit dem ASCENDING KEY Problem
- READ COMMITTED SNAPSHOT ISOLATION und offene Transaktionen
Eine vollständige Beschreibung dieser Problematik wäre ein Blog-Eintrag wert und würde den Sinn und Zweck dieses Forums sprengen. Eine gute Anlaufquelle wäre aber z. B. hier:
https://www.sqlshack.com/ascending-key-and-ce-model-variation/
Über den Zusammenhang mit lang laufenden Abfragen in Zusammenhang mit RCSI habe ich selbst geblogged, da ich bei einem Kunden ein solches Problem vorgefunden habe:
Gerne würde ich hier weiterhelfen - ohne Metadaten und genauer Datenmenge / -struktur ist das aber nur bedingt möglich. Vielleicht helfen die Anregungen aber ein wenig weiter.
Uwe Ricken (Blog | Twitter)
Microsoft Certiied Master - SQL Server 2008
Microsoft Certified Solution Master - CHARTER Data Platform
Microsoft Certified Solution Expert - Data Platform
db Berater GmbH
Microsoft SQL Server Blog (german only)- Als Antwort markiert SchneiderJoerg Mittwoch, 19. September 2018 15:23
-
Diesbezüglich habe ich aus einer anderen Geschichte heraus nur mitbekommen, dass der SQL-Server die Konstante in den Feldtyp konvertiert und nicht das Feld in den Typ der Konstante, was einen Tablescan nach sich ziehe würde.
where numericfiled = 'XX'
führte zur Fehlermeldung dass 'XX' nicht in Integer konvertierbar sei, womit er ja recht hat.
Hallo Baldur,
obwohl hier Olaf bereits den entscheidenden Hinweis gegeben hat, muss ich da auch widersprechen. Es versteht sich natürlich von selbst, dass 'XX' nicht numerisch ist - aber was ist mit '11', '99', ...
DROP TABLE dbo.demo; GO SELECT language_id, message_id, CAST(severity AS CHAR(2)) AS severity, CAST(text AS CHAR(1000)) AS text INTO dbo.demo FROM sys.messages; GO CREATE NONCLUSTERED INDEX nix_demo_severity ON dbo.demo(severity); GO
Das obige Skript erzeugt eine Testtabelle mit einem NCI auf dem Attribut [severity], das ein String ist.
SELECT * FROM dbo.demo WHERE severity = 12; SELECT * FROM dbo.demo WHERE severity = '12';
Die beiden obigen Abfragen erzeugen vollkommen unterschiedliche Ausführungspläne. Während Abfrage 1 einen TABLE-SCAN verwendet, kann Abfrage 2 einen performanten INDEX-SEEK anwenden.
Der Grund für dieses Verhalten ist von Olaf bereits weiter oben erläutert worden. ANDERS jedoch sieht es aus, wenn der Datentyp des Attributs "höherrangig" ist. Gleiches Beispiel - nur diesmal ist [severity] vom Datentypen TINYINT!
DROP TABLE dbo.demo; GO SELECT language_id, message_id, severity, CAST(text AS CHAR(1000)) AS text INTO dbo.demo FROM sys.messages; GO CREATE NONCLUSTERED INDEX nix_demo_severity ON dbo.demo(severity); GO
Wie man sehr schön erkennen kann, verwenden beide Abfragen den gleichen Plan. Während Abfrage 1 den korrekten Datentypen verwendet (TINYINT), muss Microsoft SQL Server im zweiten Beispiel das Prädikat zu einem TINYINT konvertieren. Das geht natürlich deutlich performanter als - wie im ersten Beispiel gezeigt - alle Daten der Spalte [severity] zu konvertieren.
Uwe Ricken (Blog | Twitter)
Microsoft Certiied Master - SQL Server 2008
Microsoft Certified Solution Master - CHARTER Data Platform
Microsoft Certified Solution Expert - Data Platform
db Berater GmbH
Microsoft SQL Server Blog (german only)
