RE: Most efficient technique for temp table data?

There are a few things that I don't follow in your posts, but my advice is use temp tables and a composite index. Anything else means more code, more trouble and less efficiency. As for the choice between SPL and ESQL/C, this is a matter of tastes. SPL has the advantage of putting [part of] the logic of an application in the same place where the data is (consider this a small step towards OODB's), but is generally slower than using a tool. So one big SPL pro is that you have to write app logic only once, no matter how many tools you use to access the same DB (makes porting between tools easy). The downside of this is that splitting app logic in two (i.e. tool & SP) means that you have two places (or more, if you use more that one tool or app) to look for correctness, not to mention the fact that whenever you change a SP, each SP that calls it will be considered invalid by the engine (a RPINTA! :-) Also the two languages have different limitations compared to each other (for instance, SPL has no arrays, so if you want to simulate them, you have to use temp tables), and this means that each is better at solving different problems. HTH, Marco ____________________________________________________________________________ rem radioterapia, which I immeritately manage, seldom agrees with what I say marco greco (Catania, Italy) Work: marcog@ctonline.it rem radioterapia 39 95 447828 fax 446558 (was mar.greco@agora.stm.it) Achea 39 95 503117 --- On Fri, 10 Jan 97 21:27:08 GMT Paul Mailman <pamailman@qed.com> wrote: I have the following problem, for which I've been experimenting with several approaches using stored procedures. I need to: - Make two independent selections out of table A - Take the cross-product of those two selections. - Look up the resulting pairs for any that appear in a table B (which selected pairs of row of A). The selections from A and their cross-product are temporary data, to be disposed of once the lookup in B is complete. There are several ways to approach storing this temporary data: - create temporary tables, dropping them when done - make inserts in a permanent working table, deleting the entries when done - write stored procedures that return mutiple rows (using RETURN WITH CONTINUE), and process the results in a calling procedure using FOREACH EXECUTE PROCEDURE I'm looking for advice on what's the most efficient method. I'm more concerned with optimizing the response time for an individual query than multiuser issues. (Most times only one user would be executing this procedure, so for instance I'm not too worried about lock contention issues between multiple users sharing a working table.) In test data that I'm working with, I have about 3000 rows in A and about 5000 in B. Typical hit on the original searches of A might be 5-20. Final results might be 1 to a dozen or so. (Real production data might scale this up by a factor of 2 or 3.) Would doing this in ESQL offer advantages over SPL or vice versa? For the final lookup in B: A has a serial column primary key, and each B includes two foreign key columns for the values of the primary keys of the pair rows in A. Is a composite index on the two columns in B be the best way to assure fastest match of pairs, or would there be any advantage in creating a single dependent column in B (say 32768*serialA1 + serialA2) and indexing it instead? Running Informix SE 7.1 under Unix. -----------------End of Original Message-----------------