http://qs321.pair.com?node_id=379244


in reply to SQLite and CPU Usage

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.

Replies are listed 'Best First'.
Re^2: SQLite and CPU Usage
by PerlingTheUK (Hermit) on Aug 02, 2004 at 10:46 UTC
    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]
        Thank you,
        I just added a BEGIN and COMMIT myself. Don't ask me Why I did not see these statements in the manual! Thank you for any help and sorry for asking such stupid questions.
[reply]

In Section Seekers of Perl Wisdom