 | |
Arrays vs Declared Global Temp Tables for Stored Procs in DB2 9.5
comp.databases.ibm-db2 comp.databases.ibm-db2
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Arrays vs Declared Global Temp Tables for Stored Procs in DB2 9.5 -
07-13-2010
, 11:45 PM
#2
| |||
| |||
|
|
I am using DB2 9.5.5 and cannot migrate to 9.7 anytime soon, so I cannot use Created Global Temporary Tables (CGTT). I am wondering how Arrays are implemented for stored procedures and what the performance is compared to declared global temporary tables (DGTT). In order to improve performance, I want to use arrays instead of a DGTT, but I want to make sure that Arrays are not creating a DGTT under the covers. Assume that Arrays provides me with the functionality I need. I'm actually preparing a talk on the topic. |
For now: ARRAYs are a continuous piece of memory allocated on the
application heap. At lot less overhead than all the table infrastructure.
On the flip side if you have some open-ended and huge arrays paired with
many concurrent users... well you do the math.
Of course the API for ARRAY (SET, ARRAY_AGG, UNNEST) is fundamentally
different from DGTT (UPDATE, DELETE, INSERT, DECLARE cursor)
Cheers
Serge
PS: If you do a nested loop between two arrays that's what you get. No
optimizer is going to rewrite your join...
--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab
#3
| |||
| |||
|
|
On 7/14/2010 12:45 AM, Martin wrote:> I am using DB2 9.5.5 and cannot migrate to 9.7 anytime soon, so I cannot use Created Global Temporary Tables (CGTT). I am wondering how Arrays are implemented for stored procedures and what the performance is compared to declared global temporary tables (DGTT). In order to improve performance, I want to use arrays instead of a DGTT, but I want to make sure that Arrays are not creating a DGTT under the covers. Assume that Arrays provides me with the functionality I need. I'm actually preparing a talk on the topic. For now: ARRAYs are a continuous piece of memory allocated on the application heap. At lot less overhead than all the table infrastructure. On the flip side if you have some open-ended and huge arrays paired with many concurrent users... well you do the math. Of course the API for ARRAY (SET, ARRAY_AGG, UNNEST) is fundamentally different from DGTT (UPDATE, DELETE, INSERT, DECLARE cursor) Cheers Serge PS: If you do a nested loop between two arrays that's what you get. No optimizer is going to rewrite your join... -- Serge Rielau SQL Architect DB2 for LUW IBM Toronto Lab |
maybe?
#4
| |||
| |||
|
|
On Jul 14, 9:14 pm, Serge Rielau<srie... (AT) ca (DOT) ibm.com> wrote: On 7/14/2010 12:45 AM, Martin wrote:> I am using DB2 9.5.5 and cannot migrate to 9.7 anytime soon, so I cannot use Created Global Temporary Tables (CGTT). I am wondering how Arrays are implemented for stored procedures and what the performance is compared to declared global temporary tables (DGTT). In order to improve performance, I want to use arrays instead of a DGTT, but I want to make sure that Arrays are not creating a DGTT under the covers. Assume that Arrays provides me with the functionality I need. I'm actually preparing a talk on the topic. For now: ARRAYs are a continuous piece of memory allocated on the application heap. At lot less overhead than all the table infrastructure. On the flip side if you have some open-ended and huge arrays paired with many concurrent users... well you do the math. Of course the API for ARRAY (SET, ARRAY_AGG, UNNEST) is fundamentally different from DGTT (UPDATE, DELETE, INSERT, DECLARE cursor) Cheers Serge PS: If you do a nested loop between two arrays that's what you get. No optimizer is going to rewrite your join... -- Serge Rielau SQL Architect DB2 for LUW IBM Toronto Lab Can you please share the presentation after your talk or in ur blog maybe? Absolutely. After IDUG Europe and IOD Las Vegas. |
--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab
#5
| |||
| |||
|
|
For now: ARRAYs are a continuous piece of memory allocated on the application heap. At lot less overhead than all the table infrastructure. On the flip side if you have some open-ended and huge arrays paired with many concurrent users... well you do the math. Of course the API for ARRAY (SET, ARRAY_AGG, UNNEST) is fundamentally different from DGTT (UPDATE, DELETE, INSERT, DECLARE cursor) Cheers Serge PS: If you do a nested loop between two arrays that's what you get. No optimizer is going to rewrite your join... -- Serge Rielau SQL Architect DB2 for LUW IBM Toronto Lab |
(compared to GTT), but seem to run much slower in 9.5. Is this to be
expected? We are testing the exact same SP's both releases.
#6
| |||
| |||
|
|
"Serge Rielau"<srielau (AT) ca (DOT) ibm.com> wrote in message news:8a663mFn5sU1 (AT) mid (DOT) individual.net... For now: ARRAYs are a continuous piece of memory allocated on the application heap. At lot less overhead than all the table infrastructure. On the flip side if you have some open-ended and huge arrays paired with many concurrent users... well you do the math. Of course the API for ARRAY (SET, ARRAY_AGG, UNNEST) is fundamentally different from DGTT (UPDATE, DELETE, INSERT, DECLARE cursor) PS: If you do a nested loop between two arrays that's what you get. No optimizer is going to rewrite your join... My developers are reporting that SP's with arrays are performing well in 9.7 (compared to GTT), but seem to run much slower in 9.5. Is this to be expected? We are testing the exact same SP's both releases. Interesting... no significant design changes were done. |
--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.