I am new to SQLite and ZeosLib and have ended up here in my attempt to speedup SQLite3 insert queries. After reading a bit I found that if I set the synchronous mode of SQLite to 'off', the insert queries should be 50 times faster. Wanting to test this, I wrote the following small application in Lazarus /FPC, which inserts data into a table that has 255 fields, each set to a varchar(255) data type:
Code: Select all
Writeln('Started FOR');
strQuery := 'Insert into speed_test (';
for nCntr := 1 to 255 do
begin
if nCntr < 255 then
strquery := strQuery + 'field' + IntTostr(nCntr) + ' , '
else
strQuery := strQuery + 'field' + IntToStr(nCntr) + ') values (';
end;
for nCntr := 1 to 255 do
begin
if nCntr < 255 then
strquery := strQuery + '''value_' + IntTostr(nCntr) + ''', '
else
strQuery := strQuery + '''value_' + IntToStr(nCntr) + ''');';
end;
Writeln('Query constructed');
ZQuery.SQL.Text := 'pragma synchronous;';
ZQuery.Open;
Writeln('PRAGMA STATE : ', ZQuery.Fields[0].AsString);
ZQuery.SQL.Text := 'pragma synchronous = off;';
ZQuery.ExecSQL;
ZQuery.SQL.Text := 'pragma synchronous;';
ZQuery.Open;
Writeln('PRAGMA STATE : ', ZQuery.Fields[0].AsString);
Writeln('Adding 150 inserts to buffer');
for nCntr := 1 to 150 do
begin
Write(nCntr,' ');
ZQuery.SQL.Add(strQuery);
end;
Writeln('Started Query');
dtStart := Date + Time;
ZQuery.ExecSQL;
dtEnd := Date + Time;
Writeln('Stopped, took : ', MilliSecondsBetween(dtStart, dtEnd));
1) It takes forever to execute the for statement that adds the queries to the buffer (ZQuery.SQL.Add(strQuery)). Is there a way to make this faster?
2) With synchronous mode set to 'full', the query takes 1.5 seconds to complete the insert
3) With synchronous mode set to 'off', the query takes 560 milliseconds to complete the insert. I was expecting it to be much faster.
Is the above results the expected result or am I doing something wrong here causing a bottle neck?
Kind Regards