Re: [sqlite] Slow query after reboot
> What is the name of your database (with extension please) ? > There are chances that such extension is registered into Windows XP > crap called System Restore and each time on start it detect that this > file is changed and create a restore snapshot for it. List given at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sr/sr/mon itored_file_extensions.asp Hugh
Re: [sqlite] Slow query after reboot
Geoff Simonds wrote: 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. What is the name of your database (with extension please) ? There are chances that such extension is registered into Windows XP crap called System Restore and each time on start it detect that this file is changed and create a restore snapshot for it. Regards Boguslaw Brandys
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
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
Geoff Simonds wrote: The app is running on Windows XP machines Is it possible that indexing services are enabled and XP is trying to index the database file?
Re[2]: [sqlite] Slow query after reboot
Hello Clark, I don't use a virus scanner and observe the same slow initial performance. I do believe it's possible it's the disk cache though, my disks are quite fast (15K SCSI). It acts more like some initialization phase in the DB than it does disk IO delay. C Thursday, January 19, 2006, 1:57:57 PM, you wrote: CC> Also possible, maybe even likely, is the user's anti-virus CC> software is scanning the DB file when the app is first opened. CC> 35MB is a big file for A-V to have to scan. CC> You or they may be able to configure the A-V to ignore the DB file. CC> -Clark CC> - Original Message CC> From: Geoff Simonds <[EMAIL PROTECTED]> CC> To: sqlite-users@sqlite.org CC> Sent: Thursday, January 19, 2006 7:52:55 AM CC> Subject: Re: [sqlite] Slow query after reboot CC> My table contains about 500,000 rows and 4 columns, not all that much CC> data. The overall size of the db file is 35 mb. Does 15 - 20 seconds CC> sound right to load from disk into memory? CC> 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 >> >> >> >> >> -- Best regards, Tegmailto:[EMAIL PROTECTED]
Re: [sqlite] Slow query after reboot
Also possible, maybe even likely, is the user's anti-virus software is scanning the DB file when the app is first opened. 35MB is a big file for A-V to have to scan. You or they may be able to configure the A-V to ignore the DB file. -Clark - Original Message From: Geoff Simonds <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Thursday, January 19, 2006 7:52:55 AM Subject: 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
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
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
- Original Message - From: "Geoff Simonds" <[EMAIL PROTECTED]> 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? I can't tell you that until the following are answered: 1. What's the query? 2. What's the database schema? Indexes? 3. Are you calling prepare/step/finalize to load the data or using some other method? 4. How are you storing the data in memory? linked lists? Pre-allocated array? The first two are the most important factors. 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
- 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.
Re: [sqlite] Slow query after reboot
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.
