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.

1/19/06, Geoff Simonds <geoff (AT) twinfires (DOT) comwrote:
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.

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:

1/19/06, Geoff Simonds <geoff (AT) twinfires (DOT) comwrote:

>
>>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.
>
>
>

Message
From: "Geoff Simonds" <geoff (AT) twinfires (DOT) com>

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

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:

Message From: "Geoff Simonds" <geoff (AT) twinfires (DOT) com>
>
>>
>
>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
>
>
>
>
>

Message
From: "Geoff Simonds" <geoff (AT) twinfires (DOT) com>

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

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. the entire database
in in the S 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:

>Message From: "Geoff Simonds" <geoff (AT) twinfires (DOT) com>
>>

>>
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
>>
>>
>>
>>
>>

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. the entire database
in in the S 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:
>>
Message From: "Geoff Simonds"
<geoff (AT) twinfires (DOT) com>

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 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?