Optimierung durch Parameter und Prepared statements

[schlecki]

Zitat von idefix2:

Mein Schluss daraus: Bei sehr umfangreichen Schleifen sind prepared Statements in Verbindung mit Parametern sinnvoll, aber sonst zahlt sich der Mehraufwand aber kaum aus, wenn man keine SQL-Injections befürchten muss. Nur Parameter zu verwenden anstatt direkter SQL Statements bringt überhaupt keinen Performancegewinn.

Nochmal ein anderer Aspekt: Ich weiß, das es einige DBMS gibt, die die Statements cachen und wiederverwenden. Das bedeutet, wenn du Parameter verwendest, erkennt der Server das als gleiches Statemenent und kann auf die bereits erstellten Zugriffspläne zurückgreifen. Er muß hier also nicht mehr tätig werden und diese neue berechnen. In größeren Datenbanken, die von mehreren Benutzern angesprochen werden, kann das doch einiges ausmachen. Auf jeden Fall nutzt Oracle ein solches Konstrukt, beim FB bin ich mir nicht ganz sicher.

[mkinzler]

Auch bei FireBird ist das so, wie oben auch schon öfters erwähnt wurde

[Bernhard Geyer]

Zitat von schlecki:

Nochmal ein anderer Aspekt: Ich weiß, das es einige DBMS gibt, die die Statements cachen und wiederverwenden. ... Auf jeden Fall nutzt Oracle ein solches Konstrukt

Wirklich? Haben früher sowas nicht bemerkt.
Wir selbst verwenden einen clientseitigen (selbst implementierten) Query Cache um die letzten 20 Statements zu cachen. Damit erreichen wird das i.d.R. 95% der Abfragen schon prepared Statements verwenden können.

[hoika]

Hallo,

minzler, was schlecki meint, ist, dass das Oracle automatisch macht,
d.h. ich erzeuge eine Query mit Parametern, benutze sie und gebe sie frei.
Benutzt jetzt eine andere Anwendung genau den gleichen Query-SQL-Text,
hat Oracle den Ausführungsplan vielleicht noch in seinen Query-Cache (auf dem Server slebst) und benutzt ihn.

D.h. die Anwendung muss gar nichts tun (halt Parameter verwenden ).

FB kann das AFAIK noch nicht.


Heiko

[mkinzler]

Bei FB kommt es hierbei darauf an, welche Version verwendet wird. Bei Classic könnte es gehen

[idefix2]

Zitat:

Hast du den Rechner auch nach jedem Test neu gestartet und hast die DB neu erzeugt ?

Nein, sondern vor jedem Lauf die gleiche DB mit leeren Tabellen zurückkopiert
Den Rechner nach jedem Test habe ich nicht neu gestartet, aber jeden Test mehrfach ausgeführt, mit auf die Sekunde genau gleichem Ergebnis bei jedem Durchlauf, es hat also das, was vorher passiert ist, keinen Einfluss.

Ich habe mir jetzt noch das Zeitverhalten angeschaut, wenn ich aus meinen FBExec Prozeduren Dummy-Prozeduren mache (1. Befehl: Exit). Das Programm braucht ohne SQL Ausführung genau 22 Sekunden, und zwar in jeder Variante, zum Einlesen der Daten und Vorbereiten der Parameter bzw. des SQL Strings (inkl. der 100.000 Processmessages, die sind nur marginal am Ergebnis beteiligt). D.h. heisst, der Zeitaufwand nur für die eigentliche SQL Abarbeitung ist in allen Fällen um 22 Sekunden weniger.

Zitat:

Bei meinen Anwendungen (10/20er Schleifen war es etwa 50% schneller),
auserdem belastet es bei mehreren Usern den Server nicht so sehr.

Das verblüfft mich jetzt ein bisschen, weil ich habe eine einzige 100.000er Schleife und es wird nur ca 30% schneller. Allerdings kommt bei mir bei jedem Insert zusätzlich ein Insert-Trigger zum Tragen (nur die Update-Trigger habe ich bei dem Import deaktiviert), dadurch wird das, was nach dem Absetzen in der Datenbank passiert, im Verhältnis zu Delphiprogramm etwas komplexer, vielleicht deshalb der geringere Zeitgewinn. Weil natürlich ist auch bei 20er Schleifen der Zeitanteil, den das Prepare gegenüber dem Exec benötigt, schon sehr gering, ob 20 oder 100.000 Wiederholungen, macht also gar keinen so grossen Unterschied mehr.

Was mich an meinem Testergebnis aber vor allem überrascht hat, ist, dass ohne Prepare und wiederholtes Ausführen die direkte Datenübergabe im SQL String und die Datenübergabe via Parameter genau gleich schnell sind.

[idefix2]

Was ich jetzt noch festgestellt habe: die Wahl der "richtigen" Transaktionsgrösse ist ganz entscheidend für die Geschwindigkeit, und mein erster Ansatz (500 Zeilen pro Transaktion einlesen" war ziemlich weit daneben. Wenn ich statt nach 500 Zeilen erst nach 15000 Zeilen (ca 36000 Inserts) ein Commit mit anschliessendem neuen Transaktionsstart mache, braucht das Programm weniger als die Hälfte der Zeit, nämlich 2:51 in Verbindung mit prepared Statements statt knapp 6 Minuten. Ein weiteres Vergrössern der Transaktion verlangsamt allerdings das Programm wieder. Die optimale Transaktionsgrösse wird vermutlich von einer Menge Faktoren abhängen und nicht leicht zu bestimmen sein, sie ist aber jedenfalls sehr viel grösser, als ich gedacht hatte.

@mkinzler
Das Ergebnis des Tests legt nahe, dass Firebird keinen Statement-Cache verwaltet, um sich das wiederholte Ausführen von prepare zu sparen. In meiner Einleseschleife gibt es 4 SQL Statements, die der Reihe nach aufgerufen werden. Würde Firebird mit einem Statement Cache arbeiten, dann müsste die Variante mit execute immediate mit Parametern annähernd so schnell sein wie die mit manuellem Prepare, die 4 Statements wären ja dann nach der ersten importierten Zeile schon vorbereitet im Cache. Tatsächlich ist diese Variante aber ebenso langsam wie die Variante execute immediate ohne Parameter, bei der jedes Statement anders aussieht und ein Cache deshalb keinen Vorteil bringen würde.