Improving Performance with Phase Reductions | Teradata Vantage - Improving Performance with Phase Reductions - Analytics Database - Teradata Vantage

SQL External Routine Programming

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
iiv1628111441820.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1147
lifecycle
latest
Product Category
Teradata Vantageā„¢

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.



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.



For more information, see FNC_GetPhaseEx.