Re: Comparing an array to a DBI table

My principle is to always let the database do what it does well.

my $count_sth = $dbh->prepare(
  'SELECT count(*) FROM subscription WHERE userid IN (' .
  join(',', "?" x @compare_array) .
  ')');
$count_sth->execute(@compare_array);
my $count = $count_sth->fetchrow_array;
[download]

-- Randal L. Schwartz, Perl hacker

Comment on Re: Comparing an array to a DBI table Download Code

Replies are listed 'Best First'.
Re: Re: Comparing an array to a DBI table by agoth (Chaplain) on Jul 18, 2001 at 20:16 UTC
Erk. The thought of joining 50,000 elements into an IN clause makes me shudder, possibly wrongly. I was always under the impression there was a limit to the number of items IN would allow, I'm pretty sure there was in DB2 V5, and though I haven't yet found the Oracle man page to back this up, I've avoided large IN's ever since. Not to mention the efficiency and the possible size of the SQL STATEMENT ...... NB I'm going away to do my homework on IN now...and validate / invalidate my long held assumptions...	[reply]
Re: Re: Re: Comparing an array to a DBI table by adamsj (Hermit) on Jul 19, 2001 at 17:41 UTC
Sure, there's a limit--for Oracle, I think it's 254. The value is system-dependent. (Shocked! I'm shocked!) So make a compromise method: Take the number of items IN will allow on your RDBMS, throw those into the statement, grab back the data, and loop till you've exhausted the items. I'd expect this to be more efficient than doing a select on every item--however, I'd study some explains and do some benchmarking to be get a better answer. Still, it's a valid approach to the problem. adamsj They laughed at Joan of Arc, but she went right ahead and built it. --Gracie Allen	[reply]