Querystring präparieren

[Schokohase]

Ich weiß nicht ob es jemandem aufgefallen ist:

Mein Ansatz zielt darauf ab die Aufrufe in einem Rutsch an den Server zu schicken, aber trotzdem mit Parametern.

Es wird also in einem Aufruf dieses Statement abgearbeitet:

SQL-Code:

EXECUTE PROCEDURE MY_PROC(:p1_0,:p2_0,:p3_0);
EXECUTE PROCEDURE MY_PROC(:p1_1,:p2_1,:p3_1);
EXECUTE PROCEDURE MY_PROC(:p1_2,:p2_2,:p3_2);
EXECUTE PROCEDURE MY_PROC(:p1_3,:p2_3,:p3_3);
EXECUTE PROCEDURE MY_PROC(:p1_4,:p2_4,:p3_4);
...
EXECUTE PROCEDURE MY_PROC(:p1_n,:p2_n,:p3_n);

und nicht n mal
EXECUTE PROCEDURE MY_PROC(:p1,:p2,:p3);

[Codehunter]

Könnte es evtl. sein dass ihr übersehen habt, dass es hier nicht um FireDAC sondern um FIBplus geht? Da unterstützt TFIBQuery keine Multiline-Queries und TpFIBScripter keine benannten Parameter. Insofern endet das Ausprobieren in einem "Invalid token...".

Firebird 2.5 unterstützt nur recht kleine Blöcke, sodass ich bei dynamisch berechneter Blockgröße in diesem Fall zwischen 50 und 90 Queries in einem Rutsch absetzen kann. Ich habs jetzt testweise mal ohne "Injection-Schutz" mit einem schnöden Format() implementiert. Ggü. der vorigen Implementierung mit dem zeilenweisen Aufrufen der Storedproc ist die Laufzeit von knapp 21 Minuten auf 21 Sekunden gesunken (bitte keine Witze dazu, ich habe weder die Storedproc noch das DB-Design zu verantworten). Das korrelliert insofern mit der Anzahl Zeilen je EXECUTE BLOCK.

Am Rande bemerkt empfinde ich die Einschränkungen von Firebird ggü. z.B. MariaDB wie eine Eisenkugel am Bein. MariaDB/MySQL kann Multi-Row-INSERT, was mir hier den Aufruf der Storedproc erspart hätte. Gut, FB 2.5 ist schon älter, aber in 3.0 wurde es ja auch kaum besser. Gibts da chon Erfahrungen mit der 4.0?

[jobo]

Zitat von Codehunter:

.. ist die Laufzeit von knapp 21 Minuten auf 21 Sekunden gesunken (bitte keine Witze dazu, ich habe weder die Storedproc noch das DB-Design zu verantworten).

Ich mache nie Witze!

Die Laufzeitänderung überrascht mich nun doch sehr, mit so einem starken Unterschied hätte ich nicht gerechnet. Nicht jedenfalls bei unter 100 Aufrufen.

Irgendwie scheint da noch was anderes im Spiel zu sein.

[Codehunter]

Zitat von jobo:

Die Laufzeitänderung überrascht mich nun doch sehr, mit so einem starken Unterschied hätte ich nicht gerechnet. Nicht jedenfalls bei unter 100 Aufrufen.

Irgendwie scheint da noch was anderes im Spiel zu sein.

Auf jeden Fall. Zum Beispiel wurde konstruktionsbedingt nicht nur je Aufruf der Storedproc ein TFIBStoredProc-Objekt erzeugt und freigegeben sondern auch noch jeweils eine Transaktion gestartet und Committed. Zudem läuft der Client in einer VM, worunter die Netzwerkperformance zum DB-Server insbesondere bei vielen kleinen Paketen erfahrungsgemäß leidet. Typischer Fall von gewachsenen Strukturen. Aber aus einem Eselkarren macht man im Handstreich eben keinen Sportwagen sondern bestenfalls eine Postkutsche

[EgonHugeist]

Hallo Cody,

schau da mal rein: https://zeoslib.sourceforge.io/viewt...?f=50&t=129484 ich habe for Zeos8 BatchArray bindings auf dem Component-Layer hinzugefügt.. Vielleicht interessiert's dich ja.

[Codehunter]

Zitat von EgonHugeist:

Hallo Cody,

schau da mal rein: https://zeoslib.sourceforge.io/viewt...?f=50&t=129484 ich habe for Zeos8 BatchArray bindings auf dem Component-Layer hinzugefügt.. Vielleicht interessiert's dich ja.

Das ist jetzt ein seltsamer Zufall. Obwohl das Thema schon eine Weile zurück liegt, habe ich just gestern Abend das Projekt von damals wieder ausgebuddelt. Zwischenzeitlich habe ich in anderen Projekten ebenfalls an dem Problem gearbeitet.

Grundsätzlich ist Firebird 2.5 nicht unbedingt ein Glanzstück was Performance angeht. Einmal scheint der Netzwerk-Stack nicht die Wurst vom Brot zu ziehen und andererseits nutzt die alte Architektur moderne CPUs nicht allzu effizient. Daneben können Queries auch nicht beliebig groß werden sondern sind IMHO auf 1024 Bytes begrenzt. In FB 3.0 wurde das auch nur minimal angehoben und erst in der kommenden 4.0 soll es dann endlich auf "unbegrenzt" große Queries gebracht werden. Kannst du mit ZEOS eigentlich schon die aktuelle FB 4.0 konnektieren?

Anhand deines Beispiels aus dem Link: Query.Params[0].AsBooleans[1] := True; könntest du evtl. mal ein Beispiel-Query zeigen, wie das anzuwenden ist?

[Frickler]

Zitat von Codehunter:

Daneben können Queries auch nicht beliebig groß werden sondern sind IMHO auf 1024 Bytes begrenzt. In FB 3.0 wurde das auch nur minimal angehoben und erst in der kommenden 4.0 soll es dann endlich auf "unbegrenzt" große Queries gebracht werden.

Ein bisschen mehr ist es schon: http://www.firebirdfaq.org/faq197/

[Codehunter]

Bei FB 3.0 bin ich mir nicht ganz sicher, aber bei der 2.5 schon. Ich kann mich noch gut erinnern, ich hatte das mit den 64kB damals auch gelesen und den FIBScripter entsprechend auf 32kB (50% "Luft" für UTF-8) eingestellt. Dann locker fluffig einen Stapel INSERT-Queries rein gepackt und... Exception.

Ich habe dann die Blockgröße immer weiter reduziert. Reproduzierbar stabil lief das erst ab ca. 2000 Bytes. Mit "Luft" waren es dann 1024 Bytes. Womit sich der Benefit des FIBScripters gegen Null bewegte. Ob das jetzt eine Limitierung der FIBtools oder Firebird selbst ist, da bin ich überfragt.

Bei besonders breiten Tabellen kann es sogar passieren, dass nicht mal ein vollständiges INSERT in einen Query passt. Dann muss das aufgeteilt werden in ein INSERT und ein oder mehrere UPDATE.

Unnötig zu sagen, dass ich auf Firebird und FIBtools nicht grad gut zu sprechen bin. Wenn ich die Wahl habe, nehme ich MariaDB oder SQLite.

[EgonHugeist]

Guten Morgähn,

Cody ich kenne die Komponenten nicht, jedoch sind mir diese Limitationen auch nicht bekannt. Ich würde mich Frickler anschließen auch für FB2.5. Die einzige Besonderheit, welche es zu beachten gilt, wenn das SQL Länger als High(Word) wird ist: Es darf kein #0 byte enthalten sein.

Zitat von Codehunter:

Anhand deines Beispiels aus dem Link: Query.Params[0].AsBooleans[1] := True; könntest du evtl. mal ein Beispiel-Query zeigen, wie das anzuwenden ist?

Nur mal so dahin geschmissen ohne Sinn und Zweck und ungetestet:

Code:

unit ZQueryBatchDML;

interface

uses ZConnection, ZDataSet;

procedure ExecuteQueryBatchInsert;

implementation

uses SysUtils;

procedure ExecuteQueryBatchInsert;
var Connection: TZConnection;
    Query: TZQuery;
    DMLidx: Integer;
    Succeeded: Boolean;
begin
  Connection := TZConnection.Create(nil);
  {assign everything to connect}
  Query := TZQuery.Create(nil);
  Query.Connection := Connection;
  Connection.Connect;
  try
    Connection.ExecuteDirect('CREATE TABLE DML_INSERT_DEMO('+
      'ID INTEGER NOT NULL,'+
      'NUM VARCHAR(32),'+
      'INSERT_TIMESTAMP DATETIME)');
    Connection.StartTransaction;
    Succeeded := False;
    try
      Query.SQL.Text := 'INSERT INTO DML_INSERT_DEMO VALUES (:ID,:NUM,:INSERT_TIMESTAMP)';
      Query.Params.BatchDMLCount := 10;
      for DMLidx := 0 to 9 do begin
        Query.Params[0].AsIntegers[DMLidx] := DMLidx +1;
        Query.Params[1].AsStrings[DMLidx] := IntToStr(DMLidx);
        Query.Params[2].AsDateTimes[DMLidx] := now;
      end;
      Query.ExecSQL;
      Assert(Query.RowsAffected = 10);
      Succeeded := True;
    finally
      if Succeeded
      then Connection.Commit
      else Connection.Rollback;
    end;
  finally
    Query.Free;
    Connection.Free;
  end;
end;

end.

Ist das nun verständlicher? Du kannst auch eine TZTransaction benutzen, wenn benötigt..

Zitat von Codehunter:

Kannst du mit ZEOS eigentlich schon die aktuelle FB 4.0 konnektieren?

Na klar, ich habe auch die neue (Seit FB3) OO_API eingebaut und auf den Stand der letzten FB4 Beta gebracht:
https://fossies.org/linux/Firebird/d...ng_OO_API.html

[Codehunter]

Also wenn ich das richtig verstehe, ist die magische Zeile diese: Query.Params.BatchDMLCount := 10; Damit definierst du, wie viele (gleiche) Zeilen es gibt und mit dem AsIntegers[DMLidx] spricht man dann die jeweilige Zeile an?

Hintergrundinfo: Wir hängen immer noch bei FB 2.5 fest, weil wir in den Vendor-Lock-In getappt sind. Als DAC haben wir uns vor vielen Jahren für FIBplus bzw. FIBtools entschieden. Zu der Zeit gabs noch gar kein FireDAC usw. Dummerweise hat der Hersteller von FIBplus irgendwann jegliche Tätigkeit eingestellt. Bis D 10.2.3 lassen sie sich noch kompilieren, danach ist Feierabend. Wir haben zwar die Quellen, aber die sind doch sehr "speziell" und schwer zu warten.

Wir haben dann mal eine Art Benchmark gebaut um zu vergleichen, welches DAC wie schnell ist. Im Rennen waren FIBplus, FireDAC, UniDAC und ZEOS. Bei SELECT-Queries mit diversen JOINS usw. sowie anschließender Iteration war das Ergebnis:

1. FIBplus
2. FireDAC
3. ZEOS
4. UniDAC

Bei INSERT und UPDATE mit prepared Statements war es so:

1. FIBplus
2. ZEOS
3. FireDAC
4. UniDAC

Wobei ich betone, die Quellen für den Benchmark sind aus der Erfahrung mit FIBplus entstanden und das Datenbankmodell womöglich auch daraufhin optimiert ist. Die FIBtools sind halt speziell für Interbase und Firebird macht worden und bis heute unschlagbar was die Performance bei Single-Queries angeht.

Spannend ist jedoch die Frage, wie schaut das Ganze bei Batch-Queries und Random Access (wie es im Zusammenspiel mit Devexpress häufig vorkommt) aus. Und da scheinen die FIBtools brutal in die Knie zu gehen. Das scheint ein Designproblem zu sein, weil die FIBtools anscheinend zwischen zwei Queries viel Zeit mit Aufräumen verbringen.

Nun können wir nicht ewig bei FB 2.5 bleiben. Vermutlich wird dieser Zweig spätestens mit dem Erscheinen von FB 4.0 aus jeglichem Support fallen. Irgendwann kommt dann vllt. ein Windows-Update und beerdigt den 2.5er Zweig. Ein Projekt wie unseres migriert man aber auch nicht im Handstreich auf eine andere DB. Schon gar nicht, wenn damit auch ein Wechsel des DAC verbunden ist.

FireDAC, UniDAC und ZEOS sind alle Multi-Backend-fähig. Das wird wohl darauf hinaus laufen, eins von diesen zu wählen. FireDAC ist das performanteste von diesen, jedoch zusammen mit UniDAC auch wieder mit der Gefahr eines Vendor-Lock-in behaftet. UniDAC zudem noch mit einem großen Abstand das langsamste. Für mich hätte ZEOS durchaus einen gewissen Charme, nur bin ich nicht der der am Ende entscheidet.