I already post about that kind of subject in the past and we achieved to succeed it in Java/JDBC mode.
We are executing macro statement in a multithreaded batch mode. We just cannot achieve to take advantage of statement cache (cacheFlag=T).
How can we take advantage of statement cache with .NET Teradata provider ?
I'm believing that s not possible to achieve trought .NET Teradata drivers.
Thanks you by advance for your help.
Below some additionals infos :
We re using Teradata.Client.Provider 15.00
Sample in c#
// Dedicated open connection to the command
this.command = this.controller.NewConnection().CreateCommand();
this.command.CommandType = System.Data.CommandType.Text;
// setup the macro statement
this.command.CommandText = this.configuredTeradataJob.QueryText;
// Preparing Command Parameter Type;
foreach (TdParameter p in this.configuredTeradataJob.Parameters)
TdParameter parameter = command.CreateParameter();
parameter.ParameterName = p.ParameterName;
parameter.DbType = p.DbType;
parameter.Direction = System.Data.ParameterDirection.Input;
// setup parameters value
int indexParameter = 0;
foreach (string arg in args)
this.command.Parameters[indexParameter++].Value = arg;
Extract from ".NET Teradata Provider" documentation
Prepare method sends the SQL statement to Teradata Database which parses the statement and returns column information.
When CommandType is set to CommandType.StoredProcedure,
TdCommand generates a CALL statement and utilizes the parameter collection to create the stored procedure arguments.
If you call one of the Execute methods after calling Prepare, the Data Provider will utilize the Statement Cache. However, if you call one of the Execute methods without calling Prepare, the .Net Data Provider will not utilize the Statement Cache.
If you change either the CommandText or CommandType property, the TdCommand is no longer in a Prepare state.
We strongly recommend that you Prepare parameterized queries that are executed frequently. That is first set the CommandText property, CommandType property and the Parameter collections. Next, prepare the command once and set parameter values and call execute method repeatedly.
The code is running fine using guidelines from doc.
Data from DBC.dbqlogtbl
I highly recommend that you set the following TdParameter properties
They must match the DDL; for example
1- if Parameter #1 is declared as VarChar(200) then you must set the TdType to VarChar and Size to 200.
2- if Parameter #2 is declared as Decimal(38, 2) the you must set the TdType to Decimal and Precision to 28 and Scale to 2.
otherwise the Parameter Metadata (Type, Size, Precision and Scale) sent to the Teradata Database will be calculated from the TdParameter.Value. Therefore the Parameter-Metadata for each ExecuteNonQuery will vary and potentially affect the Statement-cache utilization.