Page 1 of 1
How can I improve the insert speed for sqlite?
Posted: 11.01.2014, 16:37
by 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.
Re: How can I improve the insert speed for sqlite?
Posted: 12.01.2014, 14:39
by 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.
Re: How can I improve the insert speed for sqlite?
Posted: 12.01.2014, 15:50
by 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
(Read about: PRAGMA synchronous and PRAGMA locking_mode)
You should go below one minute.
Michal
Re: How can I improve the insert speed for sqlite?
Posted: 12.01.2014, 19:13
by 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?
Re: How can I improve the insert speed for sqlite?
Posted: 12.01.2014, 20:54
by Bofinken
duplicate
Re: How can I improve the insert speed for sqlite?
Posted: 12.01.2014, 22:45
by miab3
@Bofinken,
What am I doing wrong?
You should add propertys before the Connect (may be in designtime).
Michal
Re: How can I improve the insert speed for sqlite?
Posted: 12.01.2014, 23:26
by 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.
Re: How can I improve the insert speed for sqlite?
Posted: 21.12.2014, 05:20
by 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
Re: How can I improve the insert speed for sqlite?
Posted: 21.12.2014, 16:05
by 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
Re: How can I improve the insert speed for sqlite?
Posted: 21.12.2014, 18:19
by 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