Sometimes you can’t avoid implementing a certain functionality by means of a cursor in a stored procedure. Recently I had to deal with a stored procedure in which several INSERT statements were executed in a loop in a cursor. In the concrete case, a cursor had to process only a few thousand rows. Nevertheless, the runtime of the stored procedure was up to 6 hours depending on the load of the system.
In the given case, a solution using recursion or even window functions would have been possible, which would probably have had a better runtime, but the logic shown was complex, and due to missing specifications, reverse engineering of the logic was more expensive than I was willing to invest time.
In the cursor of the stored procedure, several volatile tables were used. I reasoned that improving performance by localizing the work for this small amount of data must be possible.
On the one hand, the goal was to get the work onto one AMP and thus avoid distributing the data using the ROWHASH. Secondly, the data should be kept in the main memory, which could not be a problem for a few thousand rows.
Therefore, I decided to use the MAPS feature of Teradata.
Finally, I created all volatile tables using a single-AMP map. This ensured that all rows were on one AMP and could very likely be cached or stored in main memory:
CREATE TABLE DWHPRO.LocalizedWork, MAP=SingleAMP_Map (a1 INTEGER, b1 INTEGER) UNIQUE PRIMARY INDEX (a1);
This change in the stored procedure reduced the runtime from 6 hours to a few minutes.
I hope I have introduced you to another tool that will be helpful for performance tuning. Sometimes a big improvement can be caused by a tiny change. Before I came up with this idea of using the MAPS Architecture, I had spent several hours trying to understand the problem that the stored procedure was supposed to solve, but due to the lack of specification or documentation in the code, I finally had to give up.