473,231 Members | 1,740 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,231 software developers and data experts.

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 17489
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: pb648174 | last post by:
I have a pivot table implementation, part of which is posted below. It returns no errors in query analyzer, but when profiler is run, it shows that "Error 208" is happening. I looked that up in BOL...
10
by: Ranga | last post by:
I was unable to run the statement "CREATE GLOBAL TEMPORARY TABLE" on unix version of DB2, it gave the follwing error db2 => create global temporary table temp ( OGI_SYS_NR char(8) ) DB21034E ...
0
by: gwaddell | last post by:
I have a stored procedure that is loading data into a global temp table. Here is all the code for that stored procedure. CREATE PROCEDURE AS SET NOCOUNT ON BEGIN
16
by: pukivruki | last post by:
hi, I wish to create a temporary table who's name is dynamic based on the argument. ALTER PROCEDURE . @PID1 VARCHAR(50), @PID2 VARCHAR(50), @TICKET VARCHAR(20)
1
by: crazy_jutt | last post by:
hi, anyone knows if i can create index on global temp tables if yes, will i use session schema for indexes also ? if yes, can i create all kinds like unique, clustered, mdc etc indexes in...
0
by: Hevan | last post by:
Hi, Can we use declared global temporary table as source table to merge data into a table in db2 8.2.
3
by: Lennart | last post by:
Any thoughts on the following scenario anyone? During a performance test I discovered that the application asked one specific query extremely often. It turned out that this particular query...
1
by: romV | last post by:
Hi, I have a procedure that creates the global temp table (##temp) inside a procedure which is populated with data when it is run for the first time. And when the procedure is executed second time,...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.