PerlingTheUK has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks,
I have recently started, including SQLite into my application. I do not want to use a standalone DBMS-server presently, but hope to use this to be able to change this soon if required.
I am aware that SQLite has no intention to be a very fast database, but what troubles me is that perl when excuting statements only runs with very limited CPU usage (1 to 5 pc at most). As some of my statements require 40 seconds CPU-time which is fine for me, in general, using only a maximum of 5 pc CPU adds up to about 20 minutes easily which is not acceptable. The remaining processor usage is assigned to the System Idle Process.
Does anyone out here know if there is a way to speed up SQLite?
I am using AS Perl 5.8.4(810).

Thank you for any help.

Replies are listed 'Best First'.
Re: SQLite and CPU Usage
by BrowserUk (Patriarch) on Aug 02, 2004 at 11:02 UTC

    There are several things you can try that should speed things up.

    1. Disable autocommit and commit your inserts in batches.

      See the DBI docs.

    2. Increase the number of pages of cache used using PRAGMA <default_>cache_size = nnnnn;

      The default is set to 2000. Increasing this to 10,000 seems to work well.

      See http://www.hwaci.com/sw/sqlite/lang.html#pragma

    3. Turn of the synchronous flag using PRAGMA <default_>synchronous = 0;.

      See the link above. Access the risks according the value of your data and the likelyhood of hardware failure on your system.

    Examine what is said, not who speaks.
    "Efficiency is intelligent laziness." -David Dunham
    "Think for yourself!" - Abigail
    "Memory, processor, disk in that order on the hardware side. Algorithm, algorithm, algorithm on the code side." - tachyon
[reply]
[d/l]
[select]
Re: SQLite and CPU Usage
by beable (Friar) on Aug 02, 2004 at 10:33 UTC
    You need to identify the bottleneck. For example, is the I/O (disk) system running at 100% speed? If so, you won't be able to raise your CPU usage because you'll be waiting for I/O. (Unless you have some CPU-intensive task you can perform while waiting for I/O.) On the other hand, if I/O is not running at maximum capacity, and CPU is at 5%, then there must be something else slowing down your program.
[reply]
      Basically the hard drive is slowing me down, The problem is that SQLite does not support INSERT statements with a list of values (rows), so it seems to read after each statement. This is where I am looking for improvement at the moment but I have not really any idea how to start so.
      It seems SQLite reads the whole database files each time I execute an INSERT statement. Is there a way to reduce this? My tables have a primary key and therefore it should be eas to only check if this is existing.
      Would it be useful to include Indices for this? My table has no foreign keys and is only a list of names for an abbreviation which is the key.
[reply]

        Turn off AutoCommit and manually commit your inserted rows ever 100 rows or so. The code is Class::DBI-ish but should be easily adapted to whatever your needs are:

        package ASE::DB;
use strict;
use base 'Class::DBI';

__PACKAGE__->set_db( Main => "dbi:SQLite:dbname=d:/data/ase.sqlite",""
+,"", 
  {RaiseError => 1, AutoCommit => 0} );

        [download]

        Of course, then you have to commit at least at the end of your program run, or whenever a work unit has been processed:

            ASE::DB->db_Main->commit;

        [download]
[reply]
[d/l]
[select]
Re: SQLite and CPU Usage
by davido (Cardinal) on Aug 02, 2004 at 15:25 UTC
    Near the end of the POD for DBD::SQLite you'll find a discussion about performance. The author claims that SQLite is very fast. He does mention, however, that INSERT is not particularly fast, especially if you're committing each INSERT one by one. He gives the example of inserting 400,000 rows using transactions, and only committing every thousand rows. There are also other performance tips in that last section of the POD.

    Dave

[reply]
Re: SQLite and CPU Usage
by paulbort (Hermit) on Aug 03, 2004 at 17:58 UTC
    All of the suggestions about batch inserts are right on, I would just like to add one minor point since you mentioned indexes: If you are going to need the indexes later, it is usually faster to build the indexes after all the inserts are done, becaus this minimizes the number of times that index pages have to be re-arranged.

    --
    Spring: Forces, Coiled Again!
[reply]

Back to Seekers of Perl Wisdom