To reiterate and define a bit of what Ron asked: where is this slow? What happens if you take out the insert statements? What does the time drop to? How long does the select take alone?
Can you use fetch instead of fetchall_arrayref? I'd take out the print statements and test there, too... Jeff > -----Original Message----- > From: Lamb Joseph [mailto:[EMAIL PROTECTED] > Sent: Wednesday, September 22, 2004 4:19 PM > To: [EMAIL PROTECTED] > Subject: RE: Faster Inserting Code > > > The select statement gets 30,000+ records. After 10 > minutes of inserting using the code below it had > processed 7000 records. I would like to have 30,000 > records processed in 5 minutes if possible. > > > --- "Reidy, Ron" <[EMAIL PROTECTED]> wrote: > > > Joseph, > > > > Define faster. Where are things slow? What are > > your expectations? > > > > ----------------- > > Ron Reidy > > Lead DBA > > Array BioPharma, Inc. > > > > > > -----Original Message----- > > From: Lamb Joseph [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, September 22, 2004 2:05 PM > > To: [EMAIL PROTECTED] > > Subject: Faster Inserting Code > > > > > > I would like to know how to insert faster using > > SQLite. > > > > use strict; > > use dbi; > > use Date::Manip; > > use Diagnostics; > > > > #Connect to the database > > my $dbh = DBI->connect("dbi:SQLite:data.dbl") > > || die "Cannot connect: $DBI::errstr"; > > > > #MSSQL Server > > my $kewilldb = 'LV_KWARCHIVE'; > > > > my $dbh_kewill = DBI->connect( "dbi:ODBC:$kewilldb", 'kewill', > > 'kewill' ) > > || die "Cannot connect to Kewill: $DBI::errstr"; > > > > $dbh->do("DROP TABLE zipcode5"); > > $dbh->do("CREATE TABLE zipcode5 ( destzip, count > > )"); > > my $sth_insertzipdata = $dbh->prepare("insert into > > zipcode5 values(?,?)") > > || die "Cannot prepare insert into zipcode5\n"; > > > > &getKewillData; > > > > $dbh->disconnect; > > $dbh_kewill->disconnect; > > > > sub getKewillData { > > my $tempdate = &DateCalc( "today", "-15days " > > ); > > my $tempdate1 = &DateCalc( "today", "-45days " > > ); > > # get time of 45 days ago > > > > my $todate = &UnixDate( $tempdate1, "%Y-%m-%d > > 00:00:00" ); > > my $fromdate = &UnixDate( $tempdate, "%Y-%m-%d > > 23:59:00" ); > > print "tempdate $tempdate tempdate1 $tempdate1 > > $todate $fromdate ", "\n"; > > > > my $sth_kewill = $dbh_kewill->prepare( > > "select destzip,count(*) from shipments > > where datecreated > ? > > and datecreated < ? > > and CarrierCode = 'USP' > > group by Destzip" > > ) > > or die "Cannot Prepare Kewill Zip Code: > > $DBI::errstr"; > > > > $sth_kewill->execute( $todate, $fromdate ) > > or die "Cannot execute kewill query\n"; > > > > my $ary_ref = $sth_kewill->fetchall_arrayref; > > > > foreach (@$ary_ref) { > > print "$_->[0], $_->[1]\n"; > > > > > > $sth_insertzipdata->execute($_->[0],$_->[1]); > > > > } > > > > } > > > > > > > > ===== > > Joseph Lamb > > > > > > > > _______________________________ > > Do you Yahoo!? > > Declare Yourself - Register online to vote today! > > http://vote.yahoo.com > > > > This electronic message transmission is a PRIVATE > communication which > > contains information which may be confidential or privileged. > > The information is intended > > to be for the use of the individual or entity named > > above. If you are not the > > intended recipient, please be aware that any > > disclosure, copying, distribution > > or use of the contents of this information is > > prohibited. Please notify the > > sender of the delivery error by replying to this > > message, or notify us by > > telephone (877-633-2436, ext. 0), and then delete it > > from your system. > > > > > > ===== > Joseph Lamb > > > > __________________________________ > Do you Yahoo!? > New and Improved Yahoo! Mail - Send 10MB messages! > http://promotions.yahoo.com/new_mail >
