Manage large data sets

If users run stored procedures on large data sets, there might be situations where there is not enough memory to hold the results of a select on that table. For example, some stored procedures can read each record from a table and take action on each record:

FOR rec in SELECT * from tablenm LOOP

--perform processing steps

END LOOP:

The SELECT operation runs first and caches its results in memory or as a temporary file on disk, depending upon the size of the result set. The procedure then applies the steps in the inner processing loop.

If the table (tablenm) is very large, such as a table with millions of rows or one with many thousands of large rows, the temporary file can be a huge file that uses the free disk space on the host. Use caution when your stored procedures process very large tables.

Note: In the Netezza Performance Server environment, these types of select loops that operate on single rows are not optimized for performance in the Netezza Performance Server environment. Where possible, recast the loop to operate on record sets.