Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris

Re: DBD::SQLite tuning

by zakzebrowski (Curate)
on Mar 17, 2005 at 02:43 UTC ( #440247=note: print w/replies, xml ) Need Help??

in reply to DBD::SQLite tuning

SQLite is a database, and you need to define indexes for columns that you will be selecting. (You may want to read a good database book for examples of when, and when NOT to use an index... for that matter, when you want to use a database and when to use a delimited text file...) Adding an index improves performace, at least at select time. Not tested for replace time, but I leave that as an exercise to the user...
Example code:
#!/usr/local/bin/perl use strict; use DBI; use DBD::SQLite; use Benchmark; my $dbh = DBI->connect("dbi:SQLite:dbname=testdb","",""); print "Connected to Database OK.\n"; print "Creating Tables && INSERTING\n"; my $sql = qq{CREATE TABLE A (A INT, B VAL)}; my $sth = $dbh->prepare($sql); $sth->execute(); my $i=0; while ($i<10000){ my $sql = qq{INSERT INTO A(A,B) VALUES (?,?)}; my $sth = $dbh->prepare($sql); $sth->execute($i,$i); $i++; } print "DONE\n"; sub doSelect{ # Yes, I could pre cache the query, but this won't affect the result +s my $sql = qq{SELECT A FROM A WHERE B = ?}; my $sth = $dbh->prepare($sql); my $i =int( rand 10000); $sth->execute($i); my @ary = $sth->fetchrow_array(); } timethese (10000,{'MethodOne'=>'&doSelect'}); my $sql2 = qq{CREATE INDEX FOO ON A(B)}; my $sth2 = $dbh->prepare($sql2); $sth2->execute(); timethese (10000,{'MethodOne'=>'&doSelect'});

Connected to Database OK. Creating Tables && INSERTING DONE Benchmark: timing 10000 iterations of MethodOne... MethodOne: 100 wallclock secs (71.48 usr + 18.18 sys = 89.66 CPU) @ 1 +11.53/s (n=10000) Benchmark: timing 10000 iterations of MethodOne... MethodOne: 5 wallclock secs ( 2.83 usr + 1.11 sys = 3.94 CPU) @ 25 +38.07/s (n=10000)

Zak - the office

Replies are listed 'Best First'.
Re^2: DBD::SQLite tuning
by perrin (Chancellor) on Mar 17, 2005 at 04:10 UTC
    My code has an index. Take a closer look at the create statement. Marking a column as the primary key causes SQLite to create a unique index on that column.

    Just for fun, I tested creating the index separately, and making it a unique index or not. There was no significant change.

      D'oh... I was running on a caffine low at that point in the afternoon yesterday... Where did you hear the 3x claim?

      Zak - the office

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://440247]
[Corion]: Whoa! Somebody forwarded the AnyEvent::Impl:: Prima loop to schmorp and he's even interested in maintaining it himself - that'd be highly convenient for me ;)
[Corion]: (I also have a mail by dk who suggests some additions, which I should add this evening)
[ambrus]: Corion: um, that was me (duh)
[ambrus]: I just can't get you two to cooperate directly because schmorp doesn't want to be in the chatterbox
[ambrus]: maybe you should join his irc channel or something, unless that causes a flame blowup

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (9)
As of 2016-12-09 14:07 GMT
Find Nodes?
    Voting Booth?
    On a regular basis, I'm most likely to spy upon:

    Results (151 votes). Check out past polls.