Exa­sol ist eine Daten­bank, die auf die Per­for­mance von Queries aus­ge­legt ist und den Anspruch hat, war­tungs­arm zu sein.

In die­sem Blog­bei­trag sol­len Tipps zur Opti­mie­rung (Teil 1) und zum guten Umgang mit Exa­sol (Teil 2) gege­ben wer­den. Vie­les davon lässt sich auch auf andere Daten­ban­ken anwenden.

SQL-Befehle wer­den in die­sem Bei­trag in Block­buch­sta­ben geschrieben.

Teil1: Query-Opti­mie­rung

Bei der Aus­füh­rung von Daten­bank­ab­fra­gen nimmt Exa­sol eigen­stän­dig Opti­mie­run­gen vor. In man­chen Fäl­len ist diese Opti­mie­rung nicht per­fekt und kann hän­disch nach­ge­bes­sert wer­den. Im Fol­gen­den wird erklärt, wie diese Nach­bes­se­rung mög­lich ist und was für eine gute Per­for­mance in SQL-State­ments beach­tet wer­den kann.

Join-Rei­hen­folge erzwingen 

Die selbst­stän­di­gen Opti­mie­rungs­maß­nah­men von Exa­sol umfas­sen u. a. die Wahl der Join-Rei­hen­folge. Grund­sätz­lich gilt, dass mit der klei­ne­ren Tabelle als Stamm­ta­belle eine bes­sere Per­for­mance erzielt wer­den kann (sie also nach vorne gestellt wer­den sollte). Dann müs­sen tech­nisch gese­hen nur wenige Zei­len ver­ar­bei­tet wer­den. In sel­te­nen Fäl­len kann es pas­sie­ren, dass Exa­sol hier­bei keine opti­male Wahl trifft. Ein Grund dafür kann sein, dass nicht berück­sich­tigt wird, dass es bei bestimm­ten Joins in einer der bei­den Tabel­len nur wenige Join-Part­ner gibt. Dadurch kön­nen beim Join viele Zei­len die­ser Tabelle raus­fal­len, was dazu führt, dass die grö­ßere Tabelle effek­tiv die klei­nere Tabelle ist.

Zur Opti­mie­rung gibt es eine Mög­lich­keit die Join-Rei­hen­folge bzw. die Wahl der Stamm­ta­belle zu erzwin­gen. Das geschieht am bes­ten, indem man einen LEFT JOIN anstelle eines (INNER) JOINS ver­wen­det und die WHERE-Bedin­gung um ein IS NOT NULL der Join-Spalte der zwei­ten Tabelle ergänzt. Wenn es sicher ist, dass es immer einen Join-Part­ner gibt, kann die WHERE-Bedin­gung auch weg­ge­las­sen werden.

Code­bei­spiel

Vor­her:

SELECT table1.column1 FROM table1	-- effektiv kleinere Tabelle
JOIN table2							-- effektiv größere Tabelle
    ON table1.column2 = table2.column2;

Nach­her:

SELECT table1.column1 FROM table1	-- effektiv kleinere Tabelle
LEFT JOIN table2					-- effektiv größere Tabelle
    ON table1.column2 = table2.column2
WHERE table2.column2 IS NOT NULL;

Mate­ria­li­sie­rung erzwingen

Wer­den bei der Aus­füh­rung einer Daten­bank­ab­frage Zwi­schen­er­geb­nisse in (tem­po­räre) Blö­cke des RAM geschrie­ben, nennt man das Mate­ria­li­sie­rung. Wenn mög­lich, soll­ten Mate­ria­li­sie­run­gen ver­mie­den wer­den. Wenn sie klein sind, kön­nen sie die Per­for­mance manch­mal aber auch verbessern.

Bei der Ver­wen­dung von Com­mon Table Expres­sions (CTE) wer­den nicht auto­ma­tisch Mate­ria­li­sie­run­gen erzeugt. Der Opti­mi­zer von Exa­sol schreibt grund­sätz­lich, wenn mög­lich, alle CTEs in Inline-Joins etc. um.

Eine Mate­ria­li­sie­rung wird erzeugt, wenn eine Sub-Query, CTE oder View eine der fol­gen­den Ope­ra­to­ren enthält:

• ORDER BY
• GROUP BY
• ROWNUM
• DISTINCT
• Ana­ly­ti­sche Funk­tion mit ORDER BY.

Die Best Prac­tice, um eine Mate­ria­li­sie­rung zu erzwin­gen, ist an die CTE ein ORDER BY FALSE anzuhängen.

Ver­meide IN und EXISTS

Die Ope­ra­to­ren IN und EXISTS wer­den von Exa­sols Opti­mi­zer gleich behan­delt. Sie soll­ten prin­zi­pi­ell ver­mie­den wer­den, weil sie nicht per­for­mant sind. (Sie ver­ur­sa­chen auch eine Mate­ria­li­sie­rung.) Sol­che State­ments kön­nen fast immer so umge­schrie­ben wer­den, dass statt­des­sen ein Join ver­wen­det wird. Bei NOT IN und NOT EXISTS geht es manch­mal nicht.

Häu­fig wird als IN-Liste in der WHERE-Bedin­gung das Ergeb­nis einer Sub-Query oder CTE ver­wen­det. Dann kann die WHERE-IN-Bedin­gung durch ein Join mit der CTE ersetzt werden.

Code­bei­spiel

Vor­her:

WITH mycte AS (
    SELECT column2 FROM table2
    WHERE column3 = 'Special Value')
SELECT SUM(table1.column1) FROM table1
WHERE table1.column2 IN (SELECT column2 FROM mycte);

Nach­her:

WITH mycte AS (
    SELECT column2 FROM table2
    WHERE column3 = 'Special Value')
SELECT SUM(table1.column1) FROM table1
JOIN mycte
    ON table1.column2 = mycte.column2;

Vor­sicht ist gebo­ten, wenn der Join Dupli­kate erzeu­gen kann, die es bei der WHERE-IN-Bedin­gung nicht gege­ben hätte. Dann muss im Ide­al­fall die CTE so umge­schrie­ben wer­den, dass keine Dupli­kate auf­tre­ten. Ist das nicht gut mög­lich, kann statt­des­sen auf DISTINCT oder GROUP BY zur Ver­mei­dung von Dupli­ka­ten zurück­ge­grif­fen wer­den. Dies führt zwar auch zu einer Mate­ria­li­sie­rung, ins­ge­samt ist das aber trotz­dem per­for­man­ter als die Ver­wen­dung eines IN.

Ver­meide UNION (ohne ALL)

Der UNION-Befehl ist sehr res­sour­cen­in­ten­siv, da jede Zeile mit jeder ande­ren Zeile ver­gli­chen wer­den muss. Grund­sätz­lich sollte UNION ALL anstatt UNION ver­wen­det wer­den. UNION wird von Exa­sol grund­sätz­lich so umge­setzt, dass erst ein UNION ALL aus­ge­führt wird und danach ein GROUP BY auf alle Spal­ten aus­ge­führt wird.

Sor­tie­rungs­op­ti­mie­rung

Sor­tie­run­gen sind res­sour­cen­in­ten­siv, da in einem par­al­le­len Sys­tem die Daten auf ver­schie­de­nen Kno­ten lie­gen, so dass viel Netz­werk­kom­mu­ni­ka­tion erfor­der­lich ist.

Exa­sol muss bei fol­gen­den Befeh­len sortieren:

• ORDER BY
• Ana­ly­ti­sche Funk­tio­nen mit ORDER BY, z. B. RANK, ROW_NUMBER oder SUM oder AVG mit OVER-Klausel

Um die Per­for­mance zu ver­bes­sern, gibt es ent­we­der die Mög­lich­kei­ten die Daten­menge zu redu­zie­ren oder, was noch bes­ser ist, Sor­tie­run­gen zu vermeiden.

Eine Mög­lich­keit, Daten zu redu­zie­ren, ist, bis zur Mate­ria­li­sie­rung weni­ger Spal­ten mit­zu­neh­men und sie statt­des­sen erst im Nach­hin­ein über einen JOIN hinzuzufügen.

Bei Exa­sol kann eine Ver­wen­dung von ROW_NUMBER() zusam­men mit Rank = 1 oft stark ver­bes­sert wer­den. Zur Ver­an­schau­li­chung hier ein pas­sen­des Minimalbeispiel:

WITH mycte AS (
    SELECT table1.key1,
        table1.key2,
        table2.key3,
        table1.zeitspalte,
        ROW_NUMBER() OVER (PARTITION BY table1.key2, table2.key3 ORDER BY table1.zeitspalte DESC) AS rnk
    FROM table1
    JOIN table2
        ON table1.key1 = table2.key1)
SELECT key1,
    key2,
    key3,
    zeitspalte
FROM mycte
WHERE rnk = 1;

Bei dem SQL wird für jede Kom­bi­na­tion aus key2 und key3 anhand des Datums ein Rang für key1 erstellt und dann mit „WHERE rnk = 1“ gefil­tert, so dass von jeder Kom­bi­na­tion nur der neuste key1-Daten­satz aus­ge­ge­ben wird.

1. Mög­lich­keit: Ver­wen­dung von Pre­fe­rence SQL („PREFERRING HIGH …“)
   Syn­tax:

SELECT [Alle Spaltennamen] FROM [Tabellenname] PREFERRING HIGH [Spaltenname der Spalte, die gerankt würde];

2. Mög­lich­keit: Ver­wen­dung von Aggre­ga­ti­ons­funk­tio­nen, z.B. MAX(), s. u.

Sor­tie­rungs­op­ti­mie­rung mit Aggregationsfunktionen

Die Query aus dem vor­he­ri­gen Abschnitt kann mit Hilfe der MAX-Funk­tion opti­miert wer­den. Wird im glei­chen Zug ein Join anstatt einer IN-Bedin­gung ver­wen­det, ergibt sich

WITH CTE_letzter AS (
    SELECT table1.key2, table2.key3, MAX(table1.zeitspalte) AS max_zeit
    FROM table2
    JOIN table1
        ON table1.key1 = table2.key1
    GROUP BY table1.key2, table2.key3)
SELECT FIRST_VALUE(table1.key1),
    table1.key2,
    table2.key3
    table1.zeitspalte
FROM table1
JOIN table2
    ON table1.key1 = table2.key1
JOIN CTE_letzter
    ON CTE_letzter.key2 = table1.key2
    AND CTE_letzter.key3 = table2.key3
    AND CTE_letzter.max_zeit = table1.zeitspalte
GROUP BY table1.key2, table2.key3, table1.zeitspalte;

In der CTE wird jetzt der jewei­lige maxi­male Zeit­wert mit Hilfe der MAX-Funk­tion berech­net. Der Fil­ter auf die Zeit­spalte wird hier mit einem Join umge­setzt. Das letzte GROUP BY ist nötig, um Dupli­kate zu ver­mei­den, wenn MAX(table1.zeitspalte) nicht ein­deu­tig ist. Auf­grund die­ser Aggre­ga­tion ist in der ers­ten Zeile ein FIRST_VALUE() nötig. Für diese Query muss Exa­sol keine Sor­tier­ope­ra­tion aus­füh­ren, was die Per­for­mance stark verbessert.

Teil2: Best Practices

Damit Exa­sol opti­mal genutzt wer­den kann und effi­zi­en­tes Arbei­ten ermög­licht wird, wer­den in die­sem Kapi­tel einige erfolgs­ver­spre­chende Metho­den und Tipps beschrieben.

Tem­po­räre Tabellen

Tem­po­räre Tabel­len kön­nen in Exa­sol nicht manu­ell erstellt wer­den. Aber es ist mög­lich, durch die Deak­ti­vie­rung des auto­com­mits Tabel­len zu drop­pen bevor ein com­mit aus­ge­führt wird. Diese Tabel­len wer­den dann nicht von ande­ren Trans­ak­tio­nen gese­hen und nicht auf die Disk (Fest­platte) geschrieben.

Sehr prak­tisch für ETL-Jobs ist fol­gen­des Vorgehen:

1. Schreibe Daten in eine tem­po­räre Tabelle (Mate­ria­li­sie­rung).
2. Arbeite damit.
3. Droppe die Tabelle und commite

Bei die­sem Vor­ge­hen ist es kein Pro­blem, wenn andere Ses­si­ons den­sel­ben Tabel­len­na­men verwenden.

Noch bes­ser ist es, die tem­po­räre Tabelle als View zu defi­nie­ren. Dann kann Exa­sols Opti­mi­zer Fil­ter frü­her anwen­den and Spal­ten eli­mi­nie­ren. Es kann also bes­ser opti­miert wer­den. (Ein spä­te­res Update auf die Inhalte der View geht dann natür­lich nicht mehr.)

Das Erzeu­gen meh­re­rer Views mit ver­rück­ten zufäl­li­gen Namen wird Sche­ma­ver­schmut­zung genannt und sollte ver­mie­den wer­den. Bei akti­vier­tem auto­com­mit soll­ten bes­ser CTEs anstatt Views ver­wen­det werden.

Umschrei­ben von LIKE-Prädikaten

Das LIKE-Schlüs­sel­wort ist opti­miert für den Abgleich mit kon­stan­ten Mus­tern, also z. B. LIKE ‚%-%‘. Kon­ka­te­na­tion (Ver­ket­tun­gen) mit Spal­ten­na­men sind nicht so per­for­mant. Wenn man z. B. prü­fen oder fil­tern will, ob der Inhalt einer Spalte eines Daten­sat­zes einen zuge­hö­ri­gen Stadt­na­men ent­hält, sollte nicht

LIKE '%' || c.city_name || '%'

ver­wen­det wer­den. Es ist bes­ser STRING-Funk­tio­nen zu benut­zen. Dafür nütz­li­che Funk­tio­nen sind:

• INSTR:  Nimmt zwei Strings als Argu­ment und gibt zurück, an wel­cher Posi­tion der zweite String im Ers­ten ent­hal­ten ist.
• SUBSTR: Gibt abhän­gig von den Argu­men­ten bestimmte Teile eines Strings zurück (z. B. Zei­chen 3 bis 6 oder die letz­ten 5 Zeichen).
• LENGTH: Gibt die Länge eines Strings zurück.

Ver­meide teure Operationen

Für die beste Per­for­mance sollte man dar­auf ach­ten, wenn mög­lich, teure Ope­ra­tio­nen zu ver­mei­den. Im Fol­gen­den wer­den teure Ope­ra­tio­nen auf­ge­lis­tet und es wird z. T. erklärt, wie sie ver­mie­den wer­den können:

• Index­er­stel­lung
• Index-Neu­bau (z. B. nach einer Reor­ga­ni­sa­tion einer Tabelle nach einer Clus­ter­ver­grö­ße­rung oder wenn >25% als gelöscht mar­kiert wur­den oder es ein Update auf eine dis­tri­bu­tion-key-Spalte gab)
  • Des­halb sollte man Updates auf dis­tri­bu­tion-key-Spal­ten vermeiden.
• BET­WEEN-Joins (BETWEEN in der Join-Bedingung) 
  • Am schnells­ten sind Joins mit Equal-Bedin­gun­gen (tab1.x = tab2.x)
• Glo­bale Joins 
  • Diese kön­nen ver­hin­dert wer­den, indem man kleine Tabel­len­grö­ßen ver­wen­det, dis­tri­bu­tion keys benutzt oder die Repli­zie­rungs­grenze (repli­ca­tion bor­der) ver­grö­ßert. In Exa­sol wer­den Tabel­len, die weni­ger Zei­len als die Repli­zie­rungs­grenze haben, auf allen Dan­te­bank­kno­ten repliziert.
• Sor­tie­run­gen
  • Siehe oben.
• UNION
  • Siehe oben.
• REORGANIZE
• Cons­traint-Checks, z. B. Pri­mär­schlüs­sel und NOT-NULL-Restrik­tio­nen für Spalten 
  • Da es hierzu eini­ges zu sagen gibt, folgt ein eige­ner Abschnitt zu Constraint-Checks

Tipps zum Umgang mit Constraints

Die Rechen­kos­ten stei­gen mit der Menge der impor­tier­ten Daten und der Menge der vor­han­de­nen Daten. Es ist Best Prac­tice Cons­traints in Tabel­len zu deak­ti­vie­ren, damit sie nicht bei jedem ein­zel­nen INSERT und UPDATE geprüft wer­den müssen.

Code­bei­spiel:

CITY_ID INTEGER PRIMARY KEY DISABLE;

anstatt

CITY_ID INTEGER PRIMARY KEY;

Befehle, um Cons­traint-Bedin­gun­gen zu tes­ten, sind

ALTER TABLE cities MODIFY PRIMARY KEY ENABLE;

und

SELECT * WITH INVALID PRIMARY KEY(city_id) FROM cities;

Das erste State­ment wirft einen Feh­ler, wenn es eine PRI­MARY-KEY-Ver­let­zung gibt, (Dupli­kate oder NULL-Werte) und das zweite State­ment gibt direkt die ungültigen/fehlerhaften Zei­len zurück.

Ana­log zur Prü­fung der PRI­MARY-KEY-Bedin­gung gibt es Prü­fun­gen auf FOREIGN KEYs und UNIQUEness:

SELECT * WITH INVALID FOREIGN KEY(city_id) FROM markets REFERENCING cities(city_id);
SELECT * WITH INVALID UNIQUE(city_id) FROM cities;

Am bes­ten kön­nen die Checks am Ende des ETL-Jobs gemacht wer­den. Es ist bil­li­ger die Cons­traint Checks alle gesam­melt zu machen anstatt nach jedem ein­zel­nen INSERT und UPDATE.

ETL

Je nach Daten­bank und Ver­bin­dungs­me­thode wer­den ETL-Pro­zesse seri­ell oder par­al­lel aus­ge­führt. Für eine Ora­cle-Daten­bank ist bei­spiel­weise ORA die native Schnitt­stelle. Des­halb wird fol­gen­der Code par­al­lel ausgeführt:

IMPORT INTO sales FROM ORA AT oracle_con TABLE myoracledb.sales;

Mit JDBC wird die­ser Import nicht mehr par­al­lel ausgeführt:

IMPORT INTO sales FROM jdbc AT mysql_con TABLE mysql.sales;

Statt­des­sen wird die Query an die Daten­bank gesen­det und sobald das Ergeb­nis ver­füg­bar ist, wer­den die Daten über das Netz­werk trans­fe­riert. Um hier eine Par­al­le­li­sie­rung zu errei­chen, muss die STATE­MENT-Klau­sel ver­wen­det werden:

IMPORT INTO sales FROM jdbc AT mysql_con TABLE mysql.sales
STATEMENT 'SELECT * FROM mysql.sales WHERE year(sales_date)=2018';

Es kön­nen meh­rere State­ments in einer Query benutzt werden:

IMPORT INTO sales FROM jdbc AT mysql_con TABLE mysql.sales
STATEMENT 'SELECT * FROM mysql.sales WHERE year(sales_date)=2018'
STATEMENT 'SELECT * FROM mysql.sales WHERE year(sales_date)=2017'
STATEMENT 'SELECT * FROM mysql.sales WHERE year(sales_date)=2016';

Man sollte nicht ver­su­chen, meh­rere Import-State­ments in meh­re­ren Ses­si­ons, die in die glei­che Tabelle schrei­ben, gleich­zei­tig aus­füh­ren. Denn dann gibt es Trans­ak­ti­ons­kon­flikte und Schreib-Locks. Statt­des­sen sollte man wie oben zu sehen meh­rere State­ment-Klau­seln benut­zen. Dann wer­den die Importe parallelisiert.

Par­al­le­les Laden wird auch für Dateien unterstützt.

Bei­spiel­code für eine Linux-Shell:

$ wc -l SALES.csv
100001 SALES.csv

Eine Datei wird nicht par­al­lel impor­tiert. Um eine Par­al­le­li­sie­rung zu errei­chen, sollte die Datei ser­ver­sei­tig in so viele Teile zer­teilt wer­den, wie der Clus­ter Kno­ten hat.  Der Code für das par­al­lele Ein­le­sen von vier Dateien könnte dann so aussehen:

IMPORT INTO sales FROM CSV AT ftp_con
FILE '/import/SALES.csv_part_aa'
FILE '/import/SALES.csv_part_ab'
FILE '/import/SALES.csv_part_ac'
FILE '/import/SALES.csv_part_ad';

Ein letz­ter Punkt zu ETL-Tools:

Wähle die höchst­mög­li­che batch size für bulk inserts und die höchst­mög­li­che fetch size und ver­wende bulk inserts statt sin­gle-row inserts.

Daten­bank­de­sign

Für eine gute Per­for­mance ist es wich­tig, pas­sende dis­tri­bu­ti­ons keys zu wählen.

Daten soll­ten nor­ma­li­siert gespei­chert wer­den. Nicht-nor­ma­li­sierte Daten ver­brau­chen mehr Spei­cher­platz, sowohl auf der der Fest­platte als auch im RAM.

Es sollte kein ORDER BY in Views ver­wen­det wer­den. Das würde dazu füh­ren, dass jedes Mal mate­ria­li­siert wird und so Opti­mie­rungs­po­ten­tial ver­lo­ren geht. Gene­rell soll­ten große Mate­ria­li­sie­run­gen ver­mie­den werden.

Daten­ty­pen

Ähn­li­che Daten (z. B. Preise) soll­ten den glei­chen Daten­typ haben

• Es soll­ten keine unnö­tig gro­ßen Daten­ty­pen benutzt wer­den. Zum Bei­spiel ist der Daten­typ DATE bes­ser als TIMESTAMP und kleine VAR­CHARs sind besser.
• CHARs sind bes­ser als VARCHARs.
• Fil­ter auf INTE­GERs sind ca. zehn­mal so schnell wie auf CHAR und Aggre­ga­tio­nen sind zwei- bis drei­mal schnel­ler. Auf Joins trifft dies in ähn­li­cher Weise zu.

Zah­len­ty­pen sind auch auf der Daten­bank und in Indi­zes klei­ner. Es ist also immer bes­ser nume­ri­sche Werte anstatt CHARs zu benutzen.

Bei Joins soll­ten die Join-Spal­ten der bei­den Join-Part­ner die glei­chen Daten­ty­pen haben. Ansons­ten muss Exa­sol die eine Seite der Gleich­heits-Join-Bedin­gung kon­ver­tie­ren und einen expres­sion index erzeu­gen. Diese Indi­zes wer­den auch bei ande­ren Funk­tio­nen wie CAST, einem Funk­ti­ons­auf­ruf, UPPERCASE, Kon­ka­te­na­tion und SUBSTRING erstellt. Expres­sion inde­xes wer­den nach dem Join gelöscht, so dass sie jedes Mal wie­der neu erstellt wer­den müssen.

Künst­li­che Schlüssel

Künst­li­che Schlüs­sel (sur­ro­gate keys) sind eine Mög­lich­keit Joins mit meh­re­ren Spalte zu vermeiden.

Bei einem gewöhn­li­chen Daten­mo­dell wer­den Dimen­si­ons­ta­bel­len an Fak­ten­ta­bel­len gejoint. Wenn eine Dimen­si­ons­ta­belle Gül­tig­keits­spal­ten besitzt, müs­sen in der Regel diese Gül­tig­kei­ten in der Join-Bedin­gung geprüft wer­den, was Per­for­man­ce­ein­bu­ßen mit sich bringt. In den meis­ten Fäl­len sind die Join-Part­ner von Fak­ten- und Dimen­si­ons­ta­belle unver­än­der­lich, so dass es sinn­voll ist, die Zuord­nung ein­mal zu berech­nen und abzu­spei­chern und so die Join-Per­for­mance zu ver­bes­sern. Hier kom­men die künst­li­chen Schlüs­sel ins Spiel. Der Dimen­si­ons­ta­belle wird eine ID-Spalte gege­ben, die für die Tabelle ein­deu­tig ist und die Rolle des künst­li­chen Schlüs­sels ein­nimmt. Das geht z. B. mit dem Befehl

ALTER TABLE [Dimensionstabellenname] ADD COLUMN [Spaltenname des künstlichen Schlüssels] INT IDENTITY;

Die neue Spalte muss nicht manu­ell befüllt wer­den. Die IDEN­TITY-Option sorgt für eine auto­ma­ti­sche Befül­lung mit auf­stei­gen­den INTEGER-Werten.

Dann muss die neue Schlüs­sel­spalte noch an die Fak­ten­ta­belle gehängt und mit einem MERGE-Befehl befüllt werden.

Fazit

Mit Hilfe der Opti­mie­rungs­tipps und Best Prac­ti­ces aus die­sem Blog­bei­trag kön­nen Sie Exa­sol noch per­for­man­ter benut­zen und effek­ti­ver damit arbei­ten. Viel Spaß bei der Ver­wen­dung von Exasol!

Quelle: Exa­sol Per­for­mance Manage­ment auf https://exacademy.exasol.com/