Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much
 
PerlMonks  

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'});

Results:
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


Comment on Re: DBD::SQLite tuning
Select or Download Code
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?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://440247]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (7)
As of 2014-09-20 09:45 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (158 votes), past polls