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!
Howard Lee Harkness wrote 49 lines
On Fri, Jul 29, 2011 at 1:42 PM, Ludo Brands 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
