GTT vs DGTT Timing Results

Roland A H07A DePratti

GTT vs DGTT Timing Results

Thanks to all for responding to my initial post.
I did get a chance to complete a performance comparison between the two
methods and found some interesting results I wanted to share.

To level set, I wanted to compare the two methods in a situation where data
services as part of an SOA implementation, coded as Stored Procedures, are
returning results sets. The assumption is that the caller will fully consume the
result set in the order that it was populated.

The test driver executed the sample SP 26000+ times passing both key
information for the data access, as well as supplied whether the SP should use
a GTT or DGTT. The results sets were of various sizes from 1 - 322,000 rows
with the following breakdown:

Total Cursors 26,059
Cursor w/ rows > 100,000 2
Cursors with rows between 50,000 and 99,999 10
Cursors with rows between 10,000 and 49,999 188
Cursors with rows between 1,000 and 9,999 516
Cursors with rows between 500 and 999 339
Cursors with rows between 1 and 499 25,004

I captured performance data through DB2PM, as well as
taking application timestamp snapshots for before and after SP execution,
as well as Declare statements.

For the scenario I tested, my test results favored GTT heavily:

Times are reported in HH:MM:SS.SS

Total Runtime 0:17:25.69 5:43:48.23
Total SP Elapsed 0:12:58.29 4:33:33.68
Total SP CPU 0:02:50.65 0:09:27.92
Total Temp Create Time 0.0 3:55:58.0
Total Global Contn Time 0:00:00.26 4:55:57.68
Avg SP Elapsed 0.039 0.646
Avg Create Time 0.0 0.5437

This is much larger than expected. We confirmed that both the driver and the
SP were bound using RELEASE(DEALLOCATE).

As always, any input you have on this is welcome and encouraged.


- rd

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at