Geoff Simonds wrote:
My table contains about 500,000 rows and 4 columns, not all that much
data. The overall size of the db file is 35 mb. Does 15 - 20 seconds
sound right to load from disk into memory?
Yes it does. The problem is, that your query is probably
not reading sequentially from disk. Therefore the disk
head has to jump forth and back. Once the entire database
in in the OS disk cache, queries are fast, because it's
only CPU bound and not disk bound anymore.
To speedup the initial access, you can:
- read the entire file once before you start your query
- run the following query (once)
select count(last_column) from big_table;
this will touch each record in a kind of optimal order
- if that is still slow, try VACUUM on your database. This
brings the records in a natural order.
I have an application that deals also with about 500,000
and the database size is about 100mb. Queries on a "cold"
database are extremely slow....
Michael
Robert Simpson wrote:
----- Original Message ----- From: "Geoff Simonds" <[EMAIL PROTECTED]>
The app is running on Windows XP machines and I assume that disk
files are cached. The strange thing is that the time it takes for
the initial read into RAM after install and first use is
significantly shorter than after a reboot. For example, if you just
installed the app and start it, the first time you do a query you see
results in about 2 seconds. Subsequent queries come back much almost
instantaneously. If the user reboots the machine or waits until the
next day and performs the same query, it now takes about 15 seconds.
After the 15 seconds, results come back and subsequent queries are
instantaneous. I am not sure if this has anything to do with it but
the app is a Deskband that lives in the taskbar on windows.
That's not so strange, really. When the app is installed (along with
the database), the Windows disk cache probably has at least part of
the database file cached -- afterall it just got finished writing it.
Robert
