ArrayBinding : Poor Performance compared to ODP

[MisterBee]

I tried to use the ArrayBinding for insert 1000 data in oracle database and i encoutered performance problem compared to Oracle Data Provider (ODP)

My result is :
Dot Connect For Oracle : 27 sec
ODP : 97 ms

My code (Direct Mode is set to false) :

Code: Select all

public static void TestArrayBind()
{
    // generate test values
    Guid idTest = Guid.NewGuid();
    DateTime dateTest = new DateTime(2008, 01, 01, 0, 0, 0, DateTimeKind.Utc);
    decimal valueTest = 0m;
    object[] idValues = new object[batchSize];
    object[] dateValues = new object[batchSize];
    object[] valuesValues = new object[batchSize];

    for (int i = 0; i < idValues.Length; i++)
    {
        idValues[i] = idTest.ToByteArray();
        dateValues[i] = dateTest;
        valuesValues[i] = valueTest;

        dateTest = dateTest.AddDays(1);
        valueTest++;
    }

    // request
    using (OracleConnection dbConnection = new OracleConnection(GetConnectionString()))
    {
        dbConnection.Open();
        using (OracleTransaction dbTransaction = dbConnection.BeginTransaction(IsolationLevel.ReadCommitted))
        {
            using (OracleCommand dbCommand = dbConnection.CreateCommand())
            {
                dbCommand.Transaction = dbTransaction;
                dbCommand.CommandTimeout = 0;
                dbCommand.CommandText = "insert into TESTARRAYBIND (ID, DATA_DATETIME, DATA_VALUE) values (:prmID, :prmDATA_DATETIME, :prmDATA_VALUE)";
                
                AddParameter(dbCommand, "prmID", DbType.Binary, idValues);
                AddParameter(dbCommand, "prmDATA_DATETIME", DbType.DateTime, dateValues);
                AddParameter(dbCommand, "prmDATA_VALUE", DbType.Decimal, valuesValues);

                dbCommand.ExecuteArray(valuesValues.Length);

                dbCommand.Parameters.Clear();
            }
            dbTransaction.Commit();
        }

        dbConnection.Close();
    }
}

private static void AddParameter(OracleCommand dbCommand, string name, DbType dbType, object[] idValues)
{
    OracleParameter param = dbCommand.CreateParameter();
    param.ParameterName = name;
    param.DbType = dbType;
    param.Direction = ParameterDirection.Input;
    param.Value = idValues;
    dbCommand.Parameters.Add(param);
}

I am using the last beta of dotConnect for Oracle in Trial Mode.
Why such a difference in time ?

Sincerely

[Shalex]

Please replace your line of code

Code: Select all

AddParameter(dbCommand, "prmID", DbType.Binary, idValues);

with the following piece of code

Code: Select all

OracleParameter param1 = new OracleParameter("prmID", idValues);
param1.OracleDbType = OracleDbType.Raw;
dbCommand.Parameters.Add(param1);

DbType.Binary corresponds to OracleDbType.Blob in dotConnect for Oracle.