Slow creating database

[aggg63]

Hello.

I'm working in a little database with SQLite. Till now I'm using CNPack for SQLite2. Works very well, It make 3 tables and 1 index in 10 seconds or less. The tables are populating with records and the greatest have 1700 records with 5 fields. I repeat, all in 10 seconds. I find Zeos components and now I'm migrating the database. But now creating the database spent 5 minutes. I test several things but doesn't work, for example

Executing all commands in this sequence

Code: Select all

   ZConsultaBD.SQL.Clear;
   ZConsultaBD.SQL.Add(comandoSQL);
   ZConsultaBD.Open;

Make an script for the commands

Code: Select all

   ZProcesoBD.Script.Clear;
   ZProcesoBD.Script.Add(comandoSQL+';');
   ZProcesoBD.Execute;

I test also execute ZProcesoBD.Script.Clear before the generator loop and ZProcesoBD.Execute after the loop. Worst results.

I have these relations and definitions:

Code: Select all

    ZConexionBD.Database:=ficheroBaseDatos;
    ZConexionBD.Protocol:='sqlite-3';
    ZConexionBD.Connected:=TRUE;
    ZConsultaBD.Connection:=ZConexionBD;
    ZProcesoBD.Connection:=ZConexionBD;

¿Somebody can tell me what is wrong? Or ¿Can tell me what is missing to optimize the queries? Thanks. I'm working with Delphi 2005.

[mse]

Try to write the records in a single transaction otherwise there will be a fsync on every write of a record which slows down updating speed. I don't know how it is done in Zeos, MSEgui has a dedicated tmsetransaction component and a slo_transactions flag in tsqlite3connection.options. It is off by default because transactions with Sqlite3 are a little bit tricky to use. A not finished fetch operation locks write operations for example.

Martin

[aggg63]

First

Code: Select all

ZConsultaBD.SQL.Text('BEGIN');
ZConsultaBD.Open;

NO work

Code: Select all

ZConexion.StartTransaction;

Last

Code: Select all

ZConsultaBD.SQL.Text('COMMIT');
ZConsultaBD.Open;

Thanks. (Thank you Martin)

[mdaems]

Did you Try ZConexion.AutoCommit := false ?

Mark