UNIQuery too slow

[Senad]

When running this query against an SQLite database, UNIQuery takes about 8 seconds to insert about 30 rows of data :

Code: Select all

procedure TForm1.cxButton1Click(Sender: TObject);
begin
with UNIquery2 do begin
  Close;
  SQL.Clear;
UNIQuery1.First;
while Uniquery1.EOF = false do begin
SQL.Text:= 'INSERT INTO MYTABLE (FIELD1,FIELD2,FIELD3,FIELD4) VALUES (:a1,:a2,:a3,:a4)';
         ParamByName('a1').asString := AdvOfficeStatusBar1.Panels[0].Text;
         ParamByName('a2').asString := UniTable1.FieldByName('FIELD2').asString;
         ParamByName('a3').asString := Uniquery1.FieldByName(',FIELD3').asString;
         ParamByName('a4').Value := Uniquery1.FieldByName('FIELD4').Value;
         Uniquery1.Next;
         ExecSQL;
end;
end;
end;

This is too slow. Why does UNIQuery takes this much time?
Also, If I add ShowMessage('done.') after UniQuery2AfterExecute, I get the message after every record inserted. How can I get the message 'done' after the query has finished executing all what it is supposed to ?

[CristianP]

This is too slow. Why does UNIQuery takes this much time?

Because you demand SQLite to execute each insert separately and this costs time. It is much faster to use transactions.
Also you can change "PRAGMA synchronous=OFF" but you database will be more vulnerable to corruptions.

Code: Select all

UNIquery2.Connection.StartTransaction;
try
  //do your inserts

  if UNIquery2.Connection.Commit;
finally
  if UNIquery2.Connection.InTransaction then
    UNIquery2.Connection.Rollback;
end;

Also, If I add ShowMessage('done.') after UniQuery2AfterExecute, I get the message after every record inserted. How can I get the message 'done' after the query has finished executing all what it is supposed to ?

After you call ExecSQL and execution is finished then AfterExecute is called.
You can group all updates into one execution separated with semicolons. In this case you will have only one execution.

Best Regards,
Cristian Peta