Front page | perl.dbi.users |
Postings from August 2006
Re: DBD-DBM too slow
Thread Previous
|
Thread Next
From:
Jared Still
Date:
August 24, 2006 07:52
Subject:
Re: DBD-DBM too slow
Message ID:
1156431457.31448.22.camel@poirot
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
Thread Previous
|
Thread Next