[Dbix-class] Regarding Oracle's 1000 element limit in IN clause

[Peter Rabbitson]

On Mon, Oct 24, 2011 at 11:55:51AM +0200, Jorge Gonzalez wrote:
> El 24/10/11 10:48, Matija Grabnar escribió:
>> You have another possibility: create the temporary tables in another  
>> database. This database can be on your OWN server, so the HUGE company  
>> doesn't even have to know about it.
>>
>
> Yes but I need to cross information from the HUGE company database, so I  
> need to join the tables. I want to filter a table with millions of rows  
> (HUGE db) with a local list of values for a field (SMALL db). For this,  
> if I want to use temp tables I have two options:
>
> A. Create a temp table in HUGE database with the contents of SMALL  
> database values. I'm not allowed to do this, as I said in my former 
> mails.
>
> B. Create a temp table in SMALL database with the contents of the HUGE  
> database I want to filter. Thi would mean transfering a copy of the  
> table with _millions_ of rows to local, just to discard it afterwards.  
> Seems not very reasonable.
>
> If I want to join the tables they need to be in the same schema (or at  
> least the same DB server), so I can do only two things: move the SMALL  
> table where the HUGE one is (not allowed), or move the HUGE one where  
> the SMALL one is (not reasonable).
>
>> If you were to download the full resultset and do the searching in  
>> Perl you would in effect be doing the
>> same thing, except your database would be in your RAM, and you would  
>> be using Perl instead of SQL
>> to search for it. For large datasets (which this seems to be) a  
>> database on the local server is more efficient than doing it all in 
>> RAM.
>
> Question is, my server has enough RAM to slurp the resultset and then  
> search (which is what I'm doing now). If enough RAM is available, no  
> disk-based SQL server can beat that, provided that efficient search  
> algorithms are used (I'm using the resultset to create several RAM based  
> indexes - perl hashes - before doing any searching).
>

Who said disk-based? my $dsn = 'dbi:SQLite::memory:' :)

Cheers