Re: Dawg process using Temp table or array

["Fehl, Douglas" <dfehl@HERSHEYS.COM>]

Release 4.3.2 of SQR which was delivered with PT 7.04 has a problem with
array performance.

PeopleSoft opened a bug report with Sqribe and we still await a resolution.
You might want to check PS Customer Connection to see the details.

The case number is 473244.
The incident id is T-SBACA-2U9E0.

> ----------
> From:         David Smith[SMTP:david.a.smith@AC.COM]
> Sent:         Wednesday, June 02, 1999 11:59 AM
> To:   Multiple recipients of list SQR-USERS
> Subject:      Dawg process using Temp table or array
>
> 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.
>