Improving Performance with Phase Reductions | Teradata Vantage - 17.10 - Improving Performance with Phase Reductions - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1147-171K
Language
English (United States)

The FNC_GetPhaseEx options give you more control of table phase transitions when developing variable mode table functions. You can use them to reduce the number of phase transitions required during execution of a table function, thus reducing the number of UDF invocations and improving table function performance.

The following table compares the number of phase transitions required for each row if you do not use the FNC_GetPhaseEx options and if you do use the various options. The phases are P (TBL_PRE_INIT), I (TBL_INIT), B (TBL_BUILD), B EOF (TBL_BUILD signalling EOF, not TBL_BUILD_EOF), F (TBL_FINI), and E (TBL_END). X is the scale factor of input to output rows.

Processing Mode Required phases if the FNC_GetPhaseEx options are not used Required phases if the specified FNC_GetPhaseEx options are used
1:1

(one row in : one row out)

I, B, B EOF, F B, if the TBL_NEWROW option is set.
1:M

(one row in : many rows out)

I, B*X, B EOF, F B * X, if the TBL_NEWROWEOF option is set.
M:1

(many rows in : one row out)

I, B EOF, F B * X, if the TBL_NEWROW | TBL_LASTROW options are set.

For example:

In a 1:1 processing mode, use the TBL_NEWROW option to get a new row on every TBL_BUILD call.

FNC_Mode mode = FNC_GetPhaseEx(&thePhase, TBL_NEWROW);

In this case, the function passes through the following phases.


TBL_NEWROW option gets new row on TBL_BUILD call

In a 1:M processing mode, use the TBL_NEWROWEOF option to get a new row when EOF is signaled.

FNC_Mode mode = FNC_GetPhaseEx(&thePhase, TBL_NEWROWEOF);

In this case, the function passes through the following phases.


TBL_NEWROWEOF option gets new row when EOF signaled

In a M:1 processing mode, you can use:

FNC_Mode mode = FNC_GetPhaseEx(&thePhase, TBL_LASTROW | TBL_NEWROW);

In a combined M:1 and 1:M processing mode, you can use the following that does not pass a new row until EOF:

FNC_Mode mode = FNC_GetPhaseEx(&thePhase, TBL_LASTROW | TBL_NEWROWEOF);

In this case, the function passes through the following phases.


TBL_NEWROWEOF option passes new row at EOF

For more information, see FNC_GetPhaseEx.