ADO v. slow calling Stored Proc

I've searched the newsgroups but can't quite find the solution. I have a Stored Procedure which involes a reasonably complicated query, LEFT JOINing and RIGHT JOINING two tables and UNIONing (with a lot of DECODE throw in for good measure), ordered by time. Understandably enough, when the tables grow to 100,000+ records, things grind to an almost complete halt.
Now remotely calling an equivalent Stored Procedure in SQLServer from a web server with an ADO MAXRECORDS of 100 appears to implicitly imply a ROWCOUNT <=100 in the Stored Procedure, and it seems to only process the minimum amount of records (As opposed to when I execute it natively, which takes forever). In Oracle, the whole lot is being processed, before a response is sent. What I would like to be able to do is select the first 100 from one table ordered by time, and likewise from the second table, and then perform the complicated query as I don't care about the remainder of the records. So far I've only managed to get the 100 records into a PL/SQL Table of Records, which I then can't execute an SQL query against. So the question is, does anyone have a suggestion of the best way to do this, bearing mind it is imperative they definitely are the most recent records ordered accordingly (it doesn't matter if the union returns more than 100, as they get trimmed off by ADO). Oh yes, and it has to work in Oracle 8.0 (which is what is causing all the hassle). The original stored procedure looked something like:

SELECT Lots_of_DECODE_Fields
FROM Table1,Table2
WHERE Two_LEFT_JOIN_clauses
UNION SELECT
Lots_of_DECODE_Fields
FROM Table1,able2
WHERE Two_RIGHT_JOIN_clauses
ORDER BY Time DESC

so perhaps you understand why it is slow and why I would just like to select the first 100 of each table and then do it. A temporary table as they have in SQLServer would fit the bill, but I don't think it's supported in Oracle, and if I explicitly CREATE TABLE .... DROP TABLE in the Stored Proc, what happens if another instance of the Stored Proc is called whilst it's running?
Furthermore, I read the post a while ago about using THE ...CAST..MULTISET..., but the caveat was that it doesn't perform well for large datasets, and I need roughly up to 1 million records at least.
Well, thanks for those of you that have stuck with it to the end of this (rather long) post, I hope someone can offer a couple of pointers. Maybe I might get to go to Cornwall for the Eclipse after all.

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Aug 05 1999 - 15:38:02 CDT