Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

Re: Odd Database Behavior

by grinder (Bishop)
on Dec 15, 2003 at 20:15 UTC ( #314896=note: print w/replies, xml ) Need Help??


in reply to Odd Database Behavior

If you just want to see whether a value appears in a column of a table, performing a count(*) is a fearfully expensive way of going about that. At the worst, you will perform a full table scan.

The query would be better formulated as select 1 from members where nickname='...'

If this is just a once-off, the simplest approach would be to use the do method, with something (error-checking notwithstanding) like:

sub nick_exists { return $db->do( "select 1 from members where nickname=" . $db->quote($_[0]) ); }

If, on the other hand, you know you'll be pounding on this routine in an inner loop, it might then be worthwhile recasting it as a prepared statement:

BEGIN { my $ss = $db->prepare( 'select 1 from members where nickname=?' ) or d +ie; sub nick_exists { $ss->execute($_[0]); my $res = $ss->fetchrow_arrayref; $ss->finish; $res and defined $res->[0] ? 1 : 0; } }

The beauty of this approach is that you don't have to change your code. The access to the database is abstracted away in the nick_exists routine.

And as if that wasn't enough, you could further add

use Memoize; memoize('nick_exists');

... although you want to be careful with that. The program will continue to assume that a nick that has just been taken is still available. That would be a bug. But, used with care, this can provide a healthy speed boost.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (9)
As of 2021-04-13 13:20 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?