GTT vs DGTT Performance (V8)

Roland A H07A DePratti

GTT vs DGTT Performance (V8)
Hi folks,

I have read many of the entries on temp tables in the archives, but still have a
basic performance question.

For an SOA type application where Stored Procedures are performing data
services work and results sets are returned as data, is there a performance
diffferences between GTT and DGTTs. (DB2 V8 Z/OS)

Some assumptions:

1. the business layer will consume all rows in the result set.
2. the b. layer will read the result in insert order
3. there is no need to update or delete a partial result sets.
4. multiple threads will be need to create their own instance of the result set.
5. no logging or recovery is required. Error results in a bad return code and re-
application-driven execution.

My gut tells me that the bells and whistles have built in overhead and that in
the scenario I paint above GTTs would perform better.

Has anyone had experiences with the tradeoffs and is there something missing
in my logic?

Thanks

Roland Depratti
CIGNA DPAS

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

James Campbell

Re: GTT vs DGTT Performance (V8)
(in response to Roland A H07A DePratti)
Only that DGTTs can have indexes - which might have a good performance impact; but have
their own overheads (at least until V9).

James Campbell



On 20 Nov 2007 at 16:19, rdpratti wrote:

> Hi folks,
>
> I have read many of the entries on temp tables in the archives, but still have a
> basic performance question.
>
> For an SOA type application where Stored Procedures are performing data
> services work and results sets are returned as data, is there a performance
> diffferences between GTT and DGTTs. (DB2 V8 Z/OS)
>
> Some assumptions:
>
> 1. the business layer will consume all rows in the result set.
> 2. the b. layer will read the result in insert order
> 3. there is no need to update or delete a partial result sets.
> 4. multiple threads will be need to create their own instance of the result set.
> 5. no logging or recovery is required. Error results in a bad return code and re-
> application-driven execution.
>
> My gut tells me that the bells and whistles have built in overhead and that in
> the scenario I paint above GTTs would perform better.
>
> Has anyone had experiences with the tradeoffs and is there something missing
> in my logic?
>
> Thanks
>
> Roland Depratti
> CIGNA DPAS
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Roland A H07A DePratti

Re: GTT vs DGTT Performance (V8)
(in response to James Campbell)
James,

Thanks for the reply. I understand the index advantage, but my understanding
is that it is a choice. A DGTT can be defined without indexes. If I didn't need
the indexes or other features would it be beneficial to stick with GTTS. For
example, is there overhead to plain vanila DGTTs that do not exist in GTTs .
For a simple application like I described, that wouldn't need the DGTT bells and
whistles, would GTTs be the better choice from a perfor,mance perspective?

I was planning on setting up an empirical test, but was wondering if you had
worked out that question before.

Thanks,

- Roland DePratti

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

James Campbell

Re: GTT vs DGTT Performance (V8)
(in response to Roland A H07A DePratti)
Have a read of SG24-7134 DB2 UDB for z/OS: Application Design for High Performance and
Availability. It has a section that covers just about everything I know.

James Campbell

On 21 Nov 2007 at 8:27, rdpratti wrote:

> James,
>
> Thanks for the reply. I understand the index advantage, but my understanding
> is that it is a choice. A DGTT can be defined without indexes. If I didn't need
> the indexes or other features would it be beneficial to stick with GTTS. For
> example, is there overhead to plain vanila DGTTs that do not exist in GTTs .
> For a simple application like I described, that wouldn't need the DGTT bells and
> whistles, would GTTs be the better choice from a perfor,mance perspective?
>
> I was planning on setting up an empirical test, but was wondering if you had
> worked out that question before.
>
> Thanks,
>
> - Roland DePratti
>



IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Larry Jardine

Re: GTT vs DGTT Performance (V8)
(in response to James Campbell)
I haven't seen anyone mention this, but the use of DGTT in a package
makes it dynamic. So any existing SQL statements in that package
(whether they use the DGTT or not) now become dynamic SQL. This makes
debugging performance more difficult (no explain data available) and
also necessitates a Bind at every execution of the package.

BTW, I have to state my dislike of the names for the different types of
temporary tables. Created Temp Tables are declared in a program and
Declared Temp Tables are created in a program!

Or did I get it backwards?


Larry Jardine
Production DBA
TDM Website: http://opsvcs.aetna.com/oltpdb/

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of rdpratti
Sent: Wednesday, November 21, 2007 8:27 AM
To: [login to unmask email]
Subject: Re: [DB2-L] GTT vs DGTT Performance (V8)

James,

Thanks for the reply. I understand the index advantage, but my
understanding is that it is a choice. A DGTT can be defined without
indexes. If I didn't need the indexes or other features would it be
beneficial to stick with GTTS. For example, is there overhead to plain
vanila DGTTs that do not exist in GTTs .
For a simple application like I described, that wouldn't need the DGTT
bells and whistles, would GTTs be the better choice from a perfor,mance
perspective?

I was planning on setting up an empirical test, but was wondering if you
had worked out that question before.

Thanks,

- Roland DePratti

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm
This e-mail may contain confidential or privileged information. If
you think you have received this e-mail in error, please advise the
sender by reply e-mail and then delete this e-mail immediately.
Thank you. Aetna

IMPORTANT NOTICE:

IDUG is pleased to announce a series of upgrades to the DB2-L discussion listserv that are being implemented to improve reliability and the overall user experience of DB2-L. These changes are coming on November 30th. Details at http://www.idug.org

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm