[Lazarus] TQuery.Open 100x slower than same code using PostrgreSQL

[Howard Lee Harkness]

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
<howard.lee.harkness at gmail.com> 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
> <howard.lee.harkness at gmail.com> 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
>> <michael at freepascal.org> 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
>>> Lazarus at lists.lazarus.freepascal.org
>>> 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