in reply to Comparing an array to a DBI table

Thank you for the suggestions.

I don't know why it didn't occur to me to approach this from the other side and read the list of userid's into one big hash and compare THAT to my array. (thank you Masem for using the satori stick) I can see that this would be a big improvement.

Luckily, most of my tables are in the 20k to 50k records range. If I ever need to deal with larger tables (500k records), I can see that dealing with it in chunks would be worthwhile.

I appreciate everyone's help and wisdom
- oakbox

Replies are listed 'Best First'.
Re: Re: Comparing an array to a DBI table
by runrig (Abbot) on Jul 18, 2001 at 20:36 UTC
    If there are more records than memory will allow, then you might consider reading all the user_ids into a DB_File tied hash. If you list of user_ids to check is not large, then use the 'IN' clause as suggested by merlyn. If your table is very large (or you don't want to use DB_File) AND your list is large, then don't use count(*), but something like this (BTW, I hope you have an index on user_id!):
    
my $sql = 'select 1 from subscription where user_id = ?';
my $sth = $dbh->prepare($sql);
foreach my $userid ... {
 $sth->execute($userid);
 my ($exists) = $sth->fetchrow_array;
 $sth->finish;
 if ($exists) {
  ...
 }
}

    [download]
    I also notice you're handling your own errors and probably not using RaiseError on the connect. That's ok, but then you should also check for errors on the prepare and the execute.
[reply]
[d/l]

In Section Seekers of Perl Wisdom