Archives of the TeradataForum

Message Posted: Wed, 27 Mar 2002 @ 17:40:21 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi,

Temp and Volatile were added in v2r3. In 4.1 we increased the number of temp and volatile tables allowed per session 15 fold. From 32 and 64 to 1000 and 2000 respectively. WE had a couple microstrategy queries which required 75 or more.

The reason they are performant is there is little to no over head in the dictionary when you materialize and unmaterialize the temp or volatile table.

with a normal v2r2 query with simulated temp tables.

create 'temp' table as a PERM table
populate temp table
run queries against temp table
drop temp table

only one person could create this table and if anyone ran this query at the same time it is possible they would step on each other one dropping the table at the time the other was populating it.

Now with temp and volatile tables after v2r3 the DBS creates a Temp table or Volatile table 'template' once and then when the use runs the query

materialize (populate) the temp table
run queries against table
unmaterialize (delete,drop, logoff) the temp table.

in microstrategy's testing they got more than a 40% boost in some of their queries that had large numbers of the 'simulated temp tables' from v2r2 to v2r3.

this is a link to a white paper from microstrategy about their use of Volatile tables and their performance gains.

www.microstrategy.com...

you might have to provide contact information to access this paper.

	<Prev	Next>		<<First	<Prev	Next>	Last>>