What Are Teradata SQL Stored Procedures?
Teradata Stored Procedures are essentially SQL with additional functionality found in traditional procedural languages such as C, C++, C#, and Pascal.
SQL is a set-based language where defining the request leads to Teradata returning a matching data set. In contrast, procedural languages like C++ utilize traditional programming methods. The primary difference lies in the approach to identifying the data to be acted upon or received.
SQL describes the requested data operation. A stored procedure adds control flow around SQL statements, but those statements still use optimizer-selected access paths; a procedural loop does not generally force a physical index or join strategy.
Teradata SQL stored procedures combine conventional SQL with the advanced features found in most procedural languages.
- Iterations
- Condition Handling
- Error Handling
- Variables and Parameters
Teradata SQL Stored Procedures connect the data stored in your RDBMS with extra procedural capabilities.
Operating on your data with SQL within a Stored Procedure is almost identical to utilizing a BTEQ script or SQL Assistant. For instance, you can execute an UPDATE statement to initiate a mass update on a single table (using SET).
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free bookStored Procedures enable procedural iteration over datasets using the CURSOR functionality, which creates a pointer to a specific record in Teradata Spool Space.
When To Use Teradata SQL Stored Procedures In Your Data Warehouse?
Consider using Stored Procedures as a viable alternative to BTEQ and Unix shell scripting when requiring procedural capabilities.
Stored Procedures encapsulate programming logic on the Teradata Server, making it unnecessary for clients to execute anything but the command to run them. This code runs solely on the Teradata Server and benefits from substantial caching of executed SQL code.
Note that parallel processing is only utilized for SQL statements directly executed on the Teradata RDBMS. Avoid iterating through individual data records, such as during updates, when a single direct SQL statement can produce the same outcome.
Stored Procedures are highly beneficial when utilized appropriately, but they must not be used as replacements for tasks that can be accomplished with pure SQL. Those with a significant background in procedural programming may mistakenly rely on Stored Procedures to compensate for their lack of comprehension of set-oriented languages, such as SQL.
Stored procedures are useful for server-side control flow. Their performance depends on the statements, data, plans, transactions and implementation; row-by-row processing is one common source of overhead.
Best practices for Teradata stored procedures
Most of the complaints about stored procedure performance come back to the same handful of habits.
- Keep the work set-based. Teradata parallelises the SQL statements a procedure issues, not the procedural logic around them. A loop that processes one row at a time gives all of that away, which is the single most common reason a procedure is called slow.
- Open a cursor only when the logic genuinely cannot be expressed as a set operation — and know what it costs when you do: the cursor mistakes that quietly serialise a procedure.
- Declare your error handling. A procedure without a handler fails in whatever way the calling session happens to be configured for. See handling errors and exceptions in a procedure.
- Prefer static SQL where the statement is known in advance. Dynamic SQL is occasionally the only option, but it gives up compile-time checking and is harder to read months later: dynamic against static SQL, and what each costs.
- Watch variable scope in nested blocks. A variable declared in an inner
BEGIN...ENDhides the outer one of the same name, and the resulting bug is silent: how variable scope works inside nested compound statements.
Set-based processing is often the best starting point. When procedural iteration is necessary, measure both per-statement and orchestration costs; neither good structure nor statement tuning guarantees a particular runtime.
Deciding what to change in your data platform?
Compare optimisation, modernisation and migration against your workload and business requirements.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free book
As far as I know just from BTEQ, not from the load utilities.
Hi, what are the possible ways of executing/calling a stored procedure? Can we call them from all Teradata utilities like BTEQ, Multiload, Fastload, and TPT?
I wish, I had this article to show one of my clients about a year ago. I suggested cursor-based processing in the stored procedure, where each record needs to be processed for several conditions. I received a strong opposition on the ground that set-based processing is more efficient than cursor-based processing.
Instead of one cursor-based process, they had to code 10plus set-based processes to get a smaller subset in each process. I wanted to demonstrate the efficiency of cursor-based processing on a small sample with a pre-compiled stored procedure, but I was not allowed any time for testing.
I wish, Teradata educate their clients more, so they could make the appropriate choices.