Dawg process using Temp table or array

[David Smith <david.a.smith@AC.COM>]

Forwarding on behalf of a collegue using Informix database and PeopleSoft:


I am currently trying to execute an outbound SQR interface program.  The program
was originally created to use a temporary table that contained 25 fields.  The
processing time for the program was approximately 4 hours.

In an attempt to improve processing time, I modified the program to utilize an
array rather than a temp table.  Unfortunately using the array did little to
improve processing time.

It appears as though the program is spending a significant amount of processing
time, attempting to retrieve enough space for the temp table/array.  The program
exhausts several hours trying to allocate space for the specified array, prior
to performing any of the functional logic.

The array was set up with 25 fields and 10,000 elements.  The array needs to be
able to handle at least 7,000 rows of data.

To gain a better understanding of processing time required to allocate space for
an array, I ran the program several times varying the element size.

If the array were to utilize 10 elements the processing time would be
instantaneous.  Using 100 elements the processing time to retrieve space jumps
to 3 minutes.  When the array size is 1000 elements the processing time to
retrieve space is 22 minutes.  An array of 10,000 elements requires a processing
time of over 2 hours.


Questions:
1) Does this problem sound familiar to anyone?
2) Is the processing time problem an attribute of the instance or the actual SQR
program?
3)  Where would be the first places to begin looking for processing
improvements?

I am currently only interested in processing time improvements related to the
space allocation of temp tables and arrays.

Any advice would be greatly appreciated.  Thanks in advance.