469,599 Members | 2,597 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,599 developers. It's quick & easy.

WITH clause vs global temp table

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

Dec 19 '06 #1
9 17095
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
Dec 19 '06 #2
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
Dec 19 '06 #3
>>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
Dec 19 '06 #4
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.

Dec 20 '06 #5
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
Dec 20 '06 #6
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
Dec 20 '06 #7
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

Dec 21 '06 #8
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

Dec 21 '06 #9
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
Dec 21 '06 #10

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by pb648174 | last post: by
reply views Thread by gwaddell | last post: by
1 post views Thread by crazy_jutt | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.