11. Februar 2008

Geht es auch schneller? Gedanken und Tipps zum SQL Tuning - für Entwickler

English title: Can it be faster? Some thoughts on optimizing SQL - for developers!

Heute und in den folgenden Ausgaben möchte ich mich dem Thema effizientes SQL widmen - und zwar aus der Sicht desjenigen, der das SQL in die Anwendungen einbaut: des Entwicklers!
Zu Beginn also etwas zum Thema Bindevariablen: Interessant ist ja, dass eigentlich nur die "Datenbankleute" einen Unterschied zwischen statischem und dynamischem SQL machen. Für den JDBC-Entwickler gibt es diesen Unterschied gar nicht; dort wird das SQL immer per Stringverkettung zusammengesetzt und dann an die Datenbank übergeben (executeQuery(), prepareStatement()). Eine Ausnahme ist SQLJ, aber das wird (soweit ich es beobachten kann) kaum genutzt.
Es kommt also darauf an, wie man das SQL zusammensetzt und vor allem, wie man dabei mit den Literalen umgeht:
  • Entweder verkettet man sie "in das SQL" hinein ... dann wird das SQL vom Entwickler fertig zusammengesetzt und an die Datenbank übergeben - die führt es sofort aus.
    select * from sh.sales where cust_id = 4711
  • Oder man verwendet Bind-Variablen (in der Java-Welt auch PreparedStatements genannt). In diesem Fall verwendet man zunächst Platzhalter für die Literale ...
    select * from sh.sales where cust_id = ?
    ... oder ...
    select * from sh.sales where cust_id = :1
    ... und übergibt das SQL ohne Parameter an die Datenbank. Im nächsten Schritt werden die Platzhalter dann mit Werten belegt, dann führt die Datenbank das Statement aus.
Der Unterschied zwischen den beiden Varianten ist erheblich, wie das folgende kleine Java-Programm zeigt ...
This and the following posts are dedicated to efficient SQL - and this from the developer's (and not the DBA's) point of view. Many SQL tuning papers are written from the DBA's point of view - but it's the developer which embeds SQL into his application.
Let's start with bind variables: It's interesting that only database guys make a difference between static and dynamic SQL. The Java/JDBC developer does not care about this - every SQL is concatenated as a string and then passed to the database via executeQuery() or prepareStatement. An exception is SQLJ but as of my experience this is used rarely.
So the question ist how to concatenate the SQL and particularly how to handle the literals:
  • The literals can be built directly into the SQL statement which is then passed to the database for execution.
    select * from sh.sales where cust_id = 4711
  • The other option is to use variables for the literals ...
    select * from sh.sales where cust_id = ?
    ... or ...
    select * from sh.sales where cust_id = :1
    . So the statement passed to the database contains just the variables - the binding to the actual values is the next step - finally the statement gets executed by the database.
The chosen option has a huge performance impact, as the following little java program shows ...
import java.sql.*;

public class Bindtest {
  public static void main(String args[]) throws Exception {
    Connection        con = null;
    Statement         stmt = null;
    PreparedStatement pstmt = null;
    ResultSet         rs = null;

    long lStartTime;
    int  iCount = (Integer.parseInt(args[0]));

    String sSql = "";

    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
    con = DriverManager.getConnection("jdbc:oracle:thin:@192.168.2.140:1521:orcl", "sh", "sh");
    con.setAutoCommit(false);

    System.out.println("1. Test: Stringverkettung");
    System.out.println("**************************");
   
    lStartTime = System.currentTimeMillis();
    stmt = con.createStatement();
    for (int i=0;i<iCount;i++) {
      sSql = "select * from sh.sales where cust_id = " + ((int)(Math.random() * iCount));
      rs = stmt.executeQuery(sSql);
      while (rs.next()) {
        // do nothing
      }
      if (i % ((int)(iCount / 80)) == 0) {
        System.out.print(".");
      }
      rs.close();
    }
    stmt.close();
    System.out.println();
    System.out.println("Zeit: " + (System.currentTimeMillis() - lStartTime) + "ms.");

    System.out.println("2. Test: Bindevariablen");
    System.out.println("************************");

    lStartTime = System.currentTimeMillis();
    pstmt = con.prepareStatement("select * from sh.sales where cust_id = ?");
    for (int i=0;i<iCount;i++) {
      pstmt.setInt(1, ((int)(Math.random() * iCount)));
      rs = pstmt.executeQuery();
      while (rs.next()) {
        // do nothing
      }
      if (i % ((int)(iCount / 80)) == 0) {
        System.out.print(".");
      }
      rs.close();
    }
    pstmt.close();
    System.out.println();
    System.out.println("Zeit: " + (System.currentTimeMillis() - lStartTime) + "ms.");
    con.close();
  }
}
    
 
Das Programm zeigt den Performance-Unterschied zwischen SQL-Abfragen mit Bindevariablen und einfacher Stringverkettung, indem einzele Zeilen aus der Tabelle SH.SALES wiederholt abgerufen werden. Dabei wird massiv SQL an die Datenbank abgesetzt. Ihr müsst den Connection String an eure Datenbank anpassen, um das Programm nutzen zu können; außerdem muss das SH-Schema vorhanden sein. Das Programm muss mit einem Parameter aufgerufen werden; dieser gibt an, wie oft die Tabelle SH.SALES abgefragt werden soll.
The java program shows the performance difference between SQL queries using bind variables and simple string concatenation. For this it queries single rows from the table SH.SALES. Before running this program make sure that the JDBC connection string matches your database and the username and password (here both sh) are correct. The Oracle sample schema SH must be present. The main method takes one argument - how often the SH.SALES table should be queried.
$ java Bindtest 5000

1. Test: Stringverkettung / Simple String concatenation
********************************************************
.................................................................................
Zeit: 38326ms.

2. Test: Bindevariablen / Bind variables
*****************************************
.................................................................................
Zeit: 18186ms.
Die Ergebnisse zeigen sehr deutlich, was die Bindevariablen bringen; in diesem Beispiel ist es Faktor 3. Das liegt einfach daran, dass im Falle der einfachen Stringverkettung jedes SQL-Kommando für die Datenbank neu ist - und daher führt die Datenbank für jedes SQL ein Parsing durch; sie prüft also, ob die Tabellen und Spalten vorhanden sind, ob die Zugriffsrechte OK sind und schließlich erstellt sie einen Ausführungsplan (welcher immer gleich aussehen dürfte). Das kostet für das einzelne Kommando nicht viel, aber es kostet etwas - und bei Last im Mehrbenutzerumfeld macht sich dieser Unterschied auch bemerkbar.
Man sieht hieran sehr schön, dass Bindevariablen (oder PreparedStatements) nicht zum Thema "schönerer Code", sondern zur Pflicht beim Umgang mit der Datenbank gehören (und das besonders, wenn die Maschine unter Last steht).
Und das gilt für alle Programmiersprachen; Java ist hier lediglich beispielhaft aufgeführt. Ihr erreicht die gleichen Ergebnisse mit C/C++, PHP, Perl, sogar mit PL/SQL und EXECUTE IMMEDIATE.
Those results illustrate the effect of using bind variables (in this example it's factor 3). The reason is that for the simple string concatenation every SQL is new for the database - so the database does a parsing for every singe SQL; it checks whether the tables and columns are present, whether the user has access privileges and finally it creates the execution plan (which probably looks the same for each SQL call). A single parse call is not very expensive but in a multi user scenario with high load on the machine this will get significant. So this example shows that using bind variables is not just beautifying code, it's essential for good query performance while the database is under load.
And this is true for all programming environments - java was used just as an example here. C/C++, PHP or Perl will deliver the same results - this is even true for PL/SQL and EXECUTE IMMEDIATE.

1 Kommentar:

Joachim hat gesagt…

Hi Carsten,

danke für deinen aufschlußreichen Vergleich zum Aufbau der Datenbank-Statements mittels .

Dass die Performance beim Nutzen der PreparedStatement besser ist, hatte ich mir schon gedacht, da dies einem in verschiedenen Foren immer wieder gesagt wird. Der Unterschied ist aber echt enorm. Hatte ich mir kleiner vorgestellt.

Danke für das Codebeispiel und den Vergleich. Hätte ich eigentlich auch schon längst mal ausprobieren können. ;-)

Freue mich schon auf die nächsten Artikel zum Thema SQL-Tuning.

Beliebte Postings