Optimierung durch Parameter und Prepared statements

[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.