Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

Re: Re: Comparing an array to a DBI table

by agoth (Chaplain)
on Jul 18, 2001 at 20:16 UTC ( [id://97725]=note: print w/replies, xml ) Need Help??


in reply to Re: Comparing an array to a DBI table
in thread 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...

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

Log In?
Username:
Password:

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

How do I use this?Last hourOther CB clients
Other Users?
Others having a coffee break in the Monastery: (5)
As of 2024-03-28 12:45 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found