Increase speed of database on cdrom

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Increase speed of database on cdrom

Uwe Steinmann
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).

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.

Would it help to create one table at a time?

  Uwe
--
  MMK GmbH, Universitaetsstr. 11, 58097 Hagen
  [hidden email]
  Tel: +2331 840446    Fax: +2331 843920

signature.asc (196 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Increase speed of database on cdrom

Ulrik Sandborg-Petersen
Hi Uwe,

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).
>
>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.
>
>Would it help to create one table at a time?
>
>  Uwe
>  
>

I don't know if this will help, but you could try VACUUM'ing the
database before shipping.  Have you tried that?

Regards,

Ulrik Petersen

Reply | Threaded
Open this post in threaded view
|

Re: Increase speed of database on cdrom

Christian Smith
In reply to this post by Uwe Steinmann
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
    / \
Reply | Threaded
Open this post in threaded view
|

Re: Increase speed of database on cdrom

Uwe Steinmann
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
  [hidden email]
  Tel: +2331 840446    Fax: +2331 843920

signature.asc (196 bytes) Download Attachment