[Dbix-class] Regarding Oracle's 1000 element limit in IN clause
rabbit+dbic at rabbit.us
Mon Oct 24 10:55:05 GMT 2011
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
> 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
> 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:' :)
More information about the DBIx-Class