[Lazarus] RE : RE : SQLite performance problem. Simple query takestoolong.

[Howard Lee Harkness]

On Fri, Jul 29, 2011 at 1:42 PM, Ludo Brands <ludo.brands at free.fr> wrote:
> I traced through TSqlQuery.Close and there isn't happening a lot, except for
> cleaning up prepared statements and cursors. And that is where your direct
> BEGIN/COMMIT could very well confuse sqlite.
> sqlite3_finalize(fstatement) is used to unprepare a statement. But is
> fstatement still valid after you have restarted a transaction? I doubt it.
> Do replace the ExecuteDirect with a TSqlQuery component. It is only a small
> change and it removes a lot of causes of errors.

Ok, it seems like a reasonable suggestion, so I tried that.

The TSQLQuery object:
  object qDIU: TSQLQuery
    IndexName = 'DEFAULT_ORDER'
    AutoCalcFields = False
    Database = FormDatabase.SQLite3Connection
    Transaction = FormDatabase.SQLTransaction
    ReadOnly = False
    Params = <>
    left = 308
    top = 48
  end

The updated procedure:
procedure TfrmMain.ExecSQL(sql:string);
begin
//     FormDatabase.SQLite3Connection.ExecuteDirect(sql);
//     FormDatabase.SQLite3Connection.ExecuteDirect('commit');
//     FormDatabase.SQLite3Connection.ExecuteDirect('begin transaction');
       qDIU.SQL.Text:=sql;
       qDIU.ExecSQL;
       FormDatabase.SQLTransaction.CommitRetaining;
end;

I also tracked down one other usage of ExecuteDirect in another form
(not used for this test, but just in case) and applied the same
change.

The result: No improvement. If anything, things got slightly worse.

However, I think maybe I have some insight into what is causing the
problem. It looks like the lookups for the dependent tables are
happening multiple times. Not sure why that would be (I didn't think I
did anything to that part of the code other than change database
engines), but I hope to find out shortly.
-- 
Howard Lee Harkness
(214) 390-4896