Prev: Simple hack to get $500 to your home.
Next: FK's on Federated Databases
From: Serge Rielau on 14 Jul 2010 12:14
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

 | 
Pages: 1
Prev: Simple hack to get $500 to your home.
Next: FK's on Federated Databases