Re: DBD-DBM too slow

On Wed, 2006-08-23 at 11:23 -0700, ManKyu Han wrote:

Is there some optimization that I should do?

Yes

#####################################
# This will populate Table
# mkUtil is nothing but collection of simple functions like random number generator
# and printing time and messages..
####################################3
use DBI;
use mkUtil;
my $dbh = DBI->connect('dbi:DBM:');
$dbh->{RaiseError} = 1;

my (@sql_c, @sql_i);
my $id = 0;
mkUtil::printTime( "DBM: Start Generating SQL Command String");
foreach my $cid ('aaaa'...'gzzz'){
push(@sql_c, "INSERT INTO dbmChar (id, input) VALUES ('$cid', 'DummyTest')");
push(@sql_i, "INSERT INTO dbmInt (id, input) VALUES ($id, 'DummyTest')");
$id++;
}
mkUtil::printTime( "DBM: Finished Generating SQL Command String");

So, you've just created a few thousand SQL statements that you intend to
parse.

The DBA's nightmare begins.

my $count = 0;
my $startTime = mkUtil::printTime( "DBM: INT Insert Start");
foreach my $sql (@sql_i){
my $sth = $dbh->prepare($sql);
$sth->execute;
$count++;
if($count%2000 eq 0){
mkUtil::printTime("$count");
}
}

Arggh. This loop has just parsed thousands of of SQL statements in a
loop.

Writing code in this manner will absolutely destroy database
performance. It doesn't matter which database.

The DBA's nightmare is complete.

Read the DBI docs to learn how to use bind variables.
Parse once, execute many.

There are many examples available, just google for them.

Jared


.