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 _______________________________________________ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/[email protected]
