Efficient use of Teradata Recursive Queries

Teradata Recursive Queries

SQL offers two methods to create a Teradata recursive query. We can either create a query using the WITH RECURSIVE clause or create a view using the CREATE RECURSIVE VIEW statement.

Each recursive query consists of 2 or 3 parts, depending on which of above approaches is used:

• The seed statement is the initial query which is executed.
• The recursive statement is the repeating query which is executed until no further rows are returned. To avoid infinite loops, we usually add a termination condition to the recurrent query. Without a termination condition, the user may run out of spool space.
• The final query returns the result of the seed query and all iterations of the recursive query.

Creating a recursive query using the WITH RECURSIVE clause:

WITH RECURSIVE <query>
(<columns>) AS
(
<seed query>
UNION ALL
<recursive query>

)
<final query>;

Creating a recursive query using a RECURSIVE VIEW:

CREATE RECURSIVE VIEW <view>
(<columns>) AS
(
<seed query>
UNION ALL
<recursive query>

);

To get a better understanding of how recursive queries work on Teradata, I prepared a common problem we want to solve: Finding the shortest paths in a graph.

Shortest Path – The Recursive Solution

We will store all available legs in below table:

CREATE SET TABLE Leg
(
From_Id INTEGER NOT NULL,
To_Id INTEGER NOT NULL
)
PRIMARY INDEX (From_Id);

/* Example Graph */

INSERT into Leg VALUES (‘1′,'2');
INSERT into Leg VALUES (‘2′,'1');
INSERT into Leg VALUES (‘2′,'3');
INSERT into Leg VALUES (‘2′,'7');
INSERT into Leg VALUES (‘3′,'2');
INSERT into Leg VALUES (‘3′,'4');
INSERT into Leg VALUES (‘3′,'5');
INSERT into Leg VALUES (‘3′,'6');
INSERT into Leg VALUES (‘6′,'7');
INSERT into Leg VALUES (‘6′,'3');
INSERT into Leg VALUES (‘7′,'2');
INSERT into Leg VALUES (‘7′,'6');
INSERT into Leg VALUES (‘7′,'8');
INSERT into Leg VALUES (‘7′,'9');
INSERT into Leg VALUES (‘8′,'7');
INSERT into Leg VALUES (‘9′,'7');
INSERT into Leg VALUES (‘5′,'3');
INSERT into Leg VALUES (‘4′,'3');

The solution with a recursive query:

WITH RECURSIVE ThePath
(From_Id, To_Id, Path, TheLength) AS
(
SELECT
From_Id
,To_Id
,(TRIM(From_Id) || ‘,' || TRIM(To_Id)) (VARCHAR(512)) AS Path
, 1 AS TheLength
FROM
Leg
WHERE
From_Id = ‘1'
UNION ALL
SELECT
ThePath.From_Id
,t01.To_Id
,ThePath.Path || ‘,' || TRIM(t01.To_Id)
,ThePath.TheLength + 1 AS TheLength
FROM
Leg t01
INNER JOIN
ThePath
ON
t01.From_Id = ThePath.To_Id
WHERE POSITION(‘,' || TRIM(t01.To_Id) || ‘,' IN ‘,' || ThePath.Path || ‘,') = 0
— Above WHERE condition ensures that we do not revisit a node a second time!
AND ThePath.TheLength <= 100
— Avoid out of spool situations, put a fixed stop after 100 recursions!
)

/* Below statement ensures that if there are multiple routes between two nodes, one of
the minimum numbers of stops are chosen */

SELECT
From_Id,
To_Id,
Path,
TheLength
FROM ThePath
QUALIFY ROW_NUMBER() OVER (PARTITION BY From_Id, To_Id ORDER BY TheLength, Path) = 1
ORDER BY 1,4,3;

Here is the result set of the query, showing all minimum distance routes from node 1

Shortest Path – The Non-Recursive Solution

We will now compare the recursive query with a solution written as Stored Procedure:

CREATE SET TABLE Route
(
From_Id INTEGER NOT NULL,
To_Id INTEGER NOT NULL,
Path VARCHAR(512),
TheLength INTEGER
) PRIMARY INDEX (From_Id);

REPLACE PROCEDURE ThePath()
DYNAMIC RESULT SETS
BEGIN
DELETE FROM ShortestPath;
INSERT INTO ShortestPath
SELECT
From_Id,
To_Id,
(TRIM(From_Hub_Id) || ‘,' || TRIM(To_Hub_Id)) (VARCHAR(512)) AS Path,
1 AS TheLength
FROM Leg
WHERE From_id = ‘1';

WHILE ACTIVITY_COUNT > 0 DO
INSERT INTO Route
SELECT
t02.From_Id
t01.To_Id
t02.Path || ‘,' || TRIM(t01.To_Id)
t02.TheLength + 1
FROM Leg t01, Route t02
WHERE
t01.From_Id = t02.To_Id
AND t01.To_Id <> ‘1'
AND t02.TheLength =
(SELECT MAX(TheLength) FROM Route)
AND t01.To_Id NOT IN
(SELECT To_Id FROM Route)
AND B.TheLength < 200
QUALIFY ROW_NUMBER() OVER (PARTITION BY t01.To_Id ORDER BY t02.Path) = 1;
END WHILE;
BEGIN
DECLARE mycursor CURSOR WITH RETURN ONLY FOR
SELECT * FROM Route  ORDER BY 4, 3;
OPEN mycursor;
END;
END;

In general, it can't be said which  solution is faster or slower. It depends in how data structures accessed look like.

In this specific example, the stored procedure consumes fewer IOs and CPU that the recursive query.

There are several reasons:

• Our stored procedure keeps a routes table of all visited nodes, while the recursive query might revisit the same node several times.
• The recursive query continues to iterate even after the shortest path between two nodes has already been found. Running additional recursive steps increases the spool usage quickly.

10 Reasons for Organizing Stored Procedure into Chapters and Steps

I made organizing Stored Procedures into Chapters and Steps a principle.

These points of separation are clearly marked by comments and by variables used in performance and result logging.

A step is one specifc technical or conceptual task that cannot be divided into further pieces without
leaving the level of abstraction of Stored Procedures or the task at hand. It could be the creation of an intermediate table, deletion of records, a calculation or a logging step.

A chapter is a group of steps that all contribute toward the achievement of one intermediate of final result.
I assign steps a number and let them increase in increments greater than one.
The first digit of the Step is the Chapter number, so that it is immediately clear where the step stems from.

Why do I engage in what you might think is nothing but embellishment?
If only some of the following arguments seem convincing, you should seriously consider chapters and steps in Stored Procedure code from now on:

1. Reflection of worthiness:

2. Means of self discipline

3. Facilitation of documentation

4. Unit of testing and debugging

5. Predefined elements of performance measurement

6. Standardization

7. Facilitation of communication

8. Evolutionary Advantage

9. Data Flow control

10. Leaving a good legacy

See also:
Teradata SQL Stored Procedures – Cursors

Teradata Stored Procedures – Dynamic or Static SQL

Characteristics of Static SQL

• The SQL statement code cannot be changed during the Stored Procedure execution
• Variables and parameters can be used to replace literals in the SQL statement
• Static SQL statements are executed as soon as code execution reaches the statement
• Many errors can be detected already during compilation

Characteristics of Dynamic SQL

• The SQL statement code can be altered during the Stored Procedure execution
• Variables and parameters can be used to replace anything in the SQL statement
• Dynamic SQL Statements have to be executed by invocation or the opening of a cursor
• Fewer errors are detectable during compilation

Static and dynamic SQL can be mixed up in a Teradata stored procedures, depending on the requirements.

Let's makes things clearer with the help of some examples

Static SQL examples:

UPDATE TESTTABLE SET COL = 1 WHERE COL = 2;
UPDATE TESTTABLE SET COL = 1 WHERE COL = :n ; — n is a declared variable
UPDATE TESTTABLE SET COL = 1 WHERE COL = n ; — n is a declared variable

Static SQL is put directly into the stored procedure. It is allowed to use variables and parameters and will be executed as soon as code execution reaches the statement.

Dynamic SQL examples:

SET sqltext = ‘UPDATE TESTTABLE SET COL = 1 WHERE PK = ?';
SET sqltext = ‘UPDATE TESTTABLE SET COL = 1 WHERE COL = ‘ || n ||'; ‘;
SET sqltext = ‘UPDATE TESTTABLE SET COL = 1 WHERE COL = ‘ || :n ||'; ‘;

Dynamic SQL is always assigned to a character variable, before being executed.

As a result, many dynamic SQL errors are not detectable during compile time. Let’s analyze this by looking at the dynamic SQL example below:

REPLACE PROCEDURE TEST_SP()
BEGIN
DECLARE mysql VARCHAR(1000);
SET mysql = ‘SELECT COL FROM TESTTABLE';
CALL DBC.SYSEXECSQL(mysql);
END;

CALL TEST_SP();

Above stored procedure will compile without any problems, but will create a runtime error as the executed SQL statement is not valid (it returns a result set which is not assigned to a cursor).

Now let’s change the stored procedure from above and use static SQL:

REPLACE PROCEDURE TEST_SP()
BEGIN
DECLARE mysql VARCHAR(1000);
SELECT COL FROM TESTTABLE;
END;

CALL TEST_SP();

The above shown stored procedure will cause a compile time error.

There is another critical impact when using volatile tables in a stored procedure.
During compile time, availability of all referenced objects is checked. This means, you have to make volatile tables which are created by static SQL have to be available during compile time, otherwise compilation will fail:

REPLACE PROCEDURE TEST_SP()
BEGIN
CREATE VOLATILE TABLE VOLTEST
(
PK INTEGER
) PRIMARY INDEX (PK);
END;

CALL TEST_SP();

By wrapping the creation of a volatile table into dynamic SQL, no compile time check can take place and therefore there is no need to have the volatile tables available at compile time:

REPLACE PROCEDURE TEST_SP()
BEGIN
DECLARE mysql VARCHAR(1000);
SET mysql = ‘CREATE VOLATILE TABLE VOLTEST
(
PK INTEGER
) PRIMARY INDEX (PK);’;

CALL DBC.SYSEXECSQL(mysql);
END;

CALL TEST_SP();

The Simple Solution To Error Handling in Teradata Stored Procedures

Introduction to Teradata Stored Procedures & Error Handling

Error handling in Teradata Stored Procedures is condition based, having the same functionality offered by modern programming languages (C++, Java, etc.).

Without the availability of state-based error handling one would need to implement and invoke code for each type of error and at each code location where the error could occur.

Not only is this a tremendous effort from a development point of view, but some error handling can easily be forgotten about.

Below you can see one example of such a programmatic approach, handling division by zero errors.

REPLACE PROCEDURE DIVISION_BY_ZERO (IN n INTEGER)
BEGIN
DECLARE x INTEGER DEFAULT 10;
— Error Handling programmatically
CASE
WHEN n = 0 THEN SET x = 0; ELSE SET x = (x/ n);
END CASE;
END;

CALL DIVISION_BY_ZERO(0);

When using error handlers, the handler code for a certain error is defined once within the handler and executed automatically each time the error condition is raised.

Below you see the division by zero problems solved with an error handler. The error code ‘22012’ is returned in case of a division by zero error:

REPLACE PROCEDURE DIVISION_BY_ZERO (IN n INTEGER)
BEGIN
DECLARE x INTEGER DEFAULT 10;
DECLARE EXIT HANDLER FOR SQLSTATE ‘22012' SET x = 0; — Error Handler

SET x = (x/n);
END;

CALL DIVISION_BY_ZERO(0);

It is possible to define error handlers which are responsible for all errors or warnings (generic handlers), they will be executed if a particular error handler does not already cover an error/warning.

The developer does not need to know in advance all possible kind of errors and warning.

Generic handler is usually used to do the cleanup before exiting a stored procedure. See below two examples for generic exception and warning handlers:

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET x =0;
DECLARE CONTINUE HANDLER FOR SQLWARNING SET x =0;

Each stored procedure can have exactly

• One generic handler for exceptions (SQLEXCEPTION)
• One generic handler for warnings (SQLWARNING)
• One generic handler in case a cursor does not return any data (NOTFOUND)
• Per code block (BEGIN…END) and error code exactly one specific handler
The same error code can be handled in different code blocks.

Within a stored procedure, all executed SQL statements return a status which can be: completed, warning or exception. Exceptions and warnings can be handled by an error handler.

The status of each executed SQL statement is returned in the read-only variable SQLSTATE. This available is implicitly available in each Stored Procedure usually, is used within generic error handlers to find out further details about an error or warning.

There exists another read-only variable called SQLCODE, but it is obsolete and should not be used anymore.

SQLSTATE consists of 5 characters. The first two characters define the error class. The remaining three characters represent the error.

Many of the error codes returned by Teradata are mapped 1:1 to the SQLSTATE variable (but not all of them). For example, the Teradata error code 3776 is assigned to T3776 (Error class T3 = Teradata 3).

There are two main types of error handlers: Exit handlers and continue handlers.

The Exit Handler

Whenever the execution of exit handler is finished, the normal code execution continues with the block (defined by BEGIN…END) which is surrounding the statement which caused the exception. Apparently, if there is no surrounding block, the stored procedure is terminating, but this is an exception. In general, don’t get fooled by the name “exit handler,” assuming the stored procedure will end. Only the block causing the handler execution is terminated, and the code execution is passed to the surrounding block.

The Continue Handler

The continue handler continues with the next statement after the one which triggered the handler execution.

Raising of conditions

Usually, the following events raise conditions which will trigger handler execution:

• SQL status exception or warning (after executing an SQL statement)

• The SIGNAL statement which is used to raise an exception programmatically

• The RESIGNAL statement passed by a continue handler to the surrounding block (BEGIN…END)

Handler Propagation

Handlers can be declared in each data block (BEGIN…END). Different data blocks can handle different errors or even the same errors. In the case of an error, the code blocks are searched for an appropriate handler, starting with the block were the error occurred until the outermost BEGIN…END is reached. If none of the code blocks contains a handler, the stored procedure will exit with an error.

In any case, after executing a continue handler, code execution persists in the data block where the error triggered. In the event of an exit handler, the code block containing the error triggering statement will be terminated, and code execution continues with the outer block (if any).

In case an error is raised within a handler, the handler execution is terminated, and the stored procedure tries to find another handler by continuing searching the data blocks from the innermost to the outermost. Be aware, that an error event in the handler will not try to solve this handler error but the original error which leads to the handler execution!

Below is an example for nested handlers. We nested three handlers for the division by zero condition. Intentionally we cause another division by zero exception (SET x=1/0) in two of the three handlers. Therefore the outermost handler finally is the one who will handle this error:

REPLACE PROCEDURE DIVISION_BY_ZERO ( IN n INTEGER)
BEGIN
   DECLARE x INTEGER DEFAULT 10.00;
   DECLARE CONTINUE HANDLER FOR SQLSTATE ‘22012' SET x=0; — handles the error
   BEGIN
   DECLARE EXIT HANDLER FOR SQLSTATE ‘22012' SET x = 1/0;
   BEGIN
       DECLARE EXIT HANDLER FOR SQLSTATE ‘22012' SET x = 1/0;
       SET x = x / n;
   END;
END; END;

CALL DIVISION_BY_ZERO(0);

Mistakes To Avoid When Using Cursors in Teradata Stored Procedures

What is a Cursor in a Teradata SQL Stored Procedure?

When we are talking about Cursors in Teradata SQL stored procedures, we refer to a pointer to a data record in the result set of an SQL query:

SELECT * FROM table_name;

Cursors are used to iterate over a result set from one row to the next. They are a means to perform record oriented data processing in the context of set-oriented data processing implemented with SQL. Record-to record activity is usually the realm of a procedural language like C or C++.

When we talk about query result sets here, we refer to Teradata spool space holding the result set of a query at one particular point in time.

Why do we need Cursors in Teradata Stored Procedures?

Without the usage of cursors, only SQL statements returning exactly one data record can be issued. Here is such an example:

SELECT COUNT(*) INTO variable_name  FROM table_name;

The above SQL returns exactly one data record. The result, therefore, can be easily assigned to a stored procedure variable, and no cursor is needed. Now let us take a look at another example:

SELECT column_name INTO variable_name  FROM table_name; 

The second example only will work if table_name contains exactly one row. Otherwise, a run-time error is thrown. Typically a point of departure for the introduction of cursors to your query processing arsenal.

Cursor States and Positioning

Cursors have to be opened before they can be used. By opening a cursor, the SQL statement is executed and the cursor positioned before the first row of the result set.

Depending on the type of cursor declaration you choose, opening and closing of the cursor may be done automatically. We will get into the details about different types of cursor declarations later.

Cursors are always closed when the stored procedure terminates. As soon as a cursor is closed, the attached spool space holding the query result set will be released.

Cursors are pointers to the result set records and can be pointing to:

• Before the first position of the result set (in case of a closed cursor)
• After the last status of the result set (in case you iterated over the last row of the result set)
• On any result set row. i.e. the current row

 Scroll ability of Cursors

Not scrollable cursors can only be iterated forward from the first to the last record. As soon as you reach the last record, or moved after the last position of the result set, the cursor content cannot be accessed anymore. Therefore, whenever you need to reuse cursor content, you have to declare it scrollable. Scrollable cursors can be moved back to the first position of the result set. As long as you don't close such a cursor, its content can be reused over and over again.

 Updateable vs. not Updateable Cursors

Only cursors declared as updateable can be used to change data records of the result set (update and delete). Updateable cursors can only be reported in ANSI transaction mode. You cannot create a Stored Procedure with an updateable cursor from your SQL assistant as SQL assistant runs in Teradata transaction mode. You will have to compile your stored procedure from BTEQ in ANSI mode.

 Cursor Declaration

Below you can find the syntax you have to use to declare a cursor in your stored procedure. Before a cursor can be utilized, it has to be stated in the header section of the stored procedure:

DECLARE cursor_name [SCROLL|NOSCROLL] CURSOR [[WITHOUT RETURN|WITH RETURN] [ONLY]] [TO CALLER|TO CLIENT] FOR sql_select_statement [FOR [READONLY|UPDATE]];

Opening the Cursor

After having declared a cursor, it has to be opened:

OPEN cursor_name [USING parameter,…];

• The cursor status is changed to open
• The related SQL statement is executed, and the spool created
• The cursor is located before the first record of the result set

The USING parameter allows you to pass parameters into the executed SQL statement.

Teradata Stored Procedures – Iterating over the Cursor Data Set

FETCH [NEXT|FIRST] FROM cursor_name INTO [local_variable|parameter];

The FETCH FIRST syntax will only work if a scrollable cursor is declared. Otherwise, you will receive a compilation error.

FETCH NEXT moves the cursor one position forward through the data set and assigns the columns to variables or parameters (see the INTO part of the statement).

There are two SQLSTATES which are important when iterating over a cursor:

No rows to fetch:  ‘02000’
Cursor is closed: ‘24501’

You can use the SQLSTATE ‘02000' to determine when you reached the end of the data set.

Teradata Stored Procedures – Closing the Cursor

As soon as you don't need the result set anymore, the cursor should be closed to free up spool space. Here is the syntax:

CLOSE cursor_name;

• The cursor status will be set to “CLOSED”
• The spool space is released

Teradata Stored Procedures – Cursor Example using static SQL

CREATE MULTISET TABLE TABLE_NAME (
PK INTEGER
) PRIMARY INDEX (PK);

INSERT INTO TABLE_NAME   VALUES (1);
INSERT INTO TABLE_NAME  VALUES (2);
INSERT INTO TABLE_NAME VALUES (3);

REPLACE PROCEDURE CURSOR_SAMPLE  ( OUT m INTEGER )
BEGIN
DECLARE c, n INTEGER DEFAULT 0;
DECLARE cursor_name CURSOR FOR SELECT PK FROM TABLE_NAME  ORDER BY 1;

OPEN cursor_name;

label1:

LOOP
FETCH cursor_name INTO c;
IF (SQLSTATE = ‘02000') THEN
  LEAVE label1;
END IF;

SET n = n + c;

END LOOP label1;
CLOSE cursor_name;
SET m = n;

END;

CALL CURSOR_SAMPLE  (x);

Please be aware that without checking for SQLSTATE ‘02000', query processing could get lost in an endless loop !

Teradata Stored Procedures – Cursor Example using dynamic SQL

This example will show you how SQL statements stored in character string variables can be attached to cursors:

REPLACE  PROCEDURE CURSOR_SAMPLE   (IN  m INTEGER)
BEGIN
DECLARE n INTEGER DEFAULT 0;
DECLARE my_sql VARCHAR(1000);
DECLARE cursor_name NO SCROLL CURSOR  FOR  my_statement;

SET my_sql =  ‘SELECT PK FROM TABLE_NAME WHERE PK = ‘ || m || ‘;';

PREPARE my_statement from my_sql;

 OPEN cursor_name;

END;

 CALL CURSOR_SAMPLE  (2);

In the dynamic SQL cursor example above we introduced a new functionality: the PREPARE statement, which builds the cursor SQL statement from a text:

PREPARE statement FROM ‘statement_string' || statement_variable;

The cursor declaration does not contain static SQL in the FOR clause but mentions the prepared statement.

Do you have a cursor declaration like this one below in mind?

DECLARE cursor_name FOR  ‘SELECT PK FROM TABLE_NAME  WHERE PK = ‘ || m || ‘;';

Note that this will not work. The argument of the FOR clause needs to be a static SQL statement or a prepared statement. The example above would pass text as an argument and is invalid.

Apart from mixing variable content directly into the SQL statement text string, you could as well use this kind of syntax:

SET my_sql =  ‘SELECT PK FROM TABLE_NAME  WHERE PK = ? ;';
PREPARE my_statement from my_sql;
OPEN cursor_name USING m;

As question mark in the query indicates the place for a variable or parameter. When opening the cursor, you have to substitute it with an existing variable or parameter. In case you use several such entry points in the SQL statement text, variables are replaced from left to right by the variables/parameters passed in the USING clause.

Prepared statements should be released as soon as not needed anymore:

DEALLOCATE PREPARE statement_name;

The FOR LOOP CURSOR

Last but not least, we will introduce a much easier way of using cursors:

FOR loop_variable AS [cursor_name CURSOR FOR]

cursor_sql DO statement

END FOR;

Cursor declaration, status change, and positioning are handled automatically:

• The cursor is opened when entering the FOR LOOP
• The cursor is iterated over each record in the result set
• The cursor is closed after leaving the FOR LOOP
• No error handling needed for the “END OF DATA” situation

Teradata Stored Procedures – Dynamic Result Sets

Cursors can be used to return complete result sets to the caller, for example to the SQL assistant. To prepare a stored procedure to return a result set must put the DYNAMIC RESULT SETS n statement before the  BEGIN of the first compound statement:

CREATE PROCEDURE MY_PROCEDURE([<PARAMETER> <DATATYPE>][,…])
DYNAMIC RESULT SETS 1  — any number between 1 and 15

BEGIN

DECLARE cursor_name CURSOR WITH RETURN ONLY FOR SELECT PK FROM TABLE_NAME;

OPEN cursor_name; — Do not close or nothing will be returned!

END;

It is important that you do not close the cursor with a CLOSE statement. Otherwise, the result set will not be returned to the client tool. You can return up to 15 open cursors to the client. Cursors will be returned in the same order they have been opened.

Teradata SQL Stored Procedures – usage guide

What are Teradata SQL Stored Procedures?

Maybe the easiest way to describe Teradata Stored Procedures is in this way: Consider them as SQL enhanced with features traditional procedural languages like C, C++, C#, Pascal, etc. are offering.

SQL is a set-orientated language, meaning you have to define your request and a set of data matching your requirements will be returned. On the other hand, traditional programming languages like C++, etc. are procedural languages. The big difference is the way you formulate which data you want to act upon or receive.

While in SQL you just have to define which data you want but not how this data has to be looked up, in a procedural language you have to specify exactly the access paths to your data.

Teradata SQL stored procedures offer traditional SQL enhanced with the typical features most of the procedural languages offer:

• Iterations
• Condition Handling
• Error Handling
• Variables and Parameters

Teradata SQL Stored Procedures are the bridge between the data stored in your RDBMS (relational database management system) and this additional procedural functionality.

You can operate on your data almost in the same way by using SQL within your Stored Procedure like you would do it in a BTEQ script or maybe in your SQL Assistant. You can issue for example a UPDATE statement to trigger a mass update on an individual table (in a SET operation manner).

Additionally Stored Procedures offer you the possibility to iterate record by record over your data sets  i.e. in a procedural way. Iteration is achieved by using CURSOR functionality. In principle, a CURSOR is nothing else than a pointer to a certain record within a Teradata Spool Space.

Technically spoken, whenever you write

When to use Teradata SQL Stored Procedures in your Data Warehouse?

Whenever you need procedural features, Stored Procedures should be considered as an alternative to other options like BTEQ together with Unix shell scripting.

Stored Procedures are encapsulating the programming logic on the Teradata Server which means only the execution command has to be issued by the client, but the code is executed exclusively on the Teradata Server. Heavy caching of SQL code executed within the Stored Procedure will be used.

Please be aware, that only the SQL statements issued directly to the Teradata RDBMS are leveraging the parallelism of the system. Don't iterate over your data record by record (for example doing an update) if the same result can be achieved by executing one direct SQL statement.

Stored Procedures are offering excellent possibilities if used in the correct way. They never should be a substitute for tasks which can be achieved with pure SQL. Especially people with a substantial procedural background often tend to misuse Stored Procedures for overcoming their lack of understanding set-orientated languages like SQL.

I often have heard the argument that “Teradata SQL Stored Procedures are slow.” As I said, used in a proper way I would consider them as the best choice for adding procedural functionality to the RDBMS. Slow Stored Procedures mostly are caused by wrong implementations.

During the next weeks, I have planned to offer a complete series of articles related to Stored Procedures. If there is enough interest, I will offer a live webinar about SQL stored procedures in January 2015. I am currently in the preparation phase, more details will follow later.