Date: Thu, 8 Nov 2007 15:54:35 +0500
From: [EMAIL PROTECTED]
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Disk caching impacts performance.
Try this, use mmap
(i assume u'r db is readonly)this is much faster and
better than ifstream read. Also ifstream read can keep the data in cache as
long as no other serious
i/o occurs.
U need to accept it as we work with Virtual Mem or
write your own FileSystem which is mem based and short circuits os calls.
Sqlite 3.5x has good support for such ext.
regrads
ragha
******************************************************************************************
This email and its attachments contain confidential information from HUAWEI,
which is intended only for the person or entity whose address is listed above.
Any use of the information contained herein in any way (including, but not
limited to, total or partial disclosure, reproduction, or dissemination) by
persons other than the intended recipient(s) is prohibited. If you receive this
e-mail in error, please notify the sender by phone or email immediately and
delete it!
*****************************************************************************************
----- Original Message -----
From: Julien Renggli
Date: Thursday, November 8, 2007 4:15 pm
Subject: [sqlite] Disk caching impacts performance.
Hello,
I'm currently working on a project which is very likely to use
SQLite as
data storage. Since performance is important for us, I already
found on
the SQLite website many ways to optimise the code (always working in
transactions where possible, using a page size of 4096 since it's
running on the Windows platform, using integers primary keys, ...).
ButI have one problem that I "solved" in an unorthodox way; it
works, but
maybe you have a better solution than mine?
I've been doing some test with a ~100 MB database, in which I have
three
tables: one for structured data (Objects, 2000 entries), one for the
blobs we have to store (ObjectBlobs ID INTEGER PRIMARY KEY, Data BLOB)
(8000 entries), and one which binds the structured data and the blobs
(8000 entries). As you can imagine, each Object has 4 blobs linked to
it; the blobs can be quite large (let's say up to 1 MB).
My (C++) application just has to read the table "Objects", and one of
the Blobs for each Object.
Now the first time I run my application, it takes quite a long time
(30s) to load the blobs. But if I re-run the app, it only takes 1s to
load them. It's clearly a disk caching issue: if I copy huge files to
the disk between two runs, it takes again 30s to load the blobs (i.e.
the DB is no more in the disk cache). Profiling the application
indicates sqlite::winRead() is the bottleneck.
I then had the following idea: SQLite is probably reading the file
randomly, depending on where the data lies. If I can force the DB
to be
cached, everything should be fine. So before connecting the
database, I
first read it sequentially (using a C++ ifstream) until the end of
file.
It perfectly solves the database problem, even though I still
notice a
difference (3s to read the file on 1st run, 0.2s later). But 3s is OK
where 30s was worrying me.
I hope I explain the situation clear enough, and ask you now: is it
theonly way to do it? I find the trick a bit nasty and don't like
it; maybe
I missed something? Before you ask: I tried to VACUUM the DB, it only
reduced the timings to 16s, which was still bad for our requirements.
Tests with a larger DB (it can get much bigger than my example) and on
different machines tend to confirm my theory.
Thanks in advance (and a big thank for SQLite which is really nice and
easy to use !),
Julien Renggli
P.S.: Some technical informations:
- sqlite v.3.3.16 (we will upgrade to the latest version later), C API
- Windows XP SP2
- Timings on Pentium 4 3.4GHz, 2GB RAM
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
