On Tue, 31 May 2005, Uwe Steinmann wrote:
>Hi,
>
>I've created a sqlite database with about 140 MB filesize which is
>going to be shipped on cdrom. The database is read by a java
>application. I made some rought speed comparision between the database
>being on cdrom and on hard disk. On startup a tree is filled
>with data from two tables. When reading the data from disk it takes
>about 5 sec., but reading it from cdrom it takes 100 sec. (the cdrom
>is a less than 1 year old modell).
If you access a CD-ROM in anyway other than sequentially, you will
decimate performance due to the very high seek times of CD-ROMs. Hard
disks use relatively fast voice coil actuators for head positioning,
resulting in random seek times <4ms on high end SCSI disks, and <9ms on
average new IDE disks. A CD-ROM has a random seek time in the order of
100ms, which would certainly account for the order of magnitude difference
in performance you're seeing.
>
>I wonder if there is any way to speed up reading the data from cdrom.
>One of my ideas is to change the way the database is created in order
>to place data in a particular table in blocks near to each other.
>Currently, all tables are filled in parallel, meaning that records
>of a particular table are inserted during the whole database creation.
>I suspect this leads to datablocks spilled over the whole file which
>increases access time.
Tips for performance in such circumstances might include:
- VACUUM the database before putting on the CD image. This makes all
tables and indexes sequential in the database file.
- When accessing more than a few rows of a table, disable indexed walking
of the table to prevent SQLite interveaving access between the index and
the table (thus inducing slow seeks from the CD-ROM.)
- Create a temporary database in memory or in a temporary HD database, and
prime it from the CD-ROM image.
If joining data from multiple tables, you may be lucky if you can join on
a non-indexed columns, as SQLite may read in the whole table in one go and
sort it in a temporary table before the join, but I'm not sure.
>
>Would it help to create one table at a time?
Not really. VACUUM the database has the same effect.
>
> Uwe
>
Christian
--
/"\
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
