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
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*/CREATETABLE dbo.DimDate
(
DateId INTNOTNULL,
CONSTRAINT PK_DimDate PRIMARYKEY (DateId)
)
GO/*Open positions per day. Position 4711 can be open for days 20160401, 20160402, and 20160403 etc.*/CREATETABLE dbo.FactPosition
(
DateId INTNOTNULL,
PositionId INTNOTNULL,
Currency CHAR(3) NOTNULL,
Rating VARCHAR(10) NOTNULL,
PresentValue FLOATNOTNULL/*Other payload columns*/CONSTRAINT PK_FactPosition PRIMARYKEY (DateID, PositionID)
FOREIGNKEY(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*/CREATETABLE dbo.FactTrade
(
DateID INTNOTNULL,
PositionID INTNOTNULL,
TradeID INTNOTNULL,
TradeType CHAR(1) NOTNULL,
IsCompleted BITNOTNULL,
Amount FloatNOTNULL,
/*Other payload columns*/CONSTRAINT PK_FactTrade PRIMARYKEY (DateID, PositionID, TradeID),
FOREIGNKEY(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 */CREATETABLE dbo.Numbers
(
Number INTNOTNULL
,CONSTRAINT PK_Numbers PRIMARYKEYCLUSTERED (Number)
);
GODECLARE @number_of_numbers INT = 100000;
WITH
a AS (SELECT 1 AS i UNIONALLSELECT 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
(
SELECTTOP(@number_of_numbers)
ROW_NUMBER() OVER (ORDERBY (SELECTNULL)) AS Number
FROM f
)
INSERT dbo.Numbers
SELECT *
FROM cte_numbers
GO/*Produce 300 days in year 2015*/DECLARE @startDATE = '2015-01-01'
,@number_of_days INT = 300;
WITH first_numbers AS
(
SELECTTOP (@number_of_days) Number
FROM dbo.Numbers
ORDERBY Number
)
INSERT dbo.DimDate
SELECTYEAR(DATEADD(DAY, Number, @start)) * 10000 +
MONTH(DATEADD(DAY, Number, @start)) * 100 +
DAY(DATEADD(DAY, Number, @start))
FROM first_numbers;
GOWITH 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 (SELECTTOP 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
GOWITH 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 (SELECTTOP 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
INNERJOIN 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
INNERJOIN dbo.FactTrade ft ON
ft.DateID=fp.DateId
AND ft.PositionID=fp.PositionId
WHERE
fp.DateId=(SELECTMAX(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
INNERJOIN dbo.FactTrade ft ON
ft.DateID=fp.DateId
AND ft.PositionID=fp.PositionId
WHERE
fp.DateId=(SELECTMAX(DateId) FROM dbo.DimDate)
AND ft.DateId=(SELECTMAX(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.
Im SDX Flurfunk schreiben SDX eXperts über Themen aus ihrem täglichen Arbeitsgebiet. So verschieden die eXperts sind, so verschieden sind auch ihre Beiträge…
SDX eXperts unterstützen Enterprise Unternehmen bei der Realisierung von .NET und BI Lösungen in den Kompetenzfeldern: Business Intelligence, Win/Web Development, Cloud Development und Projektmanagement (Scrum). Typischerweise sind sie als Software Architekt, Lead Developer, techn. Projektleiter, BI Spezialist oder Scrum Master vor Ort beim Kunden tätig.