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.
CREATE TABLE DWHPRO.LocalizedWork, MAP=SingleAMP_Map
(a1 INTEGER, b1 INTEGER) UNIQUE PRIMARY INDEX (a1);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.
