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.

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.freepascal.org/pipermail/lazarus/attachments/20110807/831447be/attachment-0001.png>

Martin Schreiber wrote

The times include fetching the whole resultset (packetrecords = -1) and

the

population of the grid.

"
select * from clients;
"
with packetrecords = 100 -> 0.006s.

Martin

There has GOT to be something I'm not understanding. I thought SQLite was going to speed things up, not slow them down.

Clearly you are not providing enough information to reproduce the problem. So far it has all been guess work for people trying to help. Fe. your initial request for help "forgot" to mention that there were several dependant queries. If you upload the project we can take a aren't needed. Populating with random data is easy. Seen the size of your project (I guess this mailing list has attachment size limits) upload it somewhere and post the link.

Ludo

After several hours (over a period of a couple of months) of battling this problem, I finally found the cause. I was not looking in the right place. Turns out that I had a dependent query that were being opened after the client TQuery. Actually, there were 3 different dependent queries, only one of which was causing the problem. On my system, I never ran into the problem, primarily because my testing was not comprehensive enough -- and the attorney ran into it almost immediately every time he used it (and he just complained that it was too slow). I finally just recorded *exactly* what he was doing, and then easily duplicated the problem.

The problem query was the "cases" table, which has a client ID foreign key, an OID (primary key), a date and a large text field. PostgreSQL did not have any slowdown at all with this database schema, so I was surprised to see that SQLite took two orders of magnitude longer to open that same table.

What I did was simply close the case table whenever doing the incremental search. That works, although it seems to be a bit of a hack.