Global Temporary Table Scalability Problem

Hi,

We have a complex stored procedure that runs slowly under large volumes of data.

After profiling and testing, I found that the use of a single global temporary table (i.e. one w/ only 5 records) caused the problem and that if I replaced the temp table with a permanent table, the results improved dramatically. (20-50x).

Some more info:
the procedure in question receives as one of its params a comma-delimited-list of "principal Ids". This list is usually very short (1-6 entries). The procedure parses the list and populates a 1field/1key temp table. The procedure then uses that table in a complicated query to return the records "which the user has access to".

When I came across the fact that the global temp tables performed so poorly, I tried using a permanent table to store the "principal IDs" (i.e. same key structure). The query ran 50x quicker . (This was a hack. I'd actually need a way to store these values temporary 'per session')

Two questions:
1) why these counterintuitive results? Why are global temporary tables so slow? under these conditions? Why do they perform differently from permanent tables?

2) is there any way to improve the performance of global temporary tables for these circumstances?

thanks,

bill milbratz Received on Fri Feb 06 2004 - 10:34:45 CST