Hello,
While breaking in a new desktop computer, I installed new versions of
the DBI, DBD-ODBC, and DBD-CSV. I have found the program below (which
takes records out of an Access DB with DBD-ODBC and puts them into a
.csv file with DBD-CSV) to start off slow (50 records/sec) and then bogs
down and runs slower and slower (2 records/sec at ~4500 records in) with
the new installs of the DBI and DBDs. However, on the old machine (with
an old DBI and old DBDs) the program was wonderfully fast processing
record 0 as fast as record 80,000 (200 - 300 records/sec). Could
somebody explain any changes that have been made in the DBI and the DBDs
that could cause the slowdowns I am seeing and the correct method to
redesign the program to again run as efficiently as before?
Thank you for your assistance!
--
Darin
------------------------------
New machine (Windows XP):
Active State Perl 5.6.1 build 635
DBI 1.37
DBD-ODBC 1.05
DBD-CSV .2002
Old machine (Windows 2000):
Active State Perl 5.6.1 build 628
DBI 1.14
DBD-ODBC .28
DBD-CSV .1025
To remove any OS questions, I installed the new DBI and DBDs on a second
Windows 2000 machine and also am experiencing the same sluggish
performance on it.
-------------------------------
#!/usr/bin/perl -w
# Purpose: convert from MS Access to csv format
# Arguments: (1) date: Format: 'MMDDYY'
use DBI;
use strict;
my $dsn = 'sibs';
my $user = 'sf';
my $pass = '';
my $table = "Mo$ARGV[0]";
# Connect to the access database: c:\calnet\Moxxxxxx.dbf
my $dbh = DBI->connect('dbi:ODBC:sibs', $user, $pass, {'RaiseError' =>
1}) or die "Database connection not made: $DBI::errsrt";
warn "Connected to Access $table table\n";
#Connect to file for output to csv data
# Connect to csv file
my $dbh2 = DBI->connect(qq{DBI:CSV:f_dir=c:/calnet})
or die "csv connection not made: $DBI::errsrt";
$dbh2->{'csv_tables'}->{'oub'} = { 'file' => 'outbound.csv'};
warn "connected to outbound csv\n";
# Delete all rows from .csv Table: outbound.csv
my $rows_deleted = $dbh2->do(q{delete from oub}) ||
die "Required Deletions not made: $DBI::errsrt";
warn "$rows_deleted rows deleted from outbound.csv\n";
$dbh->{LongReadLen} = 512 * 1024;
$dbh->{LongTruncOk} = 1;
sleep (3);
my $CBTELNUM;
my $DCALLDATE;
my $CCALLTIME;
my $NCALLDUR;
my $NCALLCOST;
my $NNETCOST;
my $CLINENUM;
my $CFROMAREA;
my $CFROMCITY;
my $CFROMSTATE;
my $CFROMZIP;
my $CFROMCNTRY;
my $CTONUM;
my $CTOAREA;
my $CTOCITY;
my $CTOSTATE;
my $CTOZIP;
my $CTOCOUNTRY;
my $CCALLTYPE;
my $CCARRIERID;
my $CRATEPERD;
my $CINTLFLAG;
my $CCALLCLASS;
my $CTIELNCODE;
my $CCALLSTAT;
my $CBILAGCYCD;
my $CACCTCODE;
my $CCARDCODE;
my $CRATECLASS;
my $CCES;
my $CAUTHFLAG;
my $CMESSTYPE;
my $CRPI;
my $CSERVICE;
my $CFLAG;
my $CPLANCODE;
my $stmt1 = $dbh->prepare(" SELECT
CBTELNUM,
DCALLDATE,
CCALLTIME,
NCALLDUR,
NCALLCOST,
NNETCOST,
CLINENUM,
CFROMAREA,
CFROMCITY,
CFROMSTATE,
CFROMZIP,
CFROMCNTRY,
CTONUM,
CTOAREA,
CTOCITY,
CTOSTATE,
CTOZIP,
CTOCOUNTRY,
CCALLTYPE,
CCARRIERID,
CRATEPERD,
CINTLFLAG,
CCALLCLASS,
CTIELNCODE,
CCALLSTAT,
CBILAGCYCD,
CACCTCODE,
CCARDCODE,
CRATECLASS,
CCES,
CAUTHFLAG,
CMESSTYPE,
CRPI,
CSERVICE,
CFLAG,
CPLANCODE FROM Mo$ARGV[0] ");
# Check if statement prepared correctly
die "ERROR: Cannot prepare statement: $DBI::errstr\n" unless (defined
$stmt1);
$stmt1->execute;
$stmt1->bind_columns(\($CBTELNUM,
$DCALLDATE,
$CCALLTIME,
$NCALLDUR,
$NCALLCOST,
$NNETCOST,
$CLINENUM,
$CFROMAREA,
$CFROMCITY,
$CFROMSTATE,
$CFROMZIP,
$CFROMCNTRY,
$CTONUM,
$CTOAREA,
$CTOCITY,
$CTOSTATE,
$CTOZIP,
$CTOCOUNTRY,
$CCALLTYPE,
$CCARRIERID,
$CRATEPERD,
$CINTLFLAG,
$CCALLCLASS,
$CTIELNCODE,
$CCALLSTAT,
$CBILAGCYCD,
$CACCTCODE,
$CCARDCODE,
$CRATECLASS,
$CCES,
$CAUTHFLAG,
$CMESSTYPE,
$CRPI,
$CSERVICE,
$CFLAG,
$CPLANCODE));
# Prepare to insert all fields into csv file
my $stmt2 = $dbh2->prepare(qq{
INSERT INTO oub ( CBTELNUM,
DCALLDATE,
CCALLTIME,
NCALLDUR,
NCALLCOST,
NNETCOST,
CLINENUM,
CFROMAREA,
CFROMCITY,
CFROMSTATE,
CFROMZIP,
CFROMCNTRY,
CTONUM,
CTOAREA,
CTOCITY,
CTOSTATE,
CTOZIP,
CTOCOUNTRY,
CCALLTYPE,
CCARRIERID,
CRATEPERD,
CINTLFLAG,
CCALLCLASS,
CTIELNCODE,
CCALLSTAT,
CBILAGCYCD,
CACCTCODE,
CCARDCODE,
CRATECLASS,
CCES,
CAUTHFLAG,
CMESSTYPE,
CRPI,
CSERVICE,
CFLAG,
CPLANCODE )
VALUES ( ?, ?, ?, ?, ?, ?, ?, ?,
?, ?,
?, ?, ?, ?, ?, ?, ?, ?,
?, ?,
?, ?, ?, ?, ?, ?, ?, ?,
?, ?,
?, ?, ?, ?, ?, ? )
}) || die "ERROR: Cannot prepare
statement: $DBI::errstr\n";
my $lines = 0;
while ( my @row = $stmt1->fetchrow_arrayref ) {
$DCALLDATE = substr($DCALLDATE, 5, 2) . '/' . substr($DCALLDATE, 8, 2)
. '/' . substr($DCALLDATE, 0, 4);
$lines++;
print "$lines\n";
$stmt2->execute(
$CBTELNUM,
$DCALLDATE,
$CCALLTIME,
$NCALLDUR,
$NCALLCOST,
$NNETCOST,
$CLINENUM,
$CFROMAREA,
$CFROMCITY,
$CFROMSTATE,
$CFROMZIP,
$CFROMCNTRY,
$CTONUM,
$CTOAREA,
$CTOCITY,
$CTOSTATE,
$CTOZIP,
$CTOCOUNTRY,
$CCALLTYPE,
$CCARRIERID,
$CRATEPERD,
$CINTLFLAG,
$CCALLCLASS,
$CTIELNCODE,
$CCALLSTAT,
$CBILAGCYCD,
$CACCTCODE,
$CCARDCODE,
$CRATECLASS,
$CCES,
$CAUTHFLAG,
$CMESSTYPE,
$CRPI,
$CSERVICE,
$CFLAG,
$CPLANCODE ) || die $dbh->errstr; # Actual insert
warn "Data fetching terminated early by error: $DBI::errstr\n" if
$DBI::err;
}
$stmt1->finish;
$stmt2->finish;
$dbh->disconnect;
$dbh2->disconnect;
