Now I'm seeing a very slow response to opening the main table, even with no dependent tables.
I set the SQL in the TQuery, then do an open. It takes 4 to 6 seconds to populate the TDBGrid. When I was using PostgreSQL with the same database, it was instant. qClients.Close; qClients.SQL.Text:='select * from clients where lastname is not NULL order by lastname,firstname,middlename'; qClients.Open; // takes 4 to 6 seconds here There has GOT to be something I'm not understanding. I thought SQLite was going to speed things up, not slow them down. On Wed, Aug 3, 2011 at 11:10 AM, Howard Lee Harkness <[email protected]> wrote: > I have added some code to only open a query if the corresponding > tabsheet is selected, which I think speeds things up sufficiently. > Still would like to know why a TQuery component would take 2 or 3 > seconds to open, even on a slow machine. > > Here is the SQL from my address table query: > > select * from addresses > where ID = :ID > order by atype,asof > > "atype" is a string (home, work, etc.) and "asof" is a date. > > The SQL for the other two queries is very similar. > > In the synchronization procedure to set the :ID value for each query: > > closeDependentQueries; > CurrentClientID := qClients.FieldByName('ID').AsInteger; > qAddresses.Params.ParamByName('ID').Value := CurrentClientID; > qCases.Params.ParamByName('ID').Value := CurrentClientID; > qPhones.Params.ParamByName('ID').Value := CurrentClientID; > > I have not yet played around with the foreign key stuff, and I'm not > really sure how to enable & use foreign keys in SQLite, anyway. > > On Mon, Aug 1, 2011 at 4:21 PM, Howard Lee Harkness > <[email protected]> wrote: >> I have a dummy database and complete program I can let you look at if >> you would like. Nothing particularly proprietary about the code; it's >> a trivial application. The database is the sensitive part, and I have >> it dummied out. >> >> I posted the schema a few days ago, and I've added a zipcode table to >> that. I had initially thought I had solved the performance problem, >> but when I got to the client, and ran it on his machine (which is >> about 100x slower than mine). I had not noticed the lag on my machine, >> but it was really awful on his. >> >> The whole project is in a 1.6Mb zip, expands to just over 7Mb. Would >> you like me to email it directly to you, or post it? >> >> On Mon, Aug 1, 2011 at 1:56 PM, Michael Van Canneyt >> <[email protected]> wrote: >>> >>> >>> On Mon, 1 Aug 2011, Howard Lee Harkness wrote: >>> >>>> I thought I had fixed the performance problem, but it is still there. >>>> What I am doing is opening 3 dependent queries after selecting a >>>> record in the main table. It takes 8 or 9 seconds to do the 3 calls to >>>> TQuery.Open. Any clues as to what I might be doing wrong? The sample >>>> queries done from sqlite3.exe return instantaneously, so I am >>>> concluding that I have not set something up correctly in the LCL. >>> >>> Can you provide test data and a sample program ? Without that, it's very >>> hard to give recommendations. >>> >>> The architecture of the sqlite components is not fundamentally different >>> from >>> the other database, and those do work instantaneously. >>> >>> Michael. >>> >>> -- >>> _______________________________________________ >>> Lazarus mailing list >>> [email protected] >>> http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus >>> >> >> >> >> -- >> Howard Lee Harkness >> (214) 390-4896 >> > > > > -- > Howard Lee Harkness > (214) 390-4896 > -- Howard Lee Harkness (214) 390-4896 -- _______________________________________________ Lazarus mailing list [email protected] http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
