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

[Ludo Brands]

> Thank you for your reply. I found it very interesting. I 
> switched the code that I posted to the following (I 
> refactored this so that I could call from several different 
> places, for inserts, updates, and
> deletes):
> 
> procedure TfrmMain.ExecSQL(sql:string);
> begin
>      FormDatabase.SQLite3Connection.ExecuteDirect(sql);
>      FormDatabase.SQLite3Connection.ExecuteDirect('commit');
>      FormDatabase.SQLite3Connection.ExecuteDirect('begin 
> transaction'); end;
> 
> I suppose that I could use a query component in there, but I 
> don't know what difference that would make (this code is not 
> presenting any performance issues; see below), and I'm a bit 
> confused about the proper usage. There are some properties of 
> TSQLQuery for which I have been unable to locate usage examples.

Th difference is that TSQLTransaction and TSQLQuery do some housekeeping
(such as transactions, preparing statements, managing cursors) which can
intervene with your direct access or vice versa. The effect doesn't have to
be immediate.

> 
> DeleteSQL
> InsertSQL
> UpdateSQL
> 

FPC didn't implement the Delphi Ttable. These propereties allow a programmer
to create a control that acts like a TTable with the advantage of having far
much more control. Downside is the more complex setup.

> I also don't know how to use the IndexDefs, IndexFieldNames, 
> or IndexName properties. Every permutation of guesses I've 
> tried either get deleted by the IDE, or don't actually change 
> anything that I can detect. I have found some documentation 
> on 
> http://free-pascal-general.1045716.n5.nabble.com/New-local-ind
> exes-support-for-sqldb-td2817867.html
> which might shed some light on those parameters.
> 

To make it simple, when a select query is executed, all data is retrieved,
at some point, in memory. Using "local" indices to the data in memory allow
for quick reordering of data in a DBGrid for example. That is how clicking
on a column header to reorder the data is working without retrieving data
from the db backend.

> A search on TSQLQuery.DeleteSQL brought up a reference to 
> http://community.freepascal.org:10000/bboards/message?message_
> id=275621&forum_id=24081
> which partially answers a question *I* posed 4 years ago on 
> that topic (that was for a prior iteration of this same 
> program, which I managed to kluge together, then not look at 
> again until last week). I see that the wiki was updated to 
> include a mention of the fact that DeleteSQL, InsertSQL, and 
> UpdateSQL can take parameters -- which is helpful information 
> (parameters can help performance), but the performance hit 
> that I am seeing is happening in a simple lookup (for which 
> the transaction stuff is not needed). Here's the code:
> 
>  qClients.Close;
>  qClients.SQL.Text:=sql;
>  qClients.Open;
> 
> That part is pretty much in the form that you suggested. The 
> Close is taking 1 to 2 seconds, and the Open is taking a 
> similar amount of time. 

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.

Ludo