Improving Stored Procedure Performance with Teradata MAPS Architecture
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.
Want more practical data engineering analysis like this?
Join DWHPro Letters and get field-tested notes on Teradata, Snowflake, AI, migrations, performance, and enterprise data work. DWHPro Letters is free. Subscribe to get new issues by email.
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.
Get the next issue by email.
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.
Planning or surviving an enterprise data platform migration?
I write regularly about the performance, cost, architecture, and project mistakes that show up in real Teradata, Snowflake, Databricks, and enterprise data work.
Subscribe for free and keep launch access.
Written by Roland Wenzlofsky, founder of DWHPro and author of Teradata Query Performance Tuning. DWHPro has helped data warehouse practitioners for 15+ years.