[gelöst] Zeos Insert

Für Themen zu Datenbanken und Zugriff auf diese. Auch für Datenbankkomponenten.
Antworten
Michl
Beiträge: 2505
Registriert: Di 19. Jun 2012, 12:54

[gelöst] Zeos Insert

Beitrag von 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.
Dateianhänge
SQLiteInsertTest.zip
(325.08 KiB) 105-mal heruntergeladen
Zuletzt geändert von Michl am Mo 24. Jul 2017, 21:31, insgesamt 1-mal geändert.

Code: Alles auswählen

type
  TLiveSelection = (lsMoney, lsChilds, lsTime);
  TLive = Array[0..1] of TLiveSelection; 

mse
Beiträge: 2013
Registriert: Do 16. Okt 2008, 10:22
OS, Lazarus, FPC: Linux,Windows,FreeBSD,(MSEide+MSEgui 4.6,git master FPC 3.0.4,fixes_3_0)
CPU-Target: x86,x64,ARM

Re: Zeos Insert

Beitrag von 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
Beiträge: 2505
Registriert: Di 19. Jun 2012, 12:54

Re: Zeos Insert

Beitrag von Michl »

mse hat geschrieben: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.

Code: Alles auswählen

type
  TLiveSelection = (lsMoney, lsChilds, lsTime);
  TLive = Array[0..1] of TLiveSelection; 

mse
Beiträge: 2013
Registriert: Do 16. Okt 2008, 10:22
OS, Lazarus, FPC: Linux,Windows,FreeBSD,(MSEide+MSEgui 4.6,git master FPC 3.0.4,fixes_3_0)
CPU-Target: x86,x64,ARM

Re: Zeos Insert

Beitrag von mse »

Code: Alles auswählen

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

hat auch keinen Einfluss?

Michl
Beiträge: 2505
Registriert: Di 19. Jun 2012, 12:54

Re: Zeos Insert

Beitrag von 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.

mse hat geschrieben:

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?

Code: Alles auswählen

type
  TLiveSelection = (lsMoney, lsChilds, lsTime);
  TLive = Array[0..1] of TLiveSelection; 

Michl
Beiträge: 2505
Registriert: Di 19. Jun 2012, 12:54

Re: Zeos Insert

Beitrag von Michl »

Habs gefunden: http://zeoslib.sourceforge.net/viewtopic.php?p=25398#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!

Code: Alles auswählen

type
  TLiveSelection = (lsMoney, lsChilds, lsTime);
  TLive = Array[0..1] of TLiveSelection; 

mse
Beiträge: 2013
Registriert: Do 16. Okt 2008, 10:22
OS, Lazarus, FPC: Linux,Windows,FreeBSD,(MSEide+MSEgui 4.6,git master FPC 3.0.4,fixes_3_0)
CPU-Target: x86,x64,ARM

Re: Zeos Insert

Beitrag von 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
Beiträge: 2505
Registriert: Di 19. Jun 2012, 12:54

Re: [gelöst] Zeos Insert

Beitrag von Michl »

mse hat geschrieben: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 :wink: , 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_(Datenbank)#Transaktionsisolation_bei_SQL es wäre doch anscheind sinnvoll den TransactIsolationLevel immer auf tiSerializable zu stellen (scheint auch nicht langsamer zu sein).

Code: Alles auswählen

type
  TLiveSelection = (lsMoney, lsChilds, lsTime);
  TLive = Array[0..1] of TLiveSelection; 

mse
Beiträge: 2013
Registriert: Do 16. Okt 2008, 10:22
OS, Lazarus, FPC: Linux,Windows,FreeBSD,(MSEide+MSEgui 4.6,git master FPC 3.0.4,fixes_3_0)
CPU-Target: x86,x64,ARM

Re: [gelöst] Zeos Insert

Beitrag von mse »

Michl hat geschrieben:Ist es sinnvoll TransactIsolationLevel generell auf tiReadCommitted zu lassen? Ich habe mal im Wiki nachgeschaut https://de.wikipedia.org/wiki/Isolation_(Datenbank)#Transaktionsisolation_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
Beiträge: 2505
Registriert: Di 19. Jun 2012, 12:54

Re: [gelöst] Zeos Insert

Beitrag von Michl »

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

Code: Alles auswählen

type
  TLiveSelection = (lsMoney, lsChilds, lsTime);
  TLive = Array[0..1] of TLiveSelection; 

Antworten