RE: Faster Inserting Code

[Reidy, Ron]

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.