Christian, Ulrik, Thanks for the very informative answer. I'll try VACUUM first.
Uwe On Tue, May 31, 2005 at 12:37:44PM +0100, Christian Smith wrote: > 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 > / \ -- MMK GmbH, Universitaetsstr. 11, 58097 Hagen [EMAIL PROTECTED] Tel: +2331 840446 Fax: +2331 843920
signature.asc
Description: Digital signature
