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.