Re: [sqlite] Slow query after reboot
Thanks to everyone for all the help on this problem. I am going to try creating a new thread to touch the tables at startup. Chris Schirlinger wrote: We have the same issue, to get around it we fire a thread when the program starts, intelligently "touching" every table that the user is likely to access (As Michael Sizaki already mentioned a select count(last_column) from big_table; will do it) Since a user is very unlikely to run a program and start doing anything complex within the first 10 to 20 seconds, by the time they DO try and run a query, it is the "second time" the query has been run and this we get the benefit of the Windows XP cache and thus the fast speed I have created a client application that is always running on a users desktop. The application accepts user input and then uses SQLite to perform a few simple queries against a single db file that contains 4 tables. The performance is fantastic after the initial install and normal usage. When the user leaves for the night and tries a query in the morning, the app hangs for 20 seconds and then finally comes back with the results. If the user then duplicates the query immediately afterward, the query is almost instantaneous. In addition, if at any point the user reboots the machine and then retries the query, the same delay happens. The time is spent in the SQLiteDataReader.read() method. Does anybody have any thoughts on why this is happening? Thanks for any help.
Re: [sqlite] Slow query after reboot
Thanks for the info and suggestions Michael. I will give this a try. Michael Sizaki wrote: 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
Re: [sqlite] Slow query after reboot
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? 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
Re: [sqlite] Slow query after reboot
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. Jay Sprenkle wrote: On 1/19/06, Geoff Simonds <[EMAIL PROTECTED]> wrote: I have created a client application that is always running on a users desktop. The application accepts user input and then uses SQLite to perform a few simple queries against a single db file that contains 4 tables. The performance is fantastic after the initial install and normal usage. When the user leaves for the night and tries a query in the morning, the app hangs for 20 seconds and then finally comes back with the results. If the user then duplicates the query immediately afterward, the query is almost instantaneous. In addition, if at any point the user reboots the machine and then retries the query, the same delay happens. The time is spent in the SQLiteDataReader.read() method. Does anybody have any thoughts on why this is happening? Thanks for any help. Does your system cache disk files? The first time it reads the file into cache and after that it's read from RAM.
[sqlite] Slow query after reboot
I have created a client application that is always running on a users desktop. The application accepts user input and then uses SQLite to perform a few simple queries against a single db file that contains 4 tables. The performance is fantastic after the initial install and normal usage. When the user leaves for the night and tries a query in the morning, the app hangs for 20 seconds and then finally comes back with the results. If the user then duplicates the query immediately afterward, the query is almost instantaneous. In addition, if at any point the user reboots the machine and then retries the query, the same delay happens. The time is spent in the SQLiteDataReader.read() method. Does anybody have any thoughts on why this is happening? Thanks for any help.
