Re: One db too many

Hi Joakim,

seems to me you have a major problem with your relational design and that one entity is missing. Create that table and use it in the join

Stefan

Joakim Mared wrote:

> Hi all,
>
> I am stuck here with a slight performance problem. I have a two-database
> environment, the big O and the free My alternative...moving all of the
> data to Oracle is unfortunately not an option for various reasons.
>
> I have a perl script that interacts with both databases. It retrieves a
> list of id numbers from the mysql database and puts these in an array. The
> number of ids can be anything from 5 to 50,000. I now want to do this:
>
> SELECT DISTINCT field FROM table WHERE id IN (<ids in array>)
>
> table is a ~1.2 million rows Oracle table, primary key on id of course.
>
> So far i have tried the following:
>
> 1) WHERE id IN (x, y, z, ...)
> Ids joined to a long comma-delimited string. This works well until number
> of ids > 1000 and i get "ORA-01795: maximum number of expressions in a
> list is 1000"
>
> 2) WHERE id = x OR id = y OR id = z OR id = ...
> This works, but is way too slow for anything more than 5,000 ids
>
> 3) loop over array, insert into dummy table in oracle, and join
> So far this is the best alternative, but gets pretty slow (20-30 seconds)
> with many ids because of having to loop over the array and do one insert
> per id. The insert is done with a placeholder for id, like so: INSERT INTO
> dummy (id) VALUES (?), and AutoCommit is off.
>
> Is there a 4th, 5th, 6th, ... alternative or is there a way to improve 3)?
> Any and all hints/suggestions would be appreciated.
>
> Thanks in advance,
>
> Joakim Mared
Received on Tue Jan 15 2002 - 06:15:52 CET