[gelöst] Zeos Insert

[Michl]

Servus,

irgendwie ist Zeos beim Insert von Daten sehr langsam. Es scheint es wird bei jedem neuem Daten hinzufügen ein Commit gemacht. Wie kann ich das beschleunigen? Mit SQLDB habe ich die Möglichkeit zuerst im Speicher zu arbeiten und dann mit der Transaction ein Commit explizit zu machen. Wie kann man das entsprechend in Zeos umsetzen?

Hintergrund ist der, daß ich später zur Laufzeit mehrere tausend Datensätze möglichst schnell speichern können muss.

Vergleichstest anbei mit einer 32bit Windows Sqlite - Dll (Zeos ist dabei hier meist 40x - 80x langsamer als SQLDB).

Ich nutze 32bit Lazarus Trunk, Windows 7, Zeos 7.2beta.

[mse]

TZConnection hat doch StartTransaction()/Commit()/Rollback(), funktioniert das nicht? Möglicherweise sollte man auch TZConnection.AutoCommit auf false stellen.
Man kann auch die begin/commit/rollback statements mit TZConnection.ExecuteDirect() ausführen:
https://sqlite.org/lang_transaction.html
-> vor ApplyUpdates() die Transaktion starten, danach committen.

[Michl]

TZConnection hat doch StartTransaction()/Commit()/Rollback(), funktioniert das nicht? Möglicherweise sollte man auch TZConnection.AutoCommit auf false stellen.

Mhm, das hatte ich gestern alles mal durchprobiert gehabt. Es wird scheinbar, egal ob Autocommit an oder aus immer in die Datenbank geschrieben (ich sehe auch daß immer ein temporäres File erstellt wird). Entweder ist irgendwas kaputt oder eine Einstellung fehlt mir bis dato. Werde mich mal weiter informieren und und weiter probieren. Danke erstmal.

[mse]

Code: Alles auswählen

 
<connection>.ExecuteDirect('BEGIN');
 
insert Operationen.
 
<connection>.ExecuteDirect('COMMIT');
 

hat auch keinen Einfluss?

[Michl]

Ich habe jetzt auch noch ZEOS 7.1.4 getestet. Dies verhält sich genauso, wie ZEOS 7.2b. Daher ist wohl nichts kaputt, sondern mir fehlt irgendeine Einstellung.

Code: Alles auswählen

 
<connection>.ExecuteDirect('BEGIN');
 
insert Operationen.
 
<connection>.ExecuteDirect('COMMIT');
 

hat auch keinen Einfluss?

Doch, das ist ist jetzt schnell (egal, ob Connection.AutoCommit aus/an). Kann man das nicht per ZConnection.Irgendeinproperty auch erreichen?

[Michl]

Habs gefunden: http://zeoslib.sourceforge.net/viewtopi ... 398#p25398

Wichtig ist in die TZConnections folgendes in die Properties einzutragen (exclusiver Zugriff ist OK für mein Projekt):

Code: Alles auswählen

synchronous=0
locking_mode=EXCLUSIVE

Flutscht nun wie die Sau.

Danke mse für die investierte Zeit!

[mse]

Das hat aber Konsequenzen:

Code: Alles auswählen

 
OFF (0)
 With synchronous OFF (0), SQLite continues without syncing as soon as it has handed data off to the operating system. If the application running SQLite crashes, the data will be safe, but the database might become corrupted if the operating system crashes or the computer loses power before that data has been written to the disk surface. On the other hand, commits can be orders of magnitude faster with synchronous OFF.
 

https://sqlite.org/pragma.html

Der eigentlich von Zeos vorgesehene Weg ist wohl:

Code: Alles auswählen

 
<connection>.Rollback(); //sicherstellen dass keine Transaktion läuft
<connection>.AutoCommit:= false;
<connection>.TransactionIsolationLevel:= tiReadCommitted;
                                   //darf nicht tiNone sein
<connection>.BeginTransaction();
try
 insert Operationen...
<connection>.Commit();
except
 <connection>.RollBack();
end;
 

Führt das auch zu einer Verbesserung? Die Frage über langsame Sqlite3 inserts wird vermutlich noch öfters kommen.

Der entsprechende code in Zeos ist src/dbc/ZDbcSqLite.pas:

Code: Alles auswählen

 
{**
  Starts a transaction support.
}
procedure TZSQLiteConnection.StartTransactionSupport;
var
  ErrorCode: Integer;
  ErrorMessage: PAnsiChar;
  SQL: RawByteString;
begin
  if TransactIsolationLevel <> tiNone then
  begin
    ErrorMessage := '';
    SQL := 'BEGIN TRANSACTION';
    ErrorCode := GetPlainDriver.Execute(FHandle, Pointer(SQL), nil, nil, ErrorMessage);
 

[Michl]

Der eigentlich von Zeos vorgesehene Weg ist wohl:

Habe das von dir vorgeschlagene Vorgehen mal probiert. Rollback geht nur mit AutoCommit aus. StartTransaction nur mit AutoCommit an. Lange Rede, kurzer Sinn, der entscheidende Hinweis war, daß ich vor StartTransaction das TransactIsolationLevel setzen muss, sonst geht dieses nicht (irgendwie logisch im nachhinein , nur wissen muss man es).

Der Test läuft eigentlich recht geschwind so:

Code: Alles auswählen

procedure TForm1.TestFill5;
var
  i: Integer;
begin
//  Connection5.AutoCommit := False;
//  Connection5.Rollback;
  Connection5.TransactIsolationLevel := tiReadCommitted;  // oder anderes TransactIsolationLevel 
  Connection5.StartTransaction;
  try
    for i := 0 to TestMax do
      Execute('INSERT INTO foo (str1, str2, str3) VALUES (' +
        '''first entry ' + IntToStr(i) + ''', ' +
        '''second entry ' + IntToStr(i) + ''', ' +
        '''third entry ' + IntToStr(i) + ''');');
    Connection5.Commit;
  finally
//    Connection5.Rollback;
//    Connection5.AutoCommit := True;
  end;
end;  

Ist es sinnvoll TransactIsolationLevel generell auf tiReadCommitted zu lassen? Ich habe mal im Wiki nachgeschaut https://de.wikipedia.org/wiki/Isolation ... on_bei_SQL es wäre doch anscheind sinnvoll den TransactIsolationLevel immer auf tiSerializable zu stellen (scheint auch nicht langsamer zu sein).

[mse]

Ist es sinnvoll TransactIsolationLevel generell auf tiReadCommitted zu lassen? Ich habe mal im Wiki nachgeschaut https://de.wikipedia.org/wiki/Isolation ... on_bei_SQL es wäre doch anscheind sinnvoll den TransactIsolationLevel immer auf tiSerializable zu stellen (scheint auch nicht langsamer zu sein).

Der Grund ist, dass Zeos bei Sqlite3 zwischen den einzelnen Levels keinen Unterschied macht:

Code: Alles auswählen

 
{**
  Starts a transaction support.
}
procedure TZSQLiteConnection.StartTransactionSupport;
var
  ErrorCode: Integer;
  ErrorMessage: PAnsiChar;
  SQL: RawByteString;
begin
  if TransactIsolationLevel <> tiNone then
  begin
    ErrorMessage := '';
    SQL := 'BEGIN TRANSACTION';
 

Es wird immer BEGIN TRANSACTION ausgeführt was DEFERRED entspricht.
https://sqlite.org/lang_transaction.html
Das Sqlite3 Transaktionmodell passt nicht ins ReadUnCommitted/ReadCommitted/RepeatableRead/Serializable Schema. Bei anderen Datenbanken sollte man aus Performancegründen und um andere Anwender nicht unnötig zu blockieren nur die tatsächlich benötigte Isolation wählen.

[Michl]

Vielen Dank für die nützlichen Hinweise! Habe noch ein bischen quergelesen, ist erstmal gut so für mich.