Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
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 examining the Monastery: (9)
As of 2015-07-07 15:20 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (90 votes), past polls