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.harkn...@gmail.com> wrote: > On Fri, Jul 29, 2011 at 1:42 PM, Ludo Brands <ludo.bra...@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 -- _______________________________________________ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus