DBI DBM Slow - Is a ramdisk the answer?

[burlo.stumproot]

Using WinXP Activestate 5.8.4

I'm using dbiBM to analyze some data. I read it from a file,
process it some and then put it in a dbiBM 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 dbiBM 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 dbiBM without using a ramdisk?
If so how?

Or should I use somthing other than DBIBM for this task?


/PM

(*)
$dbh = DBI->connect('dbiBM: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

 

[Bob Walton]

Using WinXP Activestate 5.8.4

I'm using dbiBM to analyze some data. I read it from a file,
process it some and then put it in a dbiBM 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 dbiBM 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 dbiBM without using a ramdisk?
If so how?

Or should I use somthing other than DBIBM 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. Sinan Unur]

Using WinXP Activestate 5.8.4

I'm using dbiBM to analyze some data. I read it from a file,
process it some and then put it in a dbiBM 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 dbiBM 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 dbiBM without using a ramdisk?

I do not know.

Or should I use somthing other than DBIBM 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:BI) 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.

 

[Gregory Toomey]

Using WinXP Activestate 5.8.4

I'm using dbiBM to analyze some data. I read it from a file,
process it some and then put it in a dbiBM 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

 

[burlo.stumproot]

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

 

[burlo.stumproot]

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

...


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:BM
<CODE>
use DBI;
my $dbh = DBI->connect('dbiBM: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.

I do not know.


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:BI) 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 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

 

[burlo.stumproot]

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

 

[burlo.stumproot]

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