Warum SQL Bind-Variablen für die Performance wichtig sind

Dieser Artikel wurde zuerst auf jooq.org veröffentlicht, einem Blog, das sich mit allen Fragen rund um Open Source, Java and Software-Entwicklung aus der Perspektive von jOOQ befasst.

Ein häufiges Problem mit dynamischem SQL ist das Parsen der Performance in der Produktion. Was die Sache noch schlimmer macht, ist, dass viele Entwickler keinen Zugang zu Produktionsumgebungen haben, sodass sie sich des Problems nicht bewusst sind (auch wenn es an diesem Thema nichts Neues gibt). Was genau ist das Problem?

Ausführungsplan-Caches

Die meisten Datenbankanbieter verfügen heutzutage über einen Ausführungsplan-Cache (Oracle nennt ihn Cursor-Cache), in dem zuvor analysierte SQL-Anweisungen gespeichert sind und deren Ausführungsplan (und -pläne) zur Wiederverwendung zwischengespeichert werden. Dies ist der Hauptgrund, warum Bind-Variablen so wichtig sind (der andere Grund ist die Verhinderung von SQL-Injektionen). Durch die Verwendung von Bind-Variablen können wir sicherstellen, dass die Datenbank eine identische SQL-Anweisung aus einer früheren Ausführung leicht erkennt und den zuvor gefundenen Ausführungsplan erneut ausführen kann. Dies ist in der Tat eines meiner Lieblingsthemen aus meinem SQL-Training. Mal sehen, was in verschiedenen Datenbanken passiert, wenn wir die folgenden Abfragen durchführen:

-- First, run them with "inline values" or "constant literals"
SELECT first_name, last_name FROM actor WHERE actor_id = 1;
SELECT first_name, last_name FROM actor WHERE actor_id = 2;
 
-- Then, run the same queries again with bind values
SELECT first_name, last_name FROM actor WHERE actor_id = ?;
SELECT first_name, last_name FROM actor WHERE actor_id = ?;

Es spielt keine Rolle, ob die Abfragen von JDBC, jOOQ, Hibernate oder der prozeduralen Sprache in der Datenbank ausgeführt werden, z.B. PL/SQL, T-SQL, pgplsql. Das Ergebnis ist immer das gleiche.

Führen wir ein Beispiel aus

Ich werde die folgenden Beispiele nur mit Oracle ausführen. Andere Datenbanken verhalten sich ähnlich.

Wir führen zunächst ein Skript aus, das die obigen Abfragen enthält, und eine Abfrage, um alle Ausführungspläne abzurufen:

SELECT first_name, last_name FROM actor WHERE actor_id = 1;
SELECT first_name, last_name FROM actor WHERE actor_id = 2;
 
SET SERVEROUTPUT ON
DECLARE
  v_first_name actor.first_name%TYPE;
  v_last_name  actor.last_name%TYPE;
BEGIN
  FOR i IN 1 .. 2 LOOP
    SELECT first_name, last_name 
    INTO v_first_name, v_last_name
    FROM actor 
    WHERE actor_id = i;
     
    dbms_output.put_line(v_first_name || ' ' || v_last_name);
  END LOOP;
END;
/
 
SELECT s.sql_id, p.*
FROM v$sql s, TABLE (
  dbms_xplan.display_cursor (
    s.sql_id, s.child_number, 'ALLSTATS LAST'
  )
) p
WHERE lower(s.sql_text) LIKE '%actor_id = %';

Der Output ist:

SQL_ID  90rk04nhr45yz, child number 0
-------------------------------------
SELECT FIRST_NAME, LAST_NAME FROM ACTOR WHERE ACTOR_ID = :B1
 
Plan hash value: 457831946
 
---------------------------------------------------------
| Id  | Operation                   | Name     | E-Rows |
---------------------------------------------------------
|   0 | SELECT STATEMENT            |          |        |
|   1 |  TABLE ACCESS BY INDEX ROWID| ACTOR    |      1 |
|*  2 |   INDEX UNIQUE SCAN         | PK_ACTOR |      1 |
---------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("ACTOR_ID"=:B1)
 

SQL_ID  283s8m524c9rk, child number 0
-------------------------------------
SELECT first_name, last_name FROM actor WHERE actor_id = 2
 
Plan hash value: 457831946
 
---------------------------------------------------------
| Id  | Operation                   | Name     | E-Rows |
---------------------------------------------------------
|   0 | SELECT STATEMENT            |          |        |
|   1 |  TABLE ACCESS BY INDEX ROWID| ACTOR    |      1 |
|*  2 |   INDEX UNIQUE SCAN         | PK_ACTOR |      1 |
---------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("ACTOR_ID"=2)
 

SQL_ID  3mks715670mqw, child number 0
-------------------------------------
SELECT first_name, last_name FROM actor WHERE actor_id = 1
 
Plan hash value: 457831946
 
---------------------------------------------------------
| Id  | Operation                   | Name     | E-Rows |
---------------------------------------------------------
|   0 | SELECT STATEMENT            |          |        |
|   1 |  TABLE ACCESS BY INDEX ROWID| ACTOR    |      1 |
|*  2 |   INDEX UNIQUE SCAN         | PK_ACTOR |      1 |
---------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("ACTOR_ID"=1)

Die Pläne sind immer die gleichen und da wir auf Primärschlüsselwerte zugreifen, bekommen wir immer die gleichen Kardinalitäten, sodass bei jeder einzelnen Ausführung nichts falsch zu sein scheint. Aber die Prädikatinformationen sind etwas anders. Wenn man nach einem konstanten Wert fragt, dann enthält das Prädikat diesen Wert genau dort, während wir bei der Bind-Variablen nicht wissen, was der Prädikatswert ist, aus dem Plan. Dies ist durchaus zu erwarten, da wir diesen Plan für beide Ausführungen der Query wiederverwenden wollen.

Mit einer weiteren Abfrage können wir die Anzahl der Ausführungen jeder Anweisung sehen:

SELECT sql_id, sql_text, executions
FROM v$sql
WHERE sql_id IN (
  '90rk04nhr45yz', 
  '283s8m524c9rk', 
  '3mks715670mqw'
);

SQL_ID          SQL_TEXT                                                        EXECUTIONS
------------------------------------------------------------------------------------------
90rk04nhr45yz	SELECT FIRST_NAME, LAST_NAME FROM ACTOR WHERE ACTOR_ID = :B1 	2
283s8m524c9rk	SELECT first_name, last_name FROM actor WHERE actor_id = 2	1
3mks715670mqw	SELECT first_name, last_name FROM actor WHERE actor_id = 1	1

Hier wird es interessanter. Im zweiten Fall, in dem wir eine Bind-Variable verwendet haben (die von PL/SQL automatisch generiert wurde), konnten wir die Anweisung wiederverwenden, ihren Plan zwischenspeichern und zweimal ausführen.

Meh, ist das wichtig?

Es ist sogar aus zwei Gründen wichtig:

• Durchführung der einzelnen Ausführungen
• Leistung Ihres gesamten Systems

Wie sich dies auf die einzelnen Ausführungen auswirkt

Es scheint sehr offensichtlich zu sein, dass es einen leichten Overhead für die Cache-Pflege gibt, wenn man in der Lage ist, etwas zwischenzuspeichern. Jedenfalls verglichen mit dem Gewinn, die Arbeit nicht machen zu müssen, deren Ergebnis zwischengespeichert wird. Hier geht es darum, die SQL-Anweisung zu analysieren und einen Ausführungsplan dafür zu erstellen. Auch wenn der Plan trivial ist, wie in den obigen Beispielen, ist die Berechnung dieses Plans mit Gemeinkosten verbunden.

Dieser Overhead lässt sich am besten in einem Benchmark darstellen, eine Technik, die ich auch in meinem SQL-Training anwende:

SET SERVEROUTPUT ON
 
-- Don't run these on production
-- But on your development environment, this guarantees clean caches
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;
 
CREATE TABLE results (
  run     NUMBER(2),
  stmt    NUMBER(2),
  elapsed NUMBER
);
 
DECLARE
  v_ts TIMESTAMP WITH TIME ZONE;
  v_repeat CONSTANT NUMBER := 2000;
  v_first_name actor.first_name%TYPE;
  v_last_name  actor.last_name%TYPE;
BEGIN
 
  -- Repeat whole benchmark several times to avoid warmup penalty
  FOR r IN 1..5 LOOP
    v_ts := SYSTIMESTAMP;
       
    FOR i IN 1..v_repeat LOOP
      BEGIN
        EXECUTE IMMEDIATE '
          SELECT first_name, last_name 
          FROM actor 
          WHERE actor_id = ' || i 
          -- Just fixing a syntax highlighting bug of this blog '
        INTO v_first_name, v_last_name;
      EXCEPTION
        -- Please forgive me
        WHEN OTHERS THEN NULL;
      END;
    END LOOP;
   
    INSERT INTO results VALUES (
      r, 1, SYSDATE + ((SYSTIMESTAMP - v_ts) * 86400) - SYSDATE);
    v_ts := SYSTIMESTAMP;
       
    FOR i IN 1..v_repeat LOOP
      BEGIN
        EXECUTE IMMEDIATE '
          SELECT first_name, last_name 
          FROM actor 
          WHERE actor_id = :i'
        INTO v_first_name, v_last_name
        USING i;
      EXCEPTION
        -- Please forgive me
        WHEN OTHERS THEN NULL;
      END;
    END LOOP;
       
    INSERT INTO results VALUES (
      r, 2, SYSDATE + ((SYSTIMESTAMP - v_ts) * 86400) - SYSDATE);
  END LOOP;
   
  FOR rec IN (
    SELECT
      run, stmt, 
      CAST(elapsed / MIN(elapsed) OVER() AS NUMBER(10, 5)) ratio 
    FROM results
  )
  LOOP
    dbms_output.put_line('Run ' || rec.run || 
      ', Statement ' || rec.stmt || 
      ' : ' || rec.ratio);
  END LOOP;
END;
/
 
DROP TABLE results;

Wir veröffentlichen keine tatsächlichen Ausführungszeiten, um die Lizenzbeschränkungen für Benchmark-Publikationen einzuhalten, daher vergleichen wir jede Ausführung nur mit der schnellsten Ausführung. Dies ist das Ergebnis der obigen Ausführungen:

Run 1, Statement 1 : 83.39893
Run 1, Statement 2 : 1.1685
Run 2, Statement 1 : 3.02697
Run 2, Statement 2 : 1
Run 3, Statement 1 : 2.72028
Run 3, Statement 2 : 1.03996
Run 4, Statement 1 : 2.70929
Run 4, Statement 2 : 1.00866
Run 5, Statement 1 : 2.71895
Run 5, Statement 2 : 1.02198

Wir können sehen, dass die SQL-Version, die eine Bind-Variable verwendet, 2,5-mal so schnell ist wie die, die die Bind-Variable nicht verwendet. Dieser Overhead ist bei trivialen Abfragen sehr wichtig – bei komplexeren Abfragen, bei denen die Ausführung selbst mehr Zeit in Anspruch nimmt, mag das weniger wichtig sein als beim Parsen. Aber es sollte klar sein, dass der Overhead ein Preis ist, den wir nicht zahlen wollen. Wir wollen, dass die Abfrage und ihr Plan zwischengespeichert werden!

Bemerkenswert ist auch, dass die allererste Ausführung des Benchmarks einen sehr hohen Overhead hat, da alle 2000 Abfragen zum ersten Mal aufgetreten sind, bevor sie für den zweiten Lauf zwischengespeichert werden. Das ist ein Preis, den wir nur beim ersten Lauf zahlen.

Wie sich dies auf das gesamte System auswirkt

Nicht nur einzelne Abfrageausführungen leiden, sondern auch Ihr gesamtes System. Nachdem ich den Benchmark einige Male ausgeführt habe, sind dies die Ausführungsstatistiken, die ich aus dem Oracle-Cursor-Cache erhalte:

SELECT
  count(*), 
  avg(executions), 
  min(executions), 
  max(executions)
FROM v$sql
WHERE lower(sql_text) LIKE '%actor_id = %'
AND sql_text NOT LIKE '%v$sql%';

Ergibt:

count  avg      min  max
2001   9.9950   5    10000

Momentan befinden sich 2000 Abfragen in meinem Cache. Diejenige, die 10000 Mal ausgeführt wurde (Benchmark wurde 5 Mal wiederholt und es gab 2000 Ausführungen der Query pro Lauf), und 2000 Queries, die 5 Mal ausgeführt wurde (Benchmark wurde 5 Mal wiederholt).

Wenn wir stattdessen die Abfrage 20000 Mal ausführen (und nicht vergessen, dass die Abfrage der gefilterten ACTOR_ID entspricht), dann wird das Ergebnis ganz anders aussehen!

Run 1, Statement 1 : 86.85862
Run 1, Statement 2 : 1.13546
Run 2, Statement 1 : 78.39842
Run 2, Statement 2 : 1.01298
Run 3, Statement 1 : 72.45254
Run 3, Statement 2 : 1
Run 4, Statement 1 : 73.78357
Run 4, Statement 2 : 2.24365
Run 5, Statement 1 : 84.89842
Run 5, Statement 2 : 1.143

Oh mein Gott! Was ist passiert? Überprüfen wir noch einmal die Cursor-Cache-Statistiken:

SELECT
  count(*), 
  avg(executions), 
  min(executions), 
  max(executions)
FROM v$sql
WHERE lower(sql_text) LIKE '%actor_id = %'
AND sql_text NOT LIKE '%v$sql%';

Ergibt:

count  avg      min  max
15738  3.4144   1    20000

Das ist ein ganz anderes Ergebnis. Wir haben nicht alle unsere 20000 Abfragen im Cursor-Cache, nur einige davon. Dies bedeutet, dass einige Anweisungen aus dem Cache gelöscht wurden, um Platz für neue zu schaffen (was für jeden Cache vernünftig ist).

Aber sie zu bereinigen ist auch problematisch, denn so wie der Benchmark entworfen wurde, werden sie im zweiten, dritten, vierten und fünften Lauf wieder auftauchen, also hätten wir sie im Cache behalten sollen. Und da wir jede Abfrage gleich oft ausführen, gab es wirklich keine Möglichkeit, eine „vernünftigere“ (d.h. seltenere) Abfrage zu identifizieren.

Ressourcen in einem System sind immer begrenzt, ebenso wie die Größe des Cursor-Cache. Je mehr verschiedene Abfragen wir in einem System ausführen, desto weniger können sie vom Cursor-Cache profitieren.

Dies ist kein Problem für selten ausgeführte Abfragen, einschließlich Berichte, Analysen oder einige spezielle Abfragen, die nur von sehr wenigen Benutzern ausgeführt werden. Aber die Anfragen, die ständig ausgeführt werden, sollten immer zwischengespeichert werden.

Ich kann nicht genug betonen, wie ernst das sein kann

Im obigen Fall explodierte eine einzelne Abfrage im Cursor-Cache in 20000 Abfragen, wodurch viele weitere nützliche Abfragen aus dem Cache geschoben wurden. Dadurch wird nicht nur die Ausführung dieser speziellen Abfrage verlangsamt, sondern es werden auch Tonnen von völlig unabhängigen Abfragen aus dem Cache entfernt, wodurch das gesamte System um ähnliche Faktoren verlangsamt wird. Wenn jeder drastisch verlangsamt wird, beginnt jeder, sich in die Warteschlange zu stellen, um seine SQL-Abfragen analysieren zu lassen, und das kann (im schlimmsten Fall) den gesamten Server zum Absturz bringen!

Workaround

Einige Datenbanken unterstützen das Parsen der konstanten Literale, um Variablen zu binden. In Oracle können CURSOR_SHARING = FORCE als „Quick Fix“ angegeben werden. Im SQL Server nennt man das forced parametrization.

Dieser Ansatz hat jedoch seine eigenen Grenzen und seinen eigenen Overhead, da dies bedeutet, dass jedes Mal zusätzliche Parsing-Arbeiten durchgeführt werden müssen, um konstante Literale zu erkennen und durch Bind-Variablen zu ersetzen. Dieser Overhead gilt dann für alle Anfragen!

W-JAX – Die Konferenz für Java, Architektur- und Software-Innovation

Workshop: Java Core Library Deep Dive

mit Arno Haase | Freiberufler

JUnit 5 Workshop: Mehr Spaß und weniger Bauchschmerzen beim Entwickeln durch clevere Tests

mit Michael Inden | ASMIQ AG

Die neuen Hybrid-Trainingevents mit mehr als 25 Trainer*innen und rund 30 intensiven Workshops

Progressive Web Apps mit Angular: Das Web wird nativ(er)

mit Christian Liebel | Thinktecture AG

Workshop: Angular-Apps mit der Ivy-Engine (Deep-dive)

mit Martina Kraus | Freiberufler

Fazit

Bind-Variablen sind sehr wichtig für die SQL Performance. Nach diversen Schulungen für die Verwendung von SQL Injection Reasons (was bereits ein Fortschritt ist), haben wir jetzt gesehen, wie wichtig sie auch aus Performance-Gründen sind.

Wenn man keine Bind-Variable für Werte wie IDs, Zeitstempel, Namen oder alles, was gleichmäßig verteilt ist und viele Werte in Ihrer Spalte hat, verwendet, wird das obige Problem auftreten. Die Ausnahme sind Bind-Variablen für Spalten mit nur sehr wenigen eindeutigen Werten (wie True/False-Flags, Codes, die einen bestimmten Zustand kodieren, usw.), bei denen ein konstantes Literal eine sinnvolle Option sein kann.

Aber Bind-Variablen sollten immer die erste Wahl sein. Wenn ein Client-seitiges Tool wie jOOQ oder Hibernate verwendt wird, sind Bind-Variablen in der Regel die Standardeinstellung, und alles ist gut. Wenn eine gespeicherte prozedurale Sprache wie PL/SQL oder T-SQL verwendet wird, werden Bind-Variablen automatisch generiert und es ist ebenfalls alles gut. Aber wenn JDBC oder einen JDBC-Wrapper wie Spring’s JdbcTemplates oder eine andere String-basierte API wie JPA’s native Abfrage-API zum Einsatz kommen, dann muss man sich bei jeder Variableneingabe explizit um die Verwendung von Bind-Variablen kümmern.