Meistens leistet SQL Server eine sehr gute Arbeit bei der logisch äquivalenten Umformulierung der Abfragen, aber wie mit jeder Technologie gibt’s Fälle, in denen eine algorithmische Optimierung nicht das bestmögliche Ergebnis liefert. In diesem Artikel zeige ich einen von diesen Fällen und wie man dem SQL Server helfen kann, einen besseren Ausführungsplan zu finden.
Setup
Als Spielwiese nutze ich ein fiktives Beispielschema mit 3 Tabellen: DimDate (Stichtage), FactPosition (mehrere Positionen pro Tag) und FactTrade (mehrere Trades pro Position an einem Tag). Die Definition der Tabellen sieht wie folgt aus.
/*This is a date table in integer format YYYYMMDD*/ CREATE TABLE dbo.DimDate ( DateId INT NOT NULL, CONSTRAINT PK_DimDate PRIMARY KEY (DateId) ) GO /*Open positions per day. Position 4711 can be open for days 20160401, 20160402, and 20160403 etc.*/ CREATE TABLE dbo.FactPosition ( DateId INT NOT NULL, PositionId INT NOT NULL, Currency CHAR(3) NOT NULL, Rating VARCHAR(10) NOT NULL, PresentValue FLOAT NOT NULL /*Other payload columns*/ CONSTRAINT PK_FactPosition PRIMARY KEY (DateID, PositionID) FOREIGN KEY(DateID) REFERENCES dbo.DimDate(DateId) ) GO /*Ongoing trades per day and position with their status at the end of the day, amount etc. Position 4711 can have trades 1, 2, 3 on day 20160401 and 1, 3, 5 on day 20160402 */ CREATE TABLE dbo.FactTrade ( DateID INT NOT NULL, PositionID INT NOT NULL, TradeID INT NOT NULL, TradeType CHAR(1) NOT NULL, IsCompleted BIT NOT NULL, Amount Float NOT NULL, /*Other payload columns*/ CONSTRAINT PK_FactTrade PRIMARY KEY (DateID, PositionID, TradeID), FOREIGN KEY(DateID, PositionID) REFERENCES dbo.FactPosition(DateId, PositionId) )
Mit folgendem Skript befülle ich die Tabellen mit Beispieldaten:
/*This is just a numbers table with numbers from 1 to 10^5 filled with the same logic as by Itzik Ben-Gan http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers */ CREATE TABLE dbo.Numbers ( Number INT NOT NULL ,CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number) ); GO DECLARE @number_of_numbers INT = 100000; WITH a AS (SELECT 1 AS i UNION ALL SELECT 1), b AS (SELECT 1 AS i FROM a AS x, a AS y), c AS (SELECT 1 AS i FROM b AS x, b AS y), d AS (SELECT 1 AS i FROM c AS x, c AS y), e AS (SELECT 1 AS i FROM d AS x, d AS y), f AS (SELECT 1 AS i FROM e AS x, e AS y), cte_numbers AS ( SELECT TOP(@number_of_numbers) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Number FROM f ) INSERT dbo.Numbers SELECT * FROM cte_numbers GO /*Produce 300 days in year 2015*/ DECLARE @start DATE = '2015-01-01' ,@number_of_days INT = 300; WITH first_numbers AS ( SELECT TOP (@number_of_days) Number FROM dbo.Numbers ORDER BY Number ) INSERT dbo.DimDate SELECT YEAR(DATEADD(DAY, Number, @start)) * 10000 + MONTH(DATEADD(DAY, Number, @start)) * 100 + DAY(DATEADD(DAY, Number, @start)) FROM first_numbers; GO WITH randomlyChosenPositionRanges AS ( /* The logic here is not that important. Just generating about 10000 positions per day. Their ids are not really random but tend heavily to be ascending, but that's not relevant for this example. */ SELECT d.DateId ,a.Number FROM dbo.DimDate d CROSS APPLY (SELECT TOP 10000 * FROM dbo.Numbers WHERE Number>(ABS(CHECKSUM(d.DateId, NEWID()))%100000)) a ) INSERT dbo.FactPosition SELECT randomlyChosenPositionRanges.DateId ,randomlyChosenPositionRanges.Number AS PositionId ,'EUR' AS Currency ,'AAA' AS Rating ,123456.78 AS PresentValue FROM randomlyChosenPositionRanges GO WITH randomlyChosenTradeRanges AS ( /* Same as for positions. Generate about 2 trades for each position. This method is not quite koscher because it results in trade 73 belonging to different positions even on different days, which is nonsense from business point of view. But it's just ids, we can live with it for this case. */ SELECT fp.DateId ,fp.PositionId ,a.Number FROM dbo.FactPosition fp CROSS APPLY (SELECT TOP 2 * FROM dbo.Numbers WHERE Number>(ABS(CHECKSUM(fp.DateId, fp.PositionId, NEWID()))%100000)) a ) INSERT dbo.FactTrade SELECT randomlyChosenTradeRanges.DateId ,randomlyChosenTradeRanges.PositionId ,randomlyChosenTradeRanges.Number AS TradeID ,'R' AS TradeType ,1 AS IsCompleted ,987.65 AS Amount FROM randomlyChosenTradeRanges
Zugriff auf die Daten
Für die Abfrage, die alle Trades mit Positionsdaten für einen bestimmten Stichtag ermittelt, produziert SQL Server einen recht plausiblen Ausführungsplan.
DECLARE @dueday INT = 20151028; SELECT fp.* ,ft.TradeID ,ft.IsCompleted ,ft.Amount FROM dbo.FactPosition fp INNER JOIN dbo.FactTrade ft ON ft.DateID=fp.DateId AND ft.PositionID=fp.PositionId WHERE fp.DateId=@dueday;
Die Einschränkung auf den Stichtag wird dabei auf den Leseoperator der FactPosition übertragen (Predicate Pushdown), so dass nur der relevante Teil des Clustered Indexes mit dem Seek gelesen wird. Da die Tabellen mit INNER JOIN zusammengeknüpft werden, kann diese Einschränkung auch auf FactTrade erweitert werden (transitive Hülle).
Eine leicht andere Bedingung – Filterung auf den letzten vorhandenen Tag – erzeugt einen völlig anderen Ausführungsplan.
SELECT fp.* ,ft.TradeID ,ft.IsCompleted ,ft.Amount FROM dbo.FactPosition fp INNER JOIN dbo.FactTrade ft ON ft.DateID=fp.DateId AND ft.PositionID=fp.PositionId WHERE fp.DateId=(SELECT MAX(DateId) FROM dbo.DimDate);
Diesmal wurde nur eine Tabelle (FactTrade) für den gewählten Stichtag gelesen. Die FactPosition las SQL Server komplett (Clustered Index Scan vs. Seek), was sich auch sofort bei den logical Reads und Laufzeit bemerkbar macht.
Wenn man jetzt dem SQL Server ein wenig unter die Arme greift und die Bedingung auf FactTrade explizit erweitert, dann sieht der Ausführungsplan wieder deutlich besser aus.
SELECT fp.* ,ft.TradeID ,ft.IsCompleted ,ft.Amount FROM dbo.FactPosition fp INNER JOIN dbo.FactTrade ft ON ft.DateID=fp.DateId AND ft.PositionID=fp.PositionId WHERE fp.DateId=(SELECT MAX(DateId) FROM dbo.DimDate) AND ft.DateId=(SELECT MAX(DateId) FROM dbo.DimDate);
Unter SQL Server 2016 sieht die nicht optimierte Variante etwas anders aus (weniger CPU Zeit, mehr logische Reads), erreicht aber nicht die Qualität vom manuellen Fix.
Fazit
Mit jeder Version kann SQL Server die Abfragen immer besser verstehen und verarbeiten. In diesem Artikel wurde gezeigt, wo in der algorithmischen Logik noch Lücken bestehen und wie man mit ein paar WHERE-Bedingungen – die fürs menschliche Auge völlig überflüssig sind – SQL Server beibringen kann, bestimmte Abfragen deutlich effizienter auszuführen.
Zum Schluss möchte ich mich bei meinem guten Freund Martin Preiss für die Hilfe bei der Erklärung des Phänomens sowie für die Analyse des Verhaltens aus Oracle Sicht bedanken.
Sie sehen gerade einen Platzhalterinhalt von Facebook. Um auf den eigentlichen Inhalt zuzugreifen, klicken Sie auf die Schaltfläche unten. Bitte beachten Sie, dass dabei Daten an Drittanbieter weitergegeben werden.
Mehr InformationenSie sehen gerade einen Platzhalterinhalt von Instagram. Um auf den eigentlichen Inhalt zuzugreifen, klicken Sie auf die Schaltfläche unten. Bitte beachten Sie, dass dabei Daten an Drittanbieter weitergegeben werden.
Mehr InformationenSie sehen gerade einen Platzhalterinhalt von X. Um auf den eigentlichen Inhalt zuzugreifen, klicken Sie auf die Schaltfläche unten. Bitte beachten Sie, dass dabei Daten an Drittanbieter weitergegeben werden.
Mehr Informationen