mmittiga17 has asked for the wisdom of the Perl Monks concerning the following question:
Hi All, I am trying to have a script that uses DBI & sqlite. Task at hand is to take data files and check if record exist in db already, if not insert into db table. Problem is it is taking for ever and moving slow. This is my first attempt at the sqlite & Perl. Below is my code if someone can tell me what I am doing wrong or advise how to achieve better performance, I would be grateful.
my $dbh = DBI->connect( "dbi:SQLite:dbname=SSBHLD2.db", "", "", { RaiseError => 1 }, ) or die $DBI::errstr; $dbh->do("CREATE TABLE IF NOT EXISTS HLDdata(Data TEXT)"); while(<IN>){ $line = $_; my $sth = $dbh->prepare( "select count(*) from HLDdata where Data = + '$line'"); $sth->execute(); my ($data) = $sth->fetchrow(); print "$data \n"; if ($data ne 0){ print "record exist not adding\n"; &Logit("record exist not adding"); }else{ $dbh->do("INSERT INTO HLDdata(Data) VALUES ('$line')"); $line =~s/\|/\t/g; print "$line\n"; print OUT "$line"; } } close(IN); close(OUT);
|
---|
Replies are listed 'Best First'. | |
---|---|
Re: SQLite Slow insert
by afoken (Chancellor) on Feb 17, 2013 at 19:24 UTC | |
Re: SQLite Slow insert
by Corion (Patriarch) on Feb 17, 2013 at 19:21 UTC | |
by afoken (Chancellor) on Feb 17, 2013 at 19:29 UTC | |
Re: SQLite Slow insert
by Anonymous Monk on Feb 17, 2013 at 23:19 UTC | |
by afoken (Chancellor) on Feb 18, 2013 at 06:01 UTC | |
by mmittiga17 (Scribe) on Feb 18, 2013 at 17:43 UTC | |
by Anonymous Monk on Feb 19, 2013 at 10:50 UTC | |
by mmittiga17 (Scribe) on Feb 21, 2013 at 20:16 UTC | |
Re: SQLite Slow insert
by Plankton (Vicar) on Feb 18, 2013 at 07:51 UTC | |
Re: SQLite Slow insert
by digglife (Acolyte) on Aug 14, 2014 at 03:33 UTC |
Back to
Seekers of Perl Wisdom