Cached Plans
Caching of repeatable requests in the Request-to-Step Cache reduces performance time because parsing and optimizing do not need to be done when cached requests are repeated. For subsecond queries, cached plans significantly reduce the query time and enhance throughput. The following figure is a high‑level flow chart for the request‑to‑steps cache that resides in each parsing engine on a Teradata system.
For macros to be considered for caching, Teradata Database must know that the SQL text they contain is repeatable. All of the following attributes must be identical each time the request is sent to the Teradata platform:
Parameter variables in the macro support repeatability. These parameters must be specified in both the SQL code itself and in the USING request modifier that precedes the EXEC statement that invokes the macro.
The following picture illustrates the conditions required for caching the SQL statements for a macro. In this example, the request is made using BTEQ from a mainframe client across a block multiplexor channel. Note that the SQL code is in a request parcel, while the data values are transmitted in a data parcel. Both a request and a data parcel must be transmitted for the SQL to be cached immediately.
The data values are passed from the application to the macro code where they are then processed. Their format depends on the programming conventions for the application. For example, in BTEQ you must use the .IMPORT FILE = command when you perform the macro, and the import file specified in the command contains records with the data values to be passed to the macro. The values in the file are positionally associated, from left to right, with the parameters in the USING request modifier.