[Lazarus] RE : SQLite performance problem. Simple query takes too long. (SOLVED)

[Howard Lee Harkness]

Ok, I found a fix, although I'm not really sure that I have fixed the
problem in the best way. What I did was to close all of the dependent
queries while doing the incremental search until the user selects a
client entry in the main dbgrid. Now the incremental search is as fast
as it was in PostgreSQL.

Although this dialog did not point directly to a solution, I learned a
lot, which eventually enabled me to find the bottleneck.

If any of you should think of a better way, please let me know.
Otherwise, I think it's good enough to deliver tomorrow. I spoke with
the customer and explained the slight difference in behavior, and he
thinks that is an improvement, because it will help prevent
accidentally entering phone #, address, etc. for the wrong client.

Thanks for the help!

On Fri, Jul 29, 2011 at 2:21 PM, Howard Lee Harkness
<howard.lee.harkness at gmail.com> wrote:
> 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
>



-- 
Howard Lee Harkness
(214) 390-4896