Execute Array too slow since updating to net 6 from net core 3.1

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
tecnic
Posts: 1
Joined: Wed 03 Aug 2022 06:39

Execute Array too slow since updating to net 6 from net core 3.1

Post by tecnic » Wed 03 Aug 2022 07:01

I had version 9.16.1434 with net core 3.1 and I did massive updates using the following code:

Code: Select all

foreach (KeyValuePair<string, BulkTableHelper> taula in taulesUpdate)
{
    using (OracleCommand command = ((OracleConnection)conn).CreateCommand())
    {
        int j = taula.Value.Parameters.FirstOrDefault().Value.Count;
        int take = 100;
        int offset = 0;
        if (take > j)
            take = j;
        while (j > offset)
        {
            List<OracleParameter> parameters = new List<OracleParameter>();
            int count = 0;

            var snakeCaseStrategy = new SnakeCaseNamingStrategy();
            string values = "";
            string where = "";

            foreach (KeyValuePair<string, List<object?>> parameter in taula.Value.Parameters)
            {
                OracleParameter par = new OracleParameter();
                var paramName = snakeCaseStrategy.GetPropertyName(parameter.Key, false);

                par.ParameterName = paramName;
                var paramValues = parameter.Value.ToArray().Skip(offset).Take(take).ToArray();
                par.Value = paramValues;

                par.OracleDbType = OracleHelpers.GetOracleDbTypeFromType(taula.Value.ParametersType[parameter.Key]);
                command.Parameters.Add(par);
                count = parameter.Value.Count;

                if (taula.Value.PrimaryKeys.Contains(parameter.Key))
                {
                    if (!string.IsNullOrEmpty(where))
                        where += " AND ";
                    where += $" {paramName} = :{paramName} ";
                }
                else
                {
                    values += $"{paramName} = :{paramName}, ";
                }
            }

            values = values.Remove(values.LastIndexOf(","), 1);

            string commandText = $"UPDATE {taula.Key} SET {values} WHERE {where}";

            command.CommandText = commandText;

            int result = command.ExecuteArray(take);
            res += result;
            if (offset + take > j)
            {
                take = j - offset;
                offset = j;
            }
        }


    }
}
This updated 65k registers in about 10 seconds. Then I updated the project to net 6 and tried again but now it takes 37 seconds just to update 100 registers. I'm testing with the same values and the same table so is there something I'm missing with net 6?
Also I tried to update the package to version 10.0.0 but is the same.

DmitryGm
Devart Team
Posts: 152
Joined: Fri 11 Dec 2020 10:27

Re: Execute Array too slow since updating to net 6 from net core 3.1

Post by DmitryGm » Tue 04 Oct 2022 08:07

To reproduce the issue please show us the complete test project and DDL for the tables you were updating.

Post Reply