WITH clause vs global temp table

[Veeru71]

Can someone point me to good documentation on 'WITH clause" ? (I
couldn't get much
out of Queries section from SQL Reference manual).
We are getting better performance when we explicity use global temp
tables to store
intermediate results than using "WITH cluase" in our queries.
Where does DB2 store the intermediate results if the query uses "WITH
clause" ?
Thanks

[fboldt]

Veeru71,

you might want to search for 'common table expression' which is the
more common term for 'WITH clause'

As far as I know common table expressions create temp tables like sorts
or joins sometimes do. May be some of the experts can clearify this.

cheers
Florian
Veeru71 schrieb:

Can someone point me to good documentation on 'WITH clause" ? (I
couldn't get much
out of Queries section from SQL Reference manual).
We are getting better performance when we explicity use global temp
tables to store
intermediate results than using "WITH cluase" in our queries.
Where does DB2 store the intermediate results if the query uses "WITH
clause" ?
Thanks

[fboldt]

I think the main difference is:

The common table expression lasts only for the life of the SQL
statements

The Global temporary table (GTT) lasts for the life of the database
connection and it needs the existance of a user temporary tablespace.
May be this tablespace and the bufferpool a responsible for your better
query performance with GTT.

cheers
Florian

fb****@fastmail.fm schrieb:

Veeru71,

you might want to search for 'common table expression' which is the
more common term for 'WITH clause'

As far as I know common table expressions create temp tables like sorts
or joins sometimes do. May be some of the experts can clearify this.

cheers
Florian
Veeru71 schrieb:

Can someone point me to good documentation on 'WITH clause" ? (I
couldn't get much
out of Queries section from SQL Reference manual).
We are getting better performance when we explicity use global temp
tables to store
intermediate results than using "WITH cluase" in our queries.
Where does DB2 store the intermediate results if the query uses "WITH
clause" ?
Thanks

[Serge Rielau]

>>Can someone point me to good documentation on 'WITH clause" ? (I

>>couldn't get much
out of Queries section from SQL Reference manual).
We are getting better performance when we explicity use global temp
tables to store
intermediate results than using "WITH cluase" in our queries.
Where does DB2 store the intermediate results if the query uses "WITH
clause" ?
Thanks

Also a common table expression (CTE) may not even be temped at all.
That is if you only refer to the CTE once DB2 treats it like any nested
query.
The big advantage of DGTT is that you can CREATE INDEX and RUNSTATS on them.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html

[Veeru71]

Thanks every one.

Most of our queries are joins with 20 tables with lots of CTEs and
everyone is cribbing
about performance.
I've tried breaking these complicated queries into many smaller ones
by using global temp tables (instead of CTEs) and noticed significant
performance improvements in some cases.
Probably the optimizer is not be able to select the most efficient
query plan if there are too many tables in the join.

[Knut Stolze]

Veeru71 wrote:

Thanks every one.

Most of our queries are joins with 20 tables with lots of CTEs and
everyone is cribbing
about performance.
I've tried breaking these complicated queries into many smaller ones
by using global temp tables (instead of CTEs) and noticed significant
performance improvements in some cases.

In that case, your DGTT are comparable to MQTs. They contain precomputed
data. It's not much surprising that you have performance improvements.

Probably the optimizer is not be able to select the most efficient
query plan if there are too many tables in the join.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[Serge Rielau]

Veeru71 wrote:

Thanks every one.

Most of our queries are joins with 20 tables with lots of CTEs and
everyone is cribbing
about performance.
I've tried breaking these complicated queries into many smaller ones
by using global temp tables (instead of CTEs) and noticed significant
performance improvements in some cases.
Probably the optimizer is not be able to select the most efficient
query plan if there are too many tables in the join.

Are these BI queries? You are correct that with 20 join elements the
risk of picking a bad plan increases significantly.
Statistical views are useful with that regard:
http://www.ibm.com/developerworks/db...e/dm-0612chen/

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html

[Veeru71]

Hi Serge,

They are not BI queries per se but are very complicated batch reports
that run once in a day. In some cases the users can also manually run
the reports selectively.
Because of excessive normalization that was done in the database
design, we are forced to join tons of tables for every little thing. :)
We don't do many aggrigations though and hence MQTs are not of much
help, I guess.

Currently every report contains a SQL statement running into several
pages (with tons of tables joined and lots of 'WITH clauses') and is
fired off of a perl script. Most of these SQL stmts are taking long
time to complete.

I am trying to propse an architectural change to take 'divide &
conquer' approach by breaking
the complicated SQL stmt into several simpler SQL stmts using GTTs;
move the code into SQL stored procedure and the perl script just makes
a call to the stored proc.

It is a 6 year-old stable system (but for performace reasons) and no
one will buy the idea of redesigning the tables to perform a little
bit of de-normalization. There are @ 500 tables and the db size is >
300 gb.

On a different topic, does DB2-UDB have 'table partitioning' feature
similar to Oracle's range/hash/list partitioning ?

-Thanks

[fboldt]

Veeru71 schrieb:

On a different topic, does DB2-UDB have 'table partitioning' feature
similar to Oracle's range/hash/list partitioning ?

-Thanks

Veeru71,

the first step in the direction of range partitioning was the UNION ALL
VIEW concept:
http://www-128.ibm.com/developerwork...202zuzarte.pdf

now (DB2 Version 9) IBM offers true range partitioning like Oracle does
http://blogs.ittoolbox.com/database/...ls-part-1-9381
(thanks Chris for your excellent BLOG)

cheers
Florian

[Knut Stolze]

Veeru71 wrote:

Because of excessive normalization that was done in the database
design, we are forced to join tons of tables for every little thing. :)
We don't do many aggrigations though and hence MQTs are not of much
help, I guess.

You can use MQTs w/o aggregation as well. For example, the REPLICATED
keyword copies the results of a query on all database partitions. (Note
that a non-DPF system actually has 1 partition only.)

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany