Slow query after reboot

[Geoff Simonds]

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.

[Jay Sprenkle]

Post by Geoff Simonds
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.

[Geoff Simonds]

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.

Post by Jay Sprenkle

Post by Geoff Simonds
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.

[Robert Simpson]

----- Original Message -----

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

[Geoff Simonds]

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?

Post by Robert Simpson

Post by Geoff Simonds
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

[Robert Simpson]

----- Original Message -----

Post by Geoff Simonds
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

[Michael Sizaki]

Post by Geoff Simonds
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

Post by Geoff Simonds

Post by Robert Simpson

Post by Geoff Simonds
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

[Geoff Simonds]

Thanks for the info and suggestions Michael. I will give this a try.

Post by Michael Sizaki

Post by Geoff Simonds
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.
- 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

Post by Geoff Simonds

----- Original Message ----- From: "Geoff Simonds"

Post by Geoff Simonds
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

[Clark Christensen]

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 <geoff-***@public.gmane.org>
To: sqlite-users-CzDROfG0BjIdnm+***@public.gmane.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?

Post by Robert Simpson

Post by Geoff Simonds
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

[Teg]

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 <geoff-***@public.gmane.org>
CC> To: sqlite-users-CzDROfG0BjIdnm+***@public.gmane.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?

Post by Robert Simpson

Post by Geoff Simonds
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

[Eric Bohlman]

Post by Geoff Simonds
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?

[Chris Schirlinger]

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

Post by Geoff Simonds
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.

[Geoff Simonds]

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.

Post by Chris Schirlinger
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

Post by Geoff Simonds
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.

[Bogusław Brandys]

Post by Geoff Simonds
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.

Post by Chris Schirlinger
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

Post by Geoff Simonds
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

[hgibson-CVW/MsmY5bNaa/ (Hugh Gibson)]

Post by Bogusław Brandys
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