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
9  17561 
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
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
>>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
intermediat e 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
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.
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
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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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 and it means
that an object doesn't exist. This block of code below works fine on my
local development machine, but not on our shared development server
until I go into the tempdb and make the user have the role db_owner.
Even wierder is that...
|
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 The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "create global temporary table temp (
OGI_S" was
|
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
|
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)
|
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 global temp table ?
please direct me to some web resource where i can find what all i can
do and i cannot do with global temp tables
| | |
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.
|
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 where asked 25/50/100 or 200 times from a "htmlpage", dependent
of user preferences. I figured that using a global temp table, looping
and inserting, then join would do the trick.
However, it turned out that this killed performance totally. Why,...
|
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, it uses that temp table instead of loading it all over.
The trouble I am having is that when I execute the procedure from Asp.NET the temp table stays there as long as i call the same procedure, but as soon as I call another procedure the temp...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| | |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
