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
