Re: Dawg process using Temp table or array

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

Amazing, just received the following from PeopleSoft....
============
> Here is the latest update.  This issue has been resolved in SQR v4.3.4 and
> will be shipped with PeopleTools 7.06 which will be released for General
> Availability on October 5, 1999.  It will also be shipped with PeopleTools
> 7.56 which will be released for General Availability on September 1, 1999.
>
> Discussion:
> 06/02/99 09:17:02 AM PDT: Fixed in SQR v4.3.4.  Here are comments from
> SQRIBE:
>
> Re defect 7051:  In our lab, the test case submitted for this defect ran
> in 2-3 seconds with 3.0.18.1, ~25 seconds with 4.3.3, and 3-5 seconds with
> 4.3.4. Here is information as to why 4.3.4 will still be a little slower
> than SQR3:
>
> There is a multitude of changes (and corrections) that is most likely
> causing the slow down:
>
> o   V3 only supported two types of array elements (number and char)
> whereas V4 supports 6 (number, char, integer, float, date, and decimal).
>
> o   V3 did not do garbage collection whereas V4 does. This was actually a
> severe problem as SQR's memory usage increased as array elements were
> inserted. Eventually SQR would run out of memory.
>
> o   V3 did not support "date" array elements. In this test case there are
> two DATE columns. SQR now  has to convert DATE to CHAR for each of the
> insertions.
>
> o   V3 did not support "initialization" values for array elements whereas
> V4 does. This was done the for the CLEAR-ARRAY command so we now need to
> be aware of that fact.
>
>
>
> ----------
> From:         Fehl, Douglas
> Sent:         Wednesday, June 02, 1999 12:33 PM
> To:   'SQR-USERS@list.iex.net'
> Subject:      RE: Dawg process using Temp table or array
>
> 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.
>
>