Stored Procedure Stage Performance Issue

Ultramundane · Post by **Ultramundane** » Fri Dec 02, 2005 9:58 am

Hi,

We are using Ascential DS 7.5.1A with the following patches applied:
75003,
80895,
80559,
80036,
76017,
20959

The Stored Procedure Stage caches all rows before it starts to write its output. This causes many gigabytes of memory to be used and the job to be aborted. I have tried to use no buffer and to set the job to parallel execution. Seems the default is sequential. Still no luck.

The only way I can get procedures to execute effectively is through the ODBC Enterprise Stage.

Is there a way to have the stored procedure stage writes its output streaming as the other stages do?

kumar_s · Post by **kumar_s** » Sat Dec 03, 2005 5:51 am

Hi,
If the operator requires the process of all the data before output of single data, i need to be buffered.
And cannot correlate to execution mode to buffereing mode.
Even sequential mode can be buffered. Some stages like sort which need the buffer all the data, requires large disk size.
Better to increse the size scratch disk.

-Kumar

Ultramundane · Post by **Ultramundane** » Mon Dec 05, 2005 8:25 am

It is blowing up memory!!! IT IS NOT USING THE SCRACTH DISK AT ALL!!! That is problem. It is buffering the entire result set in memory and not using disk at all.