Wenn es einen Weg gibt, etwas besser zu machen: finde ihn. – Thomas Alva Edison

Im letzten Beitrag SQL Server Filtered Index – Wie korrigiere ich Qualitätsmerkmale einer Architektur? beschrieb ich einen Lösungsansatz der Abfrageoptimierung, welcher jedoch das Problem nicht löste. Eine Abfrage die 55 Sekunden bei einer Benutzerinteraktion benötigte und nach einer Optimierung nur doppelt so schnell, wird die Erwartungen der Benutzer nicht erfüllen. Eine Benutzerinteraktion, die länger als zwei bis drei Sekunden dauert, wird vielerorts als eine Ewigkeit wahrgenommen. Du fragst dich jetzt: «Ja, aber wie wurde es bisher gemacht?». Eine Optimierungsmöglichkeit, welche in der Legacy-Anwendung zum Einsatz kam, war der Einsatz eines Cache. Einige Daten wurden mit dem Start der Applikation im Hintergrund vorgeladen. Das ist ein möglicher Lösungsansatz. Caching bevorzuge ich jedoch nicht als erste aller Optionen, denn diese Funktionalität erhöht die Komplexität einer Anwendung bei den Charaktereigenschaften der Datenqualität. Bevor ich weitere Komplexität in Kauf nehme, versuche ich das Problem an der Wurzel zu lösen.

Häufig hört man: «Der Unternehmenswert liegt in den Daten». Hier wünsche ich mir, dass dieser Umstand auch in den entsprechenden Daten-Architekturen von Softwarelösungen mehr Würdigung findet.

Kommen wir nun nochmal zurück auf die langsame Abfrage mit folgendem Aufbau:

SELECT DISTINCT 
  RELMLO_ID
FROM TABELLE1 
WHERE ID 
  IN (SELECT RELCHA_ID 
    FROM TABELLE2 
    WHERE YEAR_NR = 2016 
    AND MONTH_NR = 10 
    AND DAY_NR = 3)

Die folgenden Statistiken dazu sind aus meiner Sicht nicht optimal für die Aufgabenstellung:

• Table ‚Worktable‘. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
• Table ‚Workfile‘. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
• Table ‚TABELLE2‘. Scan count 39460, logical reads 468658, physical reads 15787, read-ahead reads 59646, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
• Table ‚TABELLE1‘. Scan count 5, logical reads 1542, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
• Table ‚Worktable‘. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Der Ausführungsplan zur Vollständigkeit ist auch sehr kostenintensiv:

Auf Konferenzen hörte ich oft: «Da ist dann halt ein Refactoring nötig». Die Realität sieht meist anders aus. Da existiert Zeit- und Kostendruck und die Refactorings werden aufgeschoben. Du kannst davon ausgehen, dass bei vielen Legacy-Anwedungen die Broken Window Theory die Ausgangslage besser beschreibt als die viel besungene Pfadfinder-Regel des sauberen Quellcode.

Nachdem wir für diese Anwendung ein besseres Verständnis bekommen hatten, machten wir uns ebenfalls eine Liste, aber dieser Umfang stand in Konflikt mit Zeit und Budget. Wir mussten in kleinen Schritten vorwärts gehen. Dem Team war es wichtig, mit den für uns zur Verfügung stehenden Mitteln schrittweise die Qualitätsmerkmale zu verbessern. Um in diesem Beispiel die Abfragegeschwindigkeit zu verbessern, legte ich zu Testzwecken eine Indexed View an.

Indexed Views sind einigen Einschränkungen unterworfen und so baute ich die Abfrage um:

SELECT
  cha. RELMLO_ID
  , tser. YEAR_NR
  , tser. MONTH_NR
  , tser. DAY_NR
  , COUNT_BIG(*) AS COUNT  
FROM dbo.TABELLE1 cha
INNER JOIN dbo.TABELLE2 tser ON cha.ID = tser.RELCHA_ID
GROUP BY
  cha.RELMLO_ID
  , tser.YEAR_NR
  , tser.MONTH_NR
  , tser.DAY_NR

Der DISTINCT-Parameter ist damit entfernt und die Eindeutigkeit wird nun mittels einem Group By Operator erzwungen. Diese Abfrage kann in einer Indexed View verwendet werden. Dazu muss eine View mit der Option Schemabinding und einem Clustered Index angelegt werden.

CREATE VIEW dbo.Test WITH SCHEMABINDING
AS
SELECT
  cha. RELMLO_ID
  , tser. YEAR_NR
  , tser. MONTH_NR
  , tser. DAY_NR
  , COUNT_BIG(*) AS COUNT  
FROM dbo.TABELLE1 cha
INNER JOIN dbo.TABELLE2 tser ON cha.ID = tser.RELCHA_ID
GROUP BY
  cha.RELMLO_ID
  , tser.YEAR_NR
  , tser.MONTH_NR
  , tser.DAY_NR
GO

-- Index
CREATE UNIQUE CLUSTERED INDEX IDX_Test   
    ON dbo.Test (RELMLO_ID, YEAR_NR, MONTH_NR, DAY_NR)
GO

Anschliessend probierte ich die Abfrage aus:

SELECT 
      RELMLO_ID
FROM dbo.Test 
WHERE YEAR_NR = 2016 
  AND MONTH_NR = 10 
  AND DAY_NR = 3

Ein Blick auf den Ausführungsplan zeigte folgendes:

Der Clustered Index der View wurde gar nicht verwendet. Von der Enterprise Edition von SQL Server kannte ich dieses Verhalten sonst nicht. Ich lass in der Dokumentation und fand den Hint NoExpand. Ich führte die Abfrage nochmals mit diesem Hint aus:

SELECT 
      RELMLO_ID
FROM dbo.Test WITH(NOEXPAND)
WHERE YEAR_NR = 2016 
  AND MONTH_NR = 10 
  AND DAY_NR = 3

Und siehe da, der Ausführungsplan verwendet den Index der View:

Die Statistiken zeigten nun folgendes Bild:

• Table ‚Test‘. Scan count 5, logical reads 15583, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Die Reads sind nun massiv reduziert und auch die Abfragedauer lag nun im Schnitt bei 300 ms. Die Worktable und das Workfile werden für die Abfrage auch nicht mehr benötigt. Der Ausführungsplan zeigt jedoch einen Clustered Index Scan. Ein Index mit dem eine Index Seek – Operation durchgeführt werden kann, wäre in diesem Zusammenhang besser.

Also legte ich folgenden Covered Index an und führte die Abfrage erneut aus:

CREATE NONCLUSTERED INDEX [CIDX_Test_Date]
ON [dbo].[Test] ([YEAR_NR],[MONTH_NR],[DAY_NR])
INCLUDE ([RELMLO_ID])

Der Ausführungsplan verwendete nun den Covered Index und ist dadurch in der Lage eine Index Seek – Operation auszuführen.

Die Statistiken mit diesem Index zeigten folgendes:

• Table ‚Test‘. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Die Reads konnten mit diesem Index nochmal massiv reduziert werden. Die Abfragedauer lag nun bei 27 ms. Nun bewegen wir uns in einem Geschwindigkeitsbereich, der bedenkenlos mit einem Benutzerinterface verwendet werden kann. Im Bezug zur Ausgangslage ist die Abfrage mehr als 2’000-mal schneller.

Dies ist jedoch nur eine Zwischenlösung um die Schmerzen zu lindern, mittelfristig ist eine Schrittweise Anpassung der Datenarchitektur sinnvoller, da zu viele Indizes eine Verschlechterung der Schreibgeschwindigkeit bewirken können.