DBI DBM Slow - Is a ramdisk the answer?

Discussion in 'Perl Misc' started by burlo.stumproot@gmail.com, Mar 15, 2005.

  1. Guest

    Using WinXP Activestate 5.8.4

    I'm using dbi:DBM to analyze some data. I read it from a file,
    process it some and then put it in a dbi:DBM database. I run a couple
    of sql's on it and save the result for later use.

    But I'm finding it rather slow. 18 sec(*) to do 126 inserts and I'm
    planning to do about 1000 inserts and then 5-10 selects when I'm
    working on live data.

    I moved the database to a ramdisk(**) and it took about 1,4 seconds.
    (I'm not planning to save the database after each run.)


    But then I got to thinking, is this the best way? Perhaps it's
    possible to tell dbi:DBM not to write it's data to file, or at least
    delay writing until I have *a big* chunk of data or until I
    $dbh->disconnect;

    Googling and reading the docs made me no wiser, and I have not had
    enough guts to start reading the source yet.


    So my question is this:

    Can I speed up dbi:DBM without using a ramdisk?
    If so how?

    Or should I use somthing other than DBI:DBM for this task?


    /PM

    (*)
    $dbh = DBI->connect('dbi:DBM:type=DB_File;mldbm=Storable');
    $dbh->{f_dir} = 'tmp_db';

    (**)
    I'm currently using this, since I have an ancient app that
    demands it's data from A:

    Virtual Floppy Drive (VFD) for Windows NT platform.
    http://chitchat.at.infoseek.co.jp/vmware/vfd.html
    , Mar 15, 2005
    #1
    1. Advertising

  2. Bob Walton Guest

    wrote:

    > Using WinXP Activestate 5.8.4
    >
    > I'm using dbi:DBM to analyze some data. I read it from a file,
    > process it some and then put it in a dbi:DBM database. I run a couple
    > of sql's on it and save the result for later use.
    >
    > But I'm finding it rather slow. 18 sec(*) to do 126 inserts and I'm
    > planning to do about 1000 inserts and then 5-10 selects when I'm
    > working on live data.
    >
    > I moved the database to a ramdisk(**) and it took about 1,4 seconds.
    > (I'm not planning to save the database after each run.)
    >
    >
    > But then I got to thinking, is this the best way? Perhaps it's
    > possible to tell dbi:DBM not to write it's data to file, or at least
    > delay writing until I have *a big* chunk of data or until I
    > $dbh->disconnect;

    ....

    > So my question is this:
    >
    > Can I speed up dbi:DBM without using a ramdisk?
    > If so how?
    >
    > Or should I use somthing other than DBI:DBM for this task?

    ....

    Perhaps DBI with DBD::RAM would be helpful? It should be able to
    do just what you indicate you want to do.

    --
    Bob Walton
    Email: http://bwalton.com/cgi-bin/emailbob.pl
    Bob Walton, Mar 15, 2005
    #2
    1. Advertising

  3. wrote in news::

    >
    > Using WinXP Activestate 5.8.4
    >
    > I'm using dbi:DBM to analyze some data. I read it from a file,
    > process it some and then put it in a dbi:DBM database. I run a couple
    > of sql's on it and save the result for later use.
    >
    > But I'm finding it rather slow. 18 sec(*) to do 126 inserts and I'm
    > planning to do about 1000 inserts and then 5-10 selects when I'm
    > working on live data.


    Without sample code and data, it is very hard to make specific
    suggestions.

    ....

    > But then I got to thinking, is this the best way? Perhaps it's
    > possible to tell dbi:DBM not to write it's data to file, or at least
    > delay writing until I have *a big* chunk of data or until I
    > $dbh->disconnect;


    Then you should turn AutoCommit off if you have it on. See

    http://search.cpan.org/~timb/DBI-1.48/DBI.pm#Transactions

    > So my question is this:
    >
    > Can I speed up dbi:DBM without using a ramdisk?


    I do not know.

    > Or should I use somthing other than DBI:DBM for this task?


    I have been using SQLite a lot lately. I have not felt the need to
    benchmark anything because the performance (even in conjunction with
    Class::DBI) has been more than adequate for my needs. It also natively
    supports multiple tables with multiple columns in the database driver
    rather than the simple (key,value) tables of DBM.

    The fact that you are using Storable for each row probably means a lot
    store/retrieve calls when you access columns.

    > (**)
    > I'm currently using this, since I have an ancient app that
    > demands it's data from A:


    I am confused. Is the database you are accessing with DBI DBD-DBM on the
    floppy disk? Then you have just found the bottleneck.

    Even if you use SQLite, the floppy access speed will be the determining
    factor in how fast your application runs, and that ain't fast.

    Sinan.
    A. Sinan Unur, Mar 15, 2005
    #3
  4. wrote:

    >
    > Using WinXP Activestate 5.8.4
    >
    > I'm using dbi:DBM to analyze some data. I read it from a file,
    > process it some and then put it in a dbi:DBM database. I run a couple
    > of sql's on it and save the result for later use.
    >
    > But I'm finding it rather slow. 18 sec(*) to do 126 inserts and I'm
    > planning to do about 1000 inserts and then 5-10 selects when I'm
    > working on live data.


    DBI isnt slow, your DBMS is.

    Using indexes(primary key, secondary), integrity constraints (eg checking
    existence of foreign keys),or insert triggers can slow inserts down.

    gtoomey
    Gregory Toomey, Mar 16, 2005
    #4
  5. Guest

    Fabian Pilkowski <-marburg.de> writes:

    > * Bob Walton wrote:
    > > wrote:
    > > >
    > > > I moved the database to a ramdisk(**) and it took about 1,4 seconds.
    > > > (I'm not planning to save the database after each run.)
    > > >
    > > > So my question is this:
    > > >
    > > > Can I speed up dbi:DBM without using a ramdisk?
    > > > If so how?
    > > >
    > > > Or should I use somthing other than DBI:DBM for this task?

    > >
    > > Perhaps DBI with DBD::RAM would be helpful? It should be able to
    > > do just what you indicate you want to do.

    >
    > A few days ago I've read in comp.lang.perl.misc [1] that DBD::RAM isn't
    > the most recent module for this (the latest version on CPAN is from May
    > 2000, nearly 5 years ago). Look at DBD::AnyData which docs contains a
    > section named "Working with in-memory tables".
    >
    > http://search.cpan.org/~jzucker/DBD-AnyData-0.08/AnyData.pm


    I'll have a look at this.
    Thank you.

    /PM
    , Mar 16, 2005
    #5
  6. Guest

    "A. Sinan Unur" <> writes:

    > wrote in news::
    >
    > >
    > > Using WinXP Activestate 5.8.4
    > >
    > > I'm using dbi:DBM to analyze some data. I read it from a file,
    > > process it some and then put it in a dbi:DBM database. I run a couple
    > > of sql's on it and save the result for later use.
    > >
    > > But I'm finding it rather slow. 18 sec(*) to do 126 inserts and I'm
    > > planning to do about 1000 inserts and then 5-10 selects when I'm
    > > working on live data.

    >
    > Without sample code and data, it is very hard to make specific
    > suggestions.
    >
    > ...
    >
    > > But then I got to thinking, is this the best way? Perhaps it's
    > > possible to tell dbi:DBM not to write it's data to file, or at least
    > > delay writing until I have *a big* chunk of data or until I
    > > $dbh->disconnect;

    >
    > Then you should turn AutoCommit off if you have it on. See
    >
    > http://search.cpan.org/~timb/DBI-1.48/DBI.pm#Transactions


    Unfortunatly this does not work for me with dbi::DBM
    <CODE>
    use DBI;
    my $dbh = DBI->connect('dbi:DBM:type=DB_File;mldbm=Storable',
    undef,
    undef,
    { RaiseError => 1, AutoCommit => 0 });
    $dbh->disconnect;
    exit;
    </CODE>

    Gives this error:
    Can't disable AutoCommit at c:/Perl/site/lib/DBD/File.pm line 246.


    > > So my question is this:
    > >
    > > Can I speed up dbi:DBM without using a ramdisk?

    >
    > I do not know.
    >
    > > Or should I use somthing other than DBI:DBM for this task?

    >
    > I have been using SQLite a lot lately. I have not felt the need to
    > benchmark anything because the performance (even in conjunction with
    > Class::DBI) has been more than adequate for my needs. It also natively
    > supports multiple tables with multiple columns in the database driver
    > rather than the simple (key,value) tables of DBM.


    I'm using or rather was using MLDBM, it adds multi-column support.

    I tried SQLite it's a bit slower (20s)if I use AutoCommit but I get
    1.4 seconds if I turn AutoCommit off and only commit when I'm done.
    And if I rollback I get an extra .2 seconds :)

    And it's clear that I should have used SQLite from the start.


    > > (**)
    > > I'm currently using this, since I have an ancient app that
    > > demands it's data from A:

    >
    > I am confused. Is the database you are accessing with DBI DBD-DBM on the
    > floppy disk? Then you have just found the bottleneck.


    No, it's a totally different application, but this is why I had a ramdisk
    installed in the first place. My computer don't have a floppy drive.

    /PM
    , Mar 16, 2005
    #6
  7. Guest

    Gregory Toomey <> writes:

    > wrote:
    >
    > >
    > > Using WinXP Activestate 5.8.4
    > >
    > > I'm using dbi:DBM to analyze some data. I read it from a file,
    > > process it some and then put it in a dbi:DBM database. I run a couple
    > > of sql's on it and save the result for later use.
    > >
    > > But I'm finding it rather slow. 18 sec(*) to do 126 inserts and I'm
    > > planning to do about 1000 inserts and then 5-10 selects when I'm
    > > working on live data.

    >
    > DBI isnt slow, your DBMS is.


    I already know what made it slow, the many writes to disk using Storable.
    Thats why it got about 10 times faster when I was using a ramdisk.

    But I'm going to use SQLite, it allows me to turn off AutoCommit
    and this gives me a significant performace boost.
    (See reply to Sinan for details)

    /PM
    , Mar 16, 2005
    #7
  8. Guest

    Jim Gibson <> writes:

    > In article <>, <>
    > wrote:
    >
    > > Using WinXP Activestate 5.8.4
    > >
    > > I'm using dbi:DBM to analyze some data. I read it from a file,
    > > process it some and then put it in a dbi:DBM database. I run a couple
    > > of sql's on it and save the result for later use.


    <snip>

    > It sounds like you are creating a persistent database on disk (or ram
    > disk) and using it as a temporary data set, extracting data, writing
    > out the results, then deleting the database when you are done. If that
    > is the case, why are you using a database at all. You could easily save
    > 1000 records in memory using arrays or hashes or some combination of
    > the two. Perform whatever manipulation you need on that data, write out
    > the results, and quit. That should be a lot faster than the overhead of
    > an RDBMS. Your problem would be translating the SQL manipulations into
    > Perl expressions and statements.


    Yes and that's what I'm trying to avoid. Why go thrugh the trouble of
    creating a set of funktions working on hashes to (probably badly) emulate
    SQL when I can use SQL.

    I actually started with hashes and wrote a couple of snippets to collect
    the data I wanted when it started to look like:
    select a,b from foo where a > b and a between d and e
    So I thought why bother and went with the database/SQL approach.

    Speed is not critical, having it take 1-2 seconds is ok(*), 18s was not.

    /PM

    (*)
    Using SQLite with AutoCommit off
    , Mar 16, 2005
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. ulloa
    Replies:
    1
    Views:
    526
    Juha Laiho
    Jul 22, 2004
  2. Oliver

    Some RAMdisk solution?

    Oliver, Oct 29, 2003, in forum: C++
    Replies:
    1
    Views:
    335
    Oliver
    Oct 30, 2003
  3. sonet
    Replies:
    0
    Views:
    117
    sonet
    Oct 27, 2005
  4. sonet
    Replies:
    2
    Views:
    121
    Dr.Ruud
    Jun 9, 2006
  5. Bill H

    Perl and a Ramdisk

    Bill H, Nov 30, 2007, in forum: Perl Misc
    Replies:
    4
    Views:
    106
    Martijn Lievaart
    Dec 2, 2007
Loading...

Share This Page