How can I improve the insert speed for sqlite?

[Bofinken]

I am using TZConnection and TZQuery to load about 50000 records into a table.
If I use Protocol := ‘sqlite-3’ it takes over an hour, but if I use Protocol := ‘firebird-2.5’ it is less then a minute. I have used the default settings.
How can I improve the insert speed for sqlite?
I have read that I should use TransactIsolationLevel := tiReadCommited, but when trying I get Undeclared identifier: 'tiReadCommited' and I cannot figure out what unit to add.

[EgonHugeist]

Sure you use 7.2? Your're posting in 7.2 forum here..

Hints accordingly perforance:

Don't use AutoCommit mode. Just do all insertiation in one transaction but take care about EOutOfMemory. So i propose to commit the data after 1000 rows.

SQLite is very slow in AutoCommit mode. Also is an SSD or Virtual Ram-Drive an imbelievable performance winner. Some more tuning tips: http://zeoslib.sourceforge.net/viewtopi ... &start=180
Actually SQLite is the fastest plain we have.

[miab3]

@Bofinken,

You can use by default AutoCommit mode but before long sequence of modifying data in the database do:

Code: Select all

 ZConnection1.AutoCommit:=false; // or ZConnection1.StartTransaction;
  begin
  Your sequence of inserts (updates)
  end;
 ZConnection1.Commit;
 ZConnection1.AutoCommit:=true;

If yet in TZConnection.Propperties you add:

Code: Select all

synchronous=0
locking_mode=EXCLUSIVE

R3063 http://sourceforge.net/p/zeoslib/code-0/3063/
(Read about: PRAGMA synchronous and PRAGMA locking_mode)

You should go below one minute.

Michal

[Bofinken]

Not as easy as it looks, I am still struggling!
Here is how I have implemented your advice:

Code: Select all

    ZConnection1.AutoCommit := False;
    ZConnection1.Properties.Add('synchronous=0');
    ZConnection1.Properties.Add('locking_mode=EXCLUSIVE');
    // 1 minute 23 sec for 555 rows sqlite
    // 1 sec for 555 rows firebird     

What am I missing?

[Bofinken]

duplicate

[miab3]

@Bofinken,

What am I doing wrong?

You should add propertys before the Connect (may be in designtime).

Michal

[Bofinken]

Found the problem, it was the combination of setting AutoCommit and TransactIsolationLevel that fixed the speed, the Properties suggestion was irrelevant.

Code: Select all

ZConnection1.AutoCommit := False;
ZConnection1.TransactIsolationLevel:= tiReadCommitted;

I needed to add ZDbcIntfs to my uses in Lazarus to set TransactIsolationLevel:= tiReadCommitted this was hard to find out and my question when I started the thread.
In Delphi 7 I am still using 7.1.2 of Zeos and still getting Undeclared identifier: 'tiReadCommited' even after adding ZDbcIntfs to my uses. I can tick it in designtime, but need to do it by code. Mybe 7.2.0 of Zeos will work now for Delphi 7.

[rdfrahm]

Great thread with some great ideas. Unfortunately, when I set TransactIsolationLevel to tiReadCommitted I get an error that says:

Safety level may not be changed inside a transaction.

I haven't been able to find any info about this. Any idea what's causing it? I have AutoCommit set to False and I'm trying to do a commit after every 1000 inserts.

Thanks,
Rich

[marsupilami]

Hello rdfrahm,

if you get that kind of error message, I think that you already opened the database. Set the transaction isolation level before connecting to your database.

Code: Select all

ZConnection.TransactionIsolation := tiReadCommitted;
ZConnection.Connect;

Also it might be a good idea to explicitly start a transaction for doing the inserts. Something like

Code: Select all

ZConnection.StartTransaction
try
  // Do your Inserts here
  ZConnection.Commit;
except
  ZConnection.Rollback;
  raise;
end;

Best regards,

Jan

[rdfrahm]

Thanks for the input Jan...I appreciate it! However, I am going to have to apologize for not being complete in doing my due diligence before asking for help. I forgot that one of the things I tried before to speed up my inserts was to do PRAGMA synchronous = 0 before doing my inserts and PRAGMA synchronous = 2 after my inserts. This is what was actually causing my problem. Now, after setting TransactIsolationLevel to tiReadCommitted, a transaction that was taking 4.5 minutes takes a second!

Thanks,
Rich