Re: strange behaviour with temporary tables (add. info)

#1 (**permalink**) 02-27-2008, 06:04 AM

the type of temporary table that I am using is
declare global temporary table

Thanks,

Sumanth

"Sumanth" <Sumanth.Yamala@sas.com> wrote in message
news:dsqos0$vgg$1@foggy.unx.sas.com...
> the pseudo code for my update is
>
> for i = 1 to n;
> insert k records into temp_table;
>
> update a set c1 = (select c1 from temp_table where
> temp_table.pk = a.pk), c2 = ( select c2 from
> temp_table where temp_table.pk = a.pk)
> where a.pk in (select pk from temp_table)
>
> delete from temp_table
> commit;
> end for;
>
> When the temp_table is created as a DB2 temporary table, the performance
> degrades after each loop, but if temp_table is created as a regular DB2
> table we see a consistent performance.
>
> Also the number of row_reads (activity monitor) was found to be extremely
> high.
>
> The size of the table that is updated is around 70,000 and the temp_table
> has 8192 rows.
>
> Can anyone shed some light on what I am doing wrong? or if there are any
> limitations on temporary table that I need to be aware of.
>
> Thanks,
> Sumanth
>

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode