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

[Martin Schreiber]

Am Samstag 06 August 2011, 23:41:03 schrieb 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
> 
In MSEgui with your 10000 records test DB:

"
select * from clients where lastname is not null order by 
lastname,firstname,middlename;
"
0.029s, returning 10 records, see attachment.

"
select * from clients where lastname is null order by 
lastname,firstname,middlename;
"
0.716s, returning 9990 records.

"
select * from clients where lastname is null;
"
0.371s, returning 9990 records.

"
select * from clients;
"
0.331s, returning 10000 records.

The times include fetching the whole resultset (packetrecords = -1) and the 
population of the grid.
 
Martin
-------------- next part --------------
A non-text attachment was scrubbed...
Name: sqliteperf.png
Type: image/png
Size: 20481 bytes
Desc: not available
URL: <http://lists.lazarus-ide.org/pipermail/lazarus/attachments/20110807/831447be/attachment-0003.png>