How to speed up sqlite transactions?
lazarus / 16-Databases

----------------------------------------------------------------------
stem · 2015-05-08 17:07

Hi,

I'm working on a (Windows) desktop application with Sqlite. Everything
works
fine.

I've put every database access into a transaction (see the following
code fragment). Is it possible to optimize the disk accesses? My
application has
no "save button" and puts all new data directly into the database
which results
in quite heavy disk access.

Thank you! 

stem

Code: [Select]  try
    SQLTransaction.Commit();
    SQLTransaction.StartTransaction();

    SQLQuery.SQL.Clear();

    SQLQuery.SQL.Text := 'UPDATE ...';  // or SELECT, INSERT, CREATE
    SQLQuery.Params.ParamByName('PID').AsInteger := id;

    SQLQuery.ExecSQL();
    SQLTransaction.Commit();
  except
    on E: Exception do
    begin
      showException(E.message);
    end;
  end;

----------------------------------------------------------------------
garlar27 · 2015-05-08 18:19

The delay is mainly in disc access for writing (as you already
notice).

There are 2 ways (AFAIK) to speed-up the writing process:
   1- Recompile SQlite library to use OS cached writings (go to SQLite
web site to know more about it since it has pros and cons and YRMV).
   2- Put all transactions in a big script and execute it. Some thing
like:

Code: [Select]SQ := 'INSERT INTO xxx;'+LineEnding+'UPDATE
xxxxxx;'{etc. etc. etc.};
SQLQuery.SQL.Text := SQ;
SQLQuery.ExecSQL();
SQLTransaction.Commit();
// You can use ExecuteDirect(SQ) from TDBConnection (if my memory is
good), which is way faster than ExecSQL();

or
Code: [Select]  try
    SQ := 'INSERT INTO xxx;'+LineEnding+'UPDATE xxxxxx;'{etc. etc.
etc.};
    SQLite3Connection1.ExecuteDirect('BEGIN TRANSACTION;');
    SQLite3Connection1.ExecuteDirect(SQ);
    SQLite3Connection1.ExecuteDirect('COMMIT TRANSACTION;');
  except
    on E: Exception do
    begin
      SQLite3Connection1.ExecuteDirect('ROLLBACK;');
      showException(E.message);
    end;
  end;

----------------------------------------------------------------------
cdbc · 2015-05-08 20:28

Hi
You may consider to roll your own TClientDataset 
Can be done quite easily with memdataset, sqlTransaction and
sqlite3Connection.... Works like a charm ;-)
Regards Benny

----------------------------------------------------------------------
cdbc · 2015-05-08 20:40

Hi again 
Here's some of my "spaghetti", have a look'see maybe you can use some
of it   Feel free, it's public domaine 
Btw. comments and critique are welcome 
Regards Benny

----------------------------------------------------------------------
miab3 · 2015-05-08 21:25

In case of ZEOS 7.2-beta,  before the connect should be added:    

Code: [Select]ZConnection1.Properties.Add('synchronous=0');    
ZConnection1.Properties.Add('locking_mode=EXCLUSIVE');
http://zeoslib.sourceforge.net/viewtopic.php?t=10608
http://zeoslib.sourceforge.net/viewtopic.php?f=40&p=41664#p38406

Michal