Delphi - Query running slow

Question

My query,when run takes about 7 seconds to do what is supposed to.But,since its inserting about 30 records,I think it is too slow.Now,either I am running the query that is not written well or it does actually takes this much time. But that would be strange. The underlying database is SQLite and the query looks like this :

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;//boolean field true/false
         Uniquery1.Next;
         ExecSQL;
end;
end;
end;

So can someone tell me if this is OK or am I missing something ? All fields are text except the 'a4' which is boolean (true/false).

The answer,modified (based on suuggestion from LS_dev):

procedure TForm1.cxButton1Click(Sender: TObject);
begin
    with UNIquery2 do begin
        Close;
        SQL.Clear;
        SQL.Add('INSERT INTO MYTABLE (FIELD1,FIELD2,FIELD3,FIELD4) VALUES (:a1,:a2,:a3,:a4)');
        SQL.Prepare;
        UniTransaction.AddConnection(UniConnection2);
        UniTransaction.StartTransaction;
try
        UNIQuery1.First;
        while Uniquery1.EOF = false do begin
            Params[0].asString := AdvOfficeStatusBar1.Panels[0].Text;
            Params[1].asString := UniTable1.FieldByName('FIELD2').asString;
            Params[2].asString := Uniquery1.FieldByName(',FIELD3').asString;
            Params[3].Value := Uniquery1.FieldByName('FIELD4').Value;//boolean field true/false
            Uniquery1.Next;
            ExecSQL;
        end;
        UniTransaction.Commit;
finally
  if UNIquery2.Connection.InTransaction then
    UNIquery2.Connection.Rollback;
    end;
    end;
    end;

You should assign the SQL statement for UniQuery2 once outside of the UniQuery1 loop. Also define the parameters (ptInput and ftString/ftString), then maybe Prepare the Query (dunno if UniQuery has that). Then in your loop you only have to set parameter values. — Jan Doggen, Dec 10 '13 at 07:18
Also, when asking questions with code, you variable definitions need to be present. What's this UniQuery kind of animal? Edit your question. — Jan Doggen, Dec 10 '13 at 07:18
As first step, stop using `XXXByName` in the loop, use indices instead. — Free Consulting, Dec 10 '13 at 07:19
I need to go through all UNIQuery1 records.I am not sure I am following you. Can you provide me some code on how it should be in your opinion ? — user763539, Dec 10 '13 at 07:20
possible duplicate of [Android SQLite database: slow insertion](http://stackoverflow.com/questions/3501516/android-sqlite-database-slow-insertion); this is a [FAQ](http://www.sqlite.org/faq.html#q19). — CL., Dec 10 '13 at 09:07
@user763539: Are you sure you mean "UniTable1.FieldByName('FIELD2')", not UniQuery1? Including your variable declarations can avoid the need for questions like this ... — MartynA, Dec 10 '13 at 10:47

score 8 · Accepted Answer · edited Dec 10 '13 at 15:25

Don't know Delphi, but will suggest some improvements:

You are not using a transaction. You should have something like something like auto-commit disabled and COMMIT command after all insertions;
Your SQL.Text:=... should probably be out of while. If this property set compiles SQL statement, putting it out of while will prevent unnecessary VDBE compilations;
If your intent is copying rows from one table to another (with a static field), you may doing using a single SQL command like INSERT INTO MYTABLE SELECT :a1, FIELD2, FIEDL3, FIELD4 FROM source_table, setting ParamByName('a1').asString := AdvOfficeStatusBar1.Panels[0].Text

This is generic DB usage improvement, hope gives you some direction.

Suggestion using unique SQL:

procedure TForm1.cxButton1Click(Sender: TObject);
begin
    with UNIquery2 do
    begin
        SQL.Clear;
        SQL.Add('INSERT INTO MYTABLE (FIELD1,FIELD2,FIELD3,FIELD4) SELECT ?,FIELD2,FIELD3,FIELD4 FROM UNIquery1_source_table');
        Params[0].asString := AdvOfficeStatusBar1.Panels[0].Text;
        ExecSQL;
    end;
end;

Suggestion using improved DB handling:

procedure TForm1.cxButton1Click(Sender: TObject);
begin
    with UNIquery2 do 
    begin
        Close;
        SQL.Clear;
        SQL.Add('INSERT INTO MYTABLE (FIELD1,FIELD2,FIELD3,FIELD4) VALUES (:a1,:a2,:a3,:a4)');
        SQL.Prepare;
        UniTransaction.AddConnection(UniConnection2);
        UniTransaction.StartTransaction;
        UNIQuery1.First;
        while Uniquery1.EOF = false do 
        begin
            Params[0].asString := AdvOfficeStatusBar1.Panels[0].Text;
            Params[1].asString := UniTable1.FieldByName('FIELD2').asString;
            Params[2].asString := Uniquery1.FieldByName(',FIELD3').asString;
            Params[3].Value := Uniquery1.FieldByName('FIELD4').Value;//boolean field true/false
            Uniquery1.Next;
            ExecSQL;
        end;
        UniTransaction.Commit;
    end;
end;

score 0 · Answer 2 · answered Dec 10 '13 at 08:35

If the SQL INSERT itself is slow, I recommend to test its execution speed in an interactive client first. Or write a simple test application which performs one hard-coded INSERT and measures its execution time.

Also you can use the debugger, logging or a profiler to find out the operation in your code which consumes time - it could be the Uniquery1.Next or the ExecSQL for example.

Delphi - Query running slow

2 Answers2