perlmeditation Brovnik Background This is rather long, but, I hope helpful. As part of developing a (DBI/MySQL) database to store info about modules (more @ [id://85848]), I needed to repeatedly check if a certain row already exists in the Database in order to avoid putting another identical row in. Each row is a tuple showing a "this module uses that module" relationship. I wanted to do some performance testing to find out the fastest way of discovering if there is a matching row already in the DB. Aside: I can't find the equivalent of an efficient "INSERT ... IF NOT EXISTS" in MySQL. The code below shows the different ways I found to get the result. Conclusions The fastest way to do it is : <code> my $sql = "SELECT uses FROM depend WHERE usedby='Depend::Module' AND uses='DBI'"; my $rows = $dbh->do($sql); </code> <readmore> This may not seem surprising ! <ol> <li>using <code>WHERE A='X' AND B='Y'</code> is faster than <code>WHERE A='X'</code>.</li> <li>using COUNT(*) is very slow</li> <li>do() is more than twice as fast as prepare()/execute()</li> <li>Doesn't matter which column SELECT returns to do counting</li> <li><code>SELECT col1</code> is slightly faster than <code>SELECT *</code></li> </ol> I found the first 3 of these surprising. In particular, I thought that do() was just a wrapper round prepare/execute, so the time difference is significant. Other optimisations and comments <ol> <li>Store IDs, rather than names in the DB. (Not tried - Would prob. be quicker, but at the expense of more lookup code and complication.)</li> <li>Cache results in PERL. (Not tried - Likely to be quicker, but less "pure" and some complexity in forward references.)</li> <li>Store "uses" relationship as a joined string in the module table and use PERLto manage. (Tried - was quicker, but again, not DB pure)</li> <li>Not tried on other DBD drivers</li> <li>The DB was small enough to fit in memory, and machine had no load, no no disk accesses during tests.</li> <li>I don't know if there are any internal optimisations happening (i.e. reusing cached results)</li> <li>I was less interested in the fact that some of the tests returned the 'wrong' result than the time taken, clearly in a full solution you need the correct answer :-)</li> <li>Not sure whether the do() is portable. The DBI code seems to suggest that some drivers return -1 untill all rows are fetched. Not sure if this would propagate back through the do() result.</li> </ol> Test Code <code> #! /usr/local/bin/perl -w #use strict; # because of the sub references use File::Find; use DBI(); use Depend::Module; use Date::Manip; use Benchmark; ### Table created with ## CREATE TABLE depend (\ ## usedby VARCHAR(80) NOT NULL ,\ ## uses VARCHAR(80) NOT NULL \ ## ) my $dbh = DBI->connect("DBI:mysql:database=modules;host=localhost", 'root', 'blah', { 'RaiseError' => 1 }); for ('Way1A','Way2A','Way2B','Way3A','Way4A','Way4B','Way5A','Way5B','Way6A') { my $ret = &$_; print "$_ returns [$ret]\n"; } my $res = timethese(10000, { 'Way1A'=>\&Way1A, 'Way2A'=>\&Way2A, 'Way3A'=>\&Way3A, 'Way4A'=>\&Way4A, 'Way4B'=>\&Way4B, 'Way5A'=>\&Way5A, 'Way6A'=>\&Way6A, 'Way2B'=>\&Way2B, 'Way5B'=>\&Way5B, }); Benchmark::cmpthese( $res) ; exit; ######## Subs sub Way1A { # Incorrect result, Only check one field # Doesn't count second field in this routine # uses prepare and execute my $sql = "SELECT uses FROM depend WHERE usedby='Depend::Module'"; my $sth = $dbh->prepare($sql); my $ret = $sth->execute; return $sth->rows; } sub Way2A { # Incorrect result, Only check one field # Doesn't count second field in this routine # uses do my $sql = "SELECT uses FROM depend WHERE usedby='Depend::Module'"; return $dbh->do($sql); } sub Way2B { # Incorrect result, Only check one field # Doesn't count second field in this routine # uses do # SELECT returns * == both columns my $sql = "SELECT * FROM depend WHERE usedby='Depend::Module'"; return $dbh->do($sql); } sub Way3A { # Generates correct result # uses prepare and execute my $sql = "SELECT uses FROM depend WHERE usedby='Depend::Module' AND uses='DBI'"; my $sth = $dbh->prepare($sql); my $ret = $sth->execute; return $sth->rows; } sub Way4A { # Generates correct result # uses do my $sql = "SELECT uses FROM depend WHERE usedby='Depend::Module' AND uses='DBI'"; my $ret = $dbh->do($sql); return $ret; } sub Way4B { # Generates correct result # uses do # counts other column my $sql = "SELECT usedby FROM depend WHERE usedby='Depend::Module' AND uses='DBI'"; my $ret = $dbh->do($sql); return $ret; } sub Way5A { # Fetch each row manually and do our own count # Incorrect result, only checks first field my $count = 0; my $sql = "SELECT uses FROM depend WHERE usedby='Depend::Module'"; my $sth = $dbh->prepare($sql); my $ret = $sth->execute; while ($ret = $sth->fetch()) { $count++}; return $count; } sub Way5B { # Fetch each row manually and do our own count # Correct result my $count = 0; my $sql = "SELECT uses FROM depend WHERE usedby='Depend::Module' AND uses='DBI'"; my $sth = $dbh->prepare($sql); my $ret = $sth->execute; while ($ret = $sth->fetch()) { $count++}; return $count; } sub Way6A { # use COUNT(*) to do counting # Correct result my $sql = "SELECT COUNT(*) FROM depend WHERE usedby='Depend::Module' AND uses='DBI'"; my $sth = $dbh->prepare($sql); my $rst = $sth->execute; my @count = $sth->fetchrow_array(); return $count[0]; } __DATA__ Results using 10,000 iterations Rate Way5A Way6A Way1A Way5B Way3A Way2B Way2A Way4B Way4A Way5A 1085/s -- -9% -10% -13% -13% -52% -53% -65% -65% Way6A 1190/s 10% -- -1% -5% -5% -48% -48% -61% -61% Way1A 1208/s 11% 1% -- -3% -4% -47% -48% -61% -61% Way5B 1250/s 15% 5% 4% -- -0% -45% -46% -59% -59% Way3A 1252/s 15% 5% 4% 0% -- -45% -46% -59% -59% Way2B 2268/s 109% 90% 88% 81% 81% -- -2% -26% -26% Way2A 2304/s 112% 94% 91% 84% 84% 2% -- -25% -25% Way4B 3077/s 184% 158% 155% 146% 146% 36% 34% -- 0% Way4A 3077/s 184% 158% 155% 146% 146% 36% 34% 0% -- </code> -- <A HREF="/index.pl?node_id=80482&lastnode_id=1072">Brovnik</A>