Re: Global Temporary Table Scalability Problem

william milbratz wrote:

> 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

Which type of GTT did you build? It makes a big difference.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)