Gloabl temp tables

[Queryman]

I am experiencing extremely poor performance when using global temp tables. Jobs that take a couple of minutes when creating regular tables and doing some processing on them take more than a few hours when doing the exact same processing by using global temp tables. Any suggestions?
Thanks,



Michael

 

[dnoeth]

Could you post some more info:
It's exactly the same table including the primary index?
Are there any statistics collected?
What processing, inserts/updates/deletes?

Dieter

 

[Queryman]

Hi Dieter,
It is a rather complex process that retrieves data and inserts/populates four tables. GT's are unique to the session as you know and to the best of my knowledge you cannot collect stats on them. The tables are identical and the two processes are identical except one uses GT's and the other regular tables.



Michael

 

[dnoeth]

If there's no difference between the tables DDL, there should be no difference in speed. If there is, i'd open an incident.

You can't collect statistics on Volatile Tables, but on Global Temporary Tables ;-)

Dieter

 

[Queryman]

Coul it have anything to do with the spool space that is available to each use. If you create a lot of GT's and use up a lot of that space, would you experience degredation in processing speed?



Michael

 

[dnoeth]

"Coul it have anything to do with the spool space that is available to each use. If you create a lot of GT's and use up a lot of that space, would you experience degredation in processing speed?"

No, when you run out of spool there's a 2646 "No more spool space" and the query is aborted.

But GTs are not part of Spool they're stored in Temp space ;-)

Maybe you should explain the steps in your script to see if there are any differences between GTs and real tables...

Dieter

 

[Queryman]

IS TEMP SPACE ALSO LIMITED BY USER, COULD THAT CAUSE THE QUERIES TO RUN SLOWER?



Michael

 

[tdatgod]

Hi,
you CAN specify TEMP SPACE limits on the create/modify User/database like you can from PERM and SPOOL although it migh not be as common an option.

However the limit hs nothing to do with the speed of the query. The query runs at full speed and when you hit the limit it aborts.

I found this in the SQL manual. There is an Option on the CREATE/ALTER table which could affect performance.



LOG / NO LOG

the transaction journaling option for a global temporary table.

LOG specifies that any updates, inserts, or deletes made to the global temporary or volatile table be logged in the transaction journal. This is the default.

NO LOG specifies that transaction journal logging is not to be performed. Because the system does not do any transaction journaling, performance is greatly enhanced.

If any sort of abort or restart occurs and the table is defined as NO LOG, then any updates, inserts, or deletes made to the temporary or volatile table cannot be
recovered.

If the table is defined as NO LOG, contents of any materialized temporary table or any volatile table are emptied when a transaction aborts.

This option pertains to global temporary and volatile tables only.



Pleease Note LOG is the default if you don't specify NO LOG explicitly.



I wonder if there is a BUG in the LOG logic that will TJ the inserts even if the table is empty.


Maybe somehow the commit logic of your script got changed when you converted it from using PERM tables to using Global Temp tables.

Some how after the first row is inserted it is committed and therefore subsequent rows are inserted into a populated table ( instead of an empty table ) and therefore they are being journaled.

But this last statement would be true in either case, because PERM tables would be journaled as well, if you didn't change the commit structure of your script.

----