.NET Executing macro and statement cache

Connectivity

.NET Executing macro and statement cache

Hello,

I already post about that kind of subject in the past and we achieved to succeed it in Java/JDBC mode.

Our issue

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 :

Code sample

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;
this.command.Parameters.Add(parameter);
}
this.command.Prepare();

while(batch) {
// setup parameters value
int indexParameter = 0;
foreach (string arg in args)
{
this.command.Parameters[indexParameter++].Value = arg;
}
// execute
this.command.ExecuteNonQuery();
}


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

SessionID StartTime QueryText CacheFlag ExtraField22

1 919577 03/11/2014 15:59:23.61 HELP SESSION Both 

2 919577 03/11/2014 15:59:23.82 execute FRDM_PAF.M_PAF_SALES_DAY_ALLOCATION_ALL_DPT(?,?,?,?,?,?,?,?,?); PrepS

3 919577 03/11/2014 15:59:24.92 execute FRDM_PAF.M_PAF_SALES_DAY_ALLOCATION_ALL_DPT(?,?,?,?,?,?,?,?,?); S Exec 

4 919577 03/11/2014 15:59:26.42 execute FRDM_PAF.M_PAF_SALES_DAY_ALLOCATION_ALL_DPT(?,?,?,?,?,?,?,?,?); G Exec 

5 919577 03/11/2014 15:59:35.81 execute FRDM_PAF.M_PAF_SALES_DAY_ALLOCATION_ALL_DPT(?,?,?,?,?,?,?,?,?); A Exec 

6 919577 03/11/2014 15:59:37.26 execute FRDM_PAF.M_PAF_SALES_DAY_ALLOCATION_ALL_DPT(?,?,?,?,?,?,?,?,?); A Exec 

7 919577 03/11/2014 15:59:38.92 execute FRDM_PAF.M_PAF_SALES_DAY_ALLOCATION_ALL_DPT(?,?,?,?,?,?,?,?,?); A Exec 

8 919577 03/11/2014 15:59:41.33 execute FRDM_PAF.M_PAF_SALES_DAY_ALLOCATION_ALL_DPT(?,?,?,?,?,?,?,?,?); A Exec 

9 919577 03/11/2014 15:59:42.79 execute FRDM_PAF.M_PAF_SALES_DAY_ALLOCATION_ALL_DPT(?,?,?,?,?,?,?,?,?); A Exec 

10 919577 03/11/2014 15:59:44.26 execute FRDM_PAF.M_PAF_SALES_DAY_ALLOCATION_ALL_DPT(?,?,?,?,?,?,?,?,?); A Exec 

11 919577 03/11/2014 15:59:46.25 execute FRDM_PAF.M_PAF_SALES_DAY_ALLOCATION_ALL_DPT(?,?,?,?,?,?,?,?,?); A Exec 

12 919577 03/11/2014 15:59:48.02 execute FRDM_PAF.M_PAF_SALES_DAY_ALLOCATION_ALL_DPT(?,?,?,?,?,?,?,?,?); A Exec 

13 919577 03/11/2014 15:59:49.37 execute FRDM_PAF.M_PAF_SALES_DAY_ALLOCATION_ALL_DPT(?,?,?,?,?,?,?,?,?); A Exec 

14 919577 03/11/2014 15:59:50.87 execute FRDM_PAF.M_PAF_SALES_DAY_ALLOCATION_ALL_DPT(?,?,?,?,?,?,?,?,?); A Exec 

15 919577 03/11/2014 15:59:52.30 execute FRDM_PAF.M_PAF_SALES_DAY_ALLOCATION_ALL_DPT(?,?,?,?,?,?,?,?,?); A Exec 

16 919577 03/11/2014 15:59:53.66 execute FRDM_PAF.M_PAF_SALES_DAY_ALLOCATION_ALL_DPT(?,?,?,?,?,?,?,?,?); A Exec 

17 919577 03/11/2014 15:59:55.02 execute FRDM_PAF.M_PAF_SALES_DAY_ALLOCATION_ALL_DPT(?,?,?,?,?,?,?,?,?); A Exec 

18 919577 03/11/2014 15:59:56.48 execute FRDM_PAF.M_PAF_SALES_DAY_ALLOCATION_ALL_DPT(?,?,?,?,?,?,?,?,?); A Exec 

2 REPLIES
Teradata Employee

Re: .NET Executing macro and statement cache

I highly recommend that you set the following TdParameter properties

  TdType

  Size

  Precision

  Scale

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.  

Re: .NET Executing macro and statement cache

Hello,

Thanks for your reply.  

It works a lot better when setting all parameters properties.