Occasionally, it is necessary to utilize a cursor within a stored procedure to execute specific functionality. I recently encountered a stored procedure that contained a loop with multiple INSERT statements executed through a cursor. This particular cursor was designed to process only a limited number of rows. Despite this, the stored procedure took up to 6 hours, depending on the system’s current load.

In this case, a recursive or window function could have been used for a more efficient solution. However, the presented logic was convoluted and reverse engineering would have been too time-consuming due to a lack of clear specifications.

The stored procedure’s cursor utilized multiple volatile tables. I inferred that it should be possible to enhance performance by localizing the work for this limited amount of data.

The objective was twofold: consolidate the work onto a single AMP to circumvent utilizing ROWHASH for data distribution, and store the data in primary memory, which proves unchallenging for a quantity of several thousand rows.

I opted to use Teradata’s MAPS feature.

I created all volatile tables on a single AMP map to consolidate the rows into one AMP and increase the likelihood of caching or main memory storage.


The stored procedure modification significantly decreased the runtime from six hours to just a few minutes.

I trust I have presented you with an additional instrument to aid performance optimization. Occasionally, a slight alteration can bring about a significant enhancement. Before devising the MAPS Architecture concept, I spent numerous hours attempting to comprehend the difficulty that the stored procedure aimed to rectify. Regrettably, due to the code’s lack of specifications or documentation, I eventually had to concede defeat.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

You might also like