Increase performance on OleDB insert into statemen

Question

I have written to append functions that insert data from custom c# list into MSAccess.

The first simply sets up a new connection for each individual recordset:

        public static void appenddatatotable(string connectionstring, string tablename, string[] values)
    {

            var myconn = new OleDbConnection(connectionstring);


            var cmd = new OleDbCommand();
            cmd.CommandText = "INSERT INTO " + tablename + " ([RunDate],[ReportingGroup], [Tariff], [Year]) VALUES(@RunDate, @ReportingGroup, @Tariff, @Year)";

            cmd.Parameters.AddRange(new[] { new OleDbParameter("@RunDate", values[0]), new OleDbParameter("@ReportingGroup", values[1]), new OleDbParameter("@Tariff", values[2]), new OleDbParameter("@Year", values[3])});
            cmd.Connection = myconn;
            myconn.Open();
            cmd.ExecuteNonQuery();
            myconn.Close();

    }

I then simply loop over my list of values and call this function on each iteration. This works fine but is slow.

In the second function I tried to include the loop in the function and work with BeginTransction and Committransaction:

        public static void appenddatatotable2(string connectionstring, string tablename, string datstr, List<PowRes> values)
    {

        var myconn = new OleDbConnection(connectionstring);
        int icounter = 0;

        var cmd = new OleDbCommand();
        OleDbTransaction trans = null;

        cmd.Connection = myconn;
        myconn.Open();
        foreach (var item in values)
        {
            if (icounter == 0)
            {
                trans = cmd.Connection.BeginTransaction();
                cmd.Transaction = trans;
            }

            cmd.CommandText = "INSERT INTO " + tablename + " ([RunDate],[ReportingGroup], [Tariff], [Year]) VALUES(@RunDate, @ReportingGroup, @Tariff, @Year)";
            if (string.IsNullOrEmpty(item.yr))
                item.yr = "";

            cmd.Parameters.AddRange(new[] { new OleDbParameter("@RunDate", datstr), new OleDbParameter("@ReportingGroup", item.RG), new OleDbParameter("@Tariff", item.tar), new OleDbParameter("@Year", item.yr)});
            cmd.ExecuteNonQuery();
            icounter++;
            if (icounter >= 500)
            {
                trans.Commit();
                icounter = 0;
            }
        }
        if (icounter > 0)
        {
            trans.Commit();
        }


        myconn.Close();

    }

This also works fine but is EVEN slower.

Is my code wrong? How could I speed up the multiple inserts?

Thanks!

Answer 1

this should be significantly faster than all of your exiting versions

public static void appenddatatotable2(string connectionstring, string tablename, string datstr, List<PowRes> values)
        {
            string commandText = "INSERT INTO " + tablename + " ([RunDate],[ReportingGroup], [Tariff], [Year]) VALUES(@RunDate, @ReportingGroup, @Tariff, @Year)";
            using (var myconn = new OleDbConnection(connectionstring))
            {
                myconn.Open();
                using (var cmd = new OleDbCommand())
                {
                    foreach (var item in values)
                    {
                        cmd.CommandText = commandText;
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddRange(new[] { new OleDbParameter("@RunDate", datstr), new OleDbParameter("@ReportingGroup", item.RG), new OleDbParameter("@Tariff", item.tar), new OleDbParameter("@Year", item.yr) });
                        cmd.Connection = myconn;
                        cmd.Prepare(); 
                        cmd.ExecuteNonQuery();
                    }
                }   
            }
        }

Answer 2

did not test, just my guess for your second function: you add too many parameters to the same command over the loop - cmd.Parameters were never cleared before each usage..

normally committing large set of commands within one connection is much faster than doing them one by one at single connection.

another way to speed up your inserts is to dump all your insert statements into a long text, separated with semicolon, and then fire a commit in one go (i am not sure whether msAccess supports it or not)

EDIT:

to combine the update command into one text:

var updates = values.Select(x => string.Format("INSERT INTO myTable ([RunDate],[ReportingGroup], [Tariff], [Year]) VALUES({0}, {1}, {2}, {3})",
                datstr, x.RG, x.tar, x.yr))
                .Aggregate((m, n) => m + ";" + n);
cmd.CommandText = update;

Though this could have sql injection issues.