Stored Procedures and Tactical Queries | Teradata Vantage - Stored Procedures and Tactical Queries - Advanced SQL Engine - Teradata Database

Using Stored Procedures to Execute Complex Tactical Updates

Complex updates in a tactical query are often more easily processed if they are disassembled and then integrated within a stored procedure. The computational completeness offered by stored procedure control statements permits you to execute SQL statements iteratively and conditionally, which not only makes what they are doing more explicit than the nested subqueries required to write many complex updates, but can also make them easier to process.

For example, to execute the following complex update, a stored procedure would execute two single-AMP statements, each of which applies only single row hash locks.

UPDATE orders
SET o_orderpriority = 5
WHERE o_orderkey = 39256
AND EXISTS
  (SELECT * FROM lineitem
   WHERE l_orderkey = o_orderkey);

The following two EXPLAIN reports represent the disassembled SQL statements that could be written inside the stored procedure to replace the previously described complex update.

EXPLAIN
SELECT *
FROM lineitem
WHERE l_orderkey = 39256;
Explanation
------------------------------------------------------------------------
  1) First, we do a single-AMP RETRIEVE step from CAB.lineitem by
     way of the primary index "CAB.lineitem.L_ORDERKEY = 39256"
     with no residual conditions into Spool 1, which is built locally
     on that AMP.  The input table will not be cached in memory, but it
     is eligible for synchronized scanning.  The size of Spool 1 is
     estimated with high confidence to be 4 rows.  The estimated time
     for this step is 0.15 seconds.
  -> The contents of Spool 1 are sent back to the user as the result of
     statement 1.  The total estimated time is 0.15 seconds.

Using the appropriate conditional logic, the procedure would then execute the second statement only if at least one lineitem row is returned from the first request.

EXPLAIN
UPDATE orders
SET o_orderpriority = 5
WHERE o_orderkey = 39256
Explanation
------------------------------------------------------------------------
  1) First, we do a single-AMP UPDATE from CAB.orders by way of
     the unique primary index "CAB.orders.O_ORDERKEY = 39256"
     with no residual conditions.

You would need to code these two statements within an explicit transaction, using BEGIN TRANSACTION and END TRANSACTION statements to specify the transaction boundaries.

Because stored procedures cannot return multirow result sets, macros are usually a better approach to use when you need to return more than one row from a table.

Using Stored Procedures to Eliminate Unnecessary Database Work

Suppose you have an application that uses data from only one of two possible tables, but you cannot know which of the two tables is required until you attempt to access the first. A stored procedure can eliminate processing that would otherwise be necessary to solve the problem.

To illustrate, consider a database with two tables that contain owned_parts and supplied_parts parts information. Assume you have a business rule that says that you return owned_parts data if it exists, and only return supplied_parts data if owned_parts data does not have the specified prime key value. Also assume that you have a tactical query that probes for parts data by specifying a part key.

A macro would have to access both tables for each request, then pass the data to the application to determine which to use when two rows are returned, as illustrated in the following graphic.

This extra work is unnecessary if you write a stored procedure to solve the problem. You can code logic in a stored procedure to determine if a row has been found in the owned_parts table, and, if so, to then return to the application without attempting to access the supplied_parts table. The following graphic provides a high level picture of the processing involved.

Security and Auditing

Considering the same parts example from Using Stored Procedures to Eliminate Unnecessary Database Work, suppose that only select users are permitted to access the supplied_parts table. With a modification to the previous stored procedure, you can code logic to check the privileges for the user who submitted the procedure against a security table. The procedure then checks permissions before access to supplied_parts is allowed without the user even being aware that his access had been monitored, as illustrated by the following graphic:

A similar approach could be taken to validate that certain data in the supplied_parts table can only be viewed by certain users, but not by others.

Macros or Stored Procedures for Tactical Queries

Macros were once a better choice than stored procedures for simple requests, multistatement requests (statements executed in parallel), and statements returning multiple rows because performance was almost always better. Stored procedures now support multistatement requests and result sets, and with their conditional logic, are a better choice than macros for running tactical queries.

Simple Requests

Stored procedures may perform better than macros for simple requests. You can use either macros or stored procedures to run simple requests.

Multistatement Requests

Both macros and stored procedures support multistatement requests. Multistatement request performance for stored procedures is the same, if not better than, macro performance.

Statements Returning Multiple Rows

Stored procedures now support result sets, which means that a stored procedure can now return multiple rows. Macros have no advantage over stored procedures in returning multiple rows.

Differences Between Macros and Stored Procedures

The following table summarizes the differences between macros and stored procedures.