MySQL Bugs: #55609: DataAdapter.Update() slowdown

Bug #55609	DataAdapter.Update() slowdown
Submitted:	28 Jul 2010 16:17	Modified:	29 Jul 2010 14:54
Reporter:	Holger Mueller	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.3.3	OS:	Any
Assigned to:	Vladislav Vaintroub	CPU Architecture:	Any
Tags:	MySqlDataAdapter

Description:
Today I updated Connector NET from 6.3.2 to 6.3.3 and noticed a huge performance impact while updating large amounts of data from a DataTable back to the DB.
After some debugging I figured out that it happens at the moment when MySqlDataAdapter.Update(DataTable) is called.
I did some performance benchmarking to compare 6.3.2 to 6.3.3 with nothing else changed. Whereas 6.3.2 takes about 30 sec to complete, 6.3.3 takes more than 2 minutes!!! (same machine, same code, same data)
I then compared source code of both versions and I guess I could track it down to the newly introduced function
'protected override int Update(DataRow[] dataRows, DataTableMapping tableMapping)'
in
'dataadapter.cs'

How to repeat:
1. create an empty table with several columns.
2. fill a DataTable with a DataDapter.Fill() with a simple SELECT * FROM table WHERE false
   (the false at the end just to receive no data, just description)
3. do add a lot of rows to the DataTable in memory (about 20.000 should be enougt to see the effect)
4. do a MySqlDataAdapter.Update(DataTable)  (with an attached MySqlCommandBuilder)
   to write the data back.
5. go an fetch some coffee while waiting...

Suggested fix:
Don't know. For me I must switch back to 6.3.2. :(

Karl, there is a unit test that exactly resembles your description.
MySql.Data\Tests\Source\DataAdapterTests.cs
the test name is TestBatchingInserts.

I set it to run with 20000 rows. On my machine, it completes in 2.65 sec given batching size 10, or in 5.19 seconds given batching size of 1 (no batching).

Preparing coffee on the other hand takes 2.5 minutes, which is about 60 times longer.

So I cannot reproduce your exact scenario.

For the reference, here is the code I used (thought to be a part of our unit tests, slightly modified for the said 20000 records)

        [Test]
        public void TestBatchingInserts()
        {
            execSQL("CREATE TABLE Test (id INT, name VARCHAR(20), PRIMARY KEY(id))");

            MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
            MySqlCommand ins = new MySqlCommand("INSERT INTO test (id, name) VALUES (?p1, ?p2)", conn);
            da.InsertCommand = ins;
            ins.UpdatedRowSource = UpdateRowSource.None;
            ins.Parameters.Add("?p1", MySqlDbType.Int32).SourceColumn = "id";
            ins.Parameters.Add("?p2", MySqlDbType.VarChar, 20).SourceColumn = "name";

            DataTable dt = new DataTable();
            da.Fill(dt);

            int N = 20000;

            for (int i = 1; i <= N ; i++)
            {
                DataRow row = dt.NewRow();
                row["id"] = i;
                row["name"] = "name " + i;
                dt.Rows.Add(row);
            }

            da.UpdateBatchSize = 1;
            da.Update(dt);

            dt.Rows.Clear();
            da.Fill(dt);
            Assert.AreEqual(N, dt.Rows.Count);
            for (int i = 0; i < N; i++)
            {
                Assert.AreEqual(i+1, dt.Rows[i]["id"]);
                Assert.AreEqual("name " + (i+1), dt.Rows[i]["name"]);
            }
        }

Since you have already spent some time to write your benchmarks, perhaps you can share them, too.  It is always a good idea to add add some code to the report like this.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/114566

829 Vladislav Vaintroub	2010-07-28
      Fix DataAdapter.Update() slowdown due to many 
      superfluous DataTable.AcceptChanges() calls
      (Bug #55609)

Karl, ignore previous comment. 
Happened to looked into wrong repository. Yes, there is/was  a regression in case of huge updates, and there is a lot of table.AcceptChanges() that can be spared. Thanks a lot for noticing, this fixed and will get into in 6.3.4

PS. Still, attaching working code to reproduce bug reports is something highly desired and helpful for us. If possible, don't miss this opportunity next time.

Thank you for the quick response!
Next time I report a bug I promise to submit demo-code with it. :-)
So I'll wait for 6.3.4. Any suggestion when it might be available?
Best regards,
Karl

Karl, re 6.3.4 I cannot give specific dates (in fact as a matter of company  policy we cannot discuss dates). Besides I do not know the dates myself.

The plan is to clean up last remaining glitches (yours was timely there:)) and thats it. It will not take long.

Karl, I suggest you build c/NET from source repository yourself after Wlad pushes the patch. This will be the quickest solution.

@Tonci, pushed already

An entry has been added to the 6.3.4 changelog:

Calling MySqlDataAdapter.Update(DataTable) resulted in an unacceptable performance hit when updating large amounts of data.