Re: Re: Comparing an array to a DBI table

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...

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

Replies are listed 'Best First'.
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]

Replies are listed 'Best First'.

Re: Re: Re: Comparing an array to a DBI table
by adamsj (Hermit) on Jul 19, 2001 at 17:41 UTC

think

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]