Insert into SQLite3 is very slow

[PetrHL]

Hello,

I'd like to insert a few records into SQLite3 database but it takes very long time. It seems it saves every record one by one because I can hear that my HDD works vey hard .

The code is:

Code: Select all

   
const
  C_INS = 'insert into nastdiety(stat,dieta) values(%s,%d)';

 try try
      Q.Close;
      Q.Connection.StartTransaction;
      for i := 0 to dmUtils.Countries.Count-1 do
      begin
        Q.SQL.Text := Format(C_INS,[QuotedStr(dmUtils.Countries.Strings[i]),1]);
        Q.ExecSQL
      end;
      Q.Connection.Commit
    except
      Q.Connection.Rollback
    end
    finally
      qryNastDiety.Close;
      qryNastDiety.Open
    end

It's horrible. I have quad core machine with 6GB of RAM and it tooks about 40 seconds to store 50 records to database. Anyone know what could be wrong, please?

Petr

[EgonHugeist]

PetrHL,

i know about this sqlite speed issue. What i don't know is if we can change something for that case. AFAIK SQLite does not support parameter or prepared-statetements. If i'm wrong than i'm willing to implement this kind of statements, which btw. all other protocols do support. If that could solve the speed issue, i don't know. What i know that's no Zeos issue..

[PetrHL]

Hello Michael,

thank you for your help.

Maybe I've found a workaround. According to SQLite documentation, it waits after every query to full sync.

So before insert of lots of records, it's better to turn off the sync and turn it on back after the operation.

Code: Select all

try
  Q.SQL.Text := 'PRAGMA synchronous=0';
  Q.ExecSQL;

  //do lots of inserts

finally
  Q.SQL.Text := 'PRAGMA synchronous=2';
  Q.ExecSQL
end

It helped a lot but it seems that even if I call Q.Connection.StartTransaction and Q.Connection.Commit it's ignored and it saves record after every insert and not all in one transaction. According to FAQ#19 of SQLite, it should handle about 60 inserts per second.

Petr

http://www.sqlite.org/pragma.html#pragma_synchronous
http://www.sqlite.org/faq.html#q19

[EgonHugeist]

PetrHL,

a good hint for me. Also did i read an article about pagesizes in the past. It should speed up everything if the default pagesize >= 4096.

Did you Set TZConnection.AutoCommit = False?

And start a transaction like TZConnection.StartTransaction?

If Autocommit is true then Zeos does commit each update...

Btw: http://www.sqlite.org/cintro.html seems like SQLite does support prepared statments. If i have the time i'll add that api to Zeos7.

[PetrHL]

if I try Q.Connection.AutoCommit := False and do Q.Connection.StartTransaction I get "error invalid operation in non AutoCommit mode" so I don't call StartTransaction when AutoCommit is False and call only Commit after all data are inserted.

When AutoCommit is true, I can call Q.Connection.StartTransaction and after all data are inserted Q.Connection.Commit. According to forum posts found by Google, this should be the same like I tried before.

Sad is, that it doesn't have any effect at all. It saves data with flush after every insert so it's very slow. It seems that it's related to Zeos library. I have SQLite manager called sqliteman (written in QT) and it works as expected. Vey fast insert, transactions working.

[EgonHugeist]

PetrHL,

uh. Hmm if i have time this weekend i'll make make a testcase. I trust you with you suggestion. Maybe i'll implement the prepared statments too (if i've the time to do it)..

[EgonHugeist]

PetrHL,

i've commited the CAPI prepared statement for SQLite today. Rev 2012 /testing (SVN).

Actually this statement is not default but you can get access by using TZQuery.Options [doPreperePrepared] = True. Then Zeos switches to this statment.

Currently i had not the time for an own test case. Did you chage the TZConnection.TransactionIsolationLevel to tiReadCommited and then TZConnection.StartTransaction? IMHO this should work. ((:

Edit: I did my testcaase:

Code: Select all

  NowTime := now;
  ZQuery1.SQL.Text := 'INSERT INTO people(P_ID, P_NAME, P_BEGIN_WORK, P_END_WORK) values (:P_ID, :P_NAME, :P_BEGIN_WORK, :P_END_WORK)';
  for i := 100 to 1000 do
  begin
    ZQuery1.Params[0].AsInteger := i;
    ZQuery1.Params[1].AsString := 'human'+IntToStr(i);
    ZQuery1.Params[2].AsDateTime := NowTime;
    ZQuery1.Params[3].AsDateTime := now;
    ZQuery1.ExecSQL;
  end;

This one with TransactionIsolationLevel = tiNone needs over two miniutes for the 900 records.

Code: Select all

  NowTime := now;
  ZConUnicode.Connected := False;
  ZConUnicode.TransactIsolationLevel := tiReadCommitted;
  ZConUnicode.Connected := True;
  ZConUnicode.StartTransaction;
  ZQuery1.Options := ZQueryUnicode.Options + [doPreferPrepared];
  ZQuery1.SQL.Text := 'INSERT INTO people(P_ID, P_NAME, P_BEGIN_WORK, P_END_WORK) values (:P_ID, :P_NAME, :P_BEGIN_WORK, :P_END_WORK)';
  for i := 100001 to 200000 do
  begin
    ZQuery1.Params[0].AsInteger := i;
    ZQuery1.Params[1].AsString := 'human'+IntToStr(i);
    ZQuery1.Params[2].AsDateTime := NowTime;
    ZQuery1.Params[3].AsDateTime := now;
    ZQuery1.ExecSQL;
  end;
  ZConUnicode.Commit;

This one with TransactionIsolationLevel = tiReadCommited + CAPI statment needs 2 seconds for 99999 rows.

Code: Select all

  NowTime := now;
  ZConUnicode.StartTransaction;
  ZQuery1.Options := [];
  ZQuery1.SQL.Text := 'INSERT INTO people(P_ID, P_NAME, P_BEGIN_WORK, P_END_WORK) values (:P_ID, :P_NAME, :P_BEGIN_WORK, :P_END_WORK)';
  for i := 200001 to 300000 do
  begin
    ZQuery1.Params[0].AsInteger := i;
    ZQuery1.Params[1].AsString := 'human'+IntToStr(i);
    ZQuery1.Params[2].AsDateTime := NowTime;
    ZQuery1.Params[3].AsDateTime := now;
    ZQuery1.ExecSQL;
  end;
  ZConUnicode.Commit;

This one with TransactionIsolationLevel = tiReadCommited without CAPI statment needs 4 seconds for 99999 rows.

So it's just a question how you use Zeos IMHO...

[mdaems]

PetrHL,
Just for your information:
The new PreparedStatement API has been moved to the testing-7.1 branch. So it will only be available from Zeos 7.1, not in the current beta version.

And just my vision on the speed of specialized sqlite admin tools : they are written for optimal efficience with sqlite. Zeos is a multi-purpose/multi-database tool.
We can't afford to do 'unsafe' stuff as disabling sync of flushing by default, because inmost use cases people just don't want to take the risk to corrupt the database.
But if you can disable these safeties by setting the pragmas yourself : that's perfectly allright.
Might it be a good idea to handle pragmas in sqlite the way we automatically load connection settings in mysql (TZMySQLConnection.Open)? Then we'll be very happy to accept (or discuss) patches...

Mark