I bagged SQLite and used an Oracle DB. Thanks for the help.
--- Jeff Urlwin <[EMAIL PROTECTED]> wrote: > 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 > > > > ===== Joseph Lamb __________________________________ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail
