| Bug #59616 | Only INSERTs are batched | ||
|---|---|---|---|
| Submitted: | 19 Jan 2011 17:34 | Modified: | 9 Mar 2011 18:20 |
| Reporter: | Yvan Rodrigues | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / NET | Severity: | S2 (Serious) |
| Version: | 6.3.6 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | batch, batching, insert, UPDATE | ||
[25 Feb 2011 13:25]
Bogdan Degtyariov
Yvan,
I do not quite understand how it is possible to update all rows at once if each row is associated with its primary key and its unique data. Each data change has a unique query, which corresponds to a particular row:
UPDATE t_bug59616 SET txt='new value' WHERE ID=....
Or perhaps you expected all these rows to be concatenated like "UPDATE ...; UPDATE ...; UPDATE ..."?
C# test case:
-------------------------------------------------------------------------------
void run_bug_59616()
{
MySqlConnection con = new MySqlConnection();
try
{
con.ConnectionString = "server=localhost;database=test;" +
"user id=****;Password=*****;Allow Batch=true";
con.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = con;
cmd.CommandText = "DROP TABLE IF EXISTS t_bug59616";
cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE TABLE t_bug59616 " +
"(id int auto_increment primary key, txt varchar(32))";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO t_bug59616(id, txt)" +
" VALUES (1, 'something 1')";
for(int i=2; i<100; i++)
cmd.CommandText+= ",(" + i.ToString() + ", 'something "
+ i.ToString() + "')";
cmd.ExecuteNonQuery();
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM t_bug59616", con);
DataTable dataTable = new DataTable();
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
da.UpdateCommand = cb.GetUpdateCommand();
da.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
da.Fill(dataTable);
for (int j = 0; j < dataTable.Rows.Count; j++)
{
dataTable.Rows[j]["txt"] = "AAA BBB " + j.ToString();
}
da.Update(dataTable);
cmd.CommandText = "DROP TABLE IF EXISTS t_bug59616";
cmd.ExecuteNonQuery();
Console.WriteLine("Done!");
}
catch (Exception ex)
{
string error = ex.Message.ToString();
error += ex.StackTrace;
Console.WriteLine("=============================================\r\n" +
"Error!\r\n" + error + "\r\n" +
"=============================================\r\n");
}
finally
{
con.Close();
}
}
[25 Feb 2011 14:28]
Yvan Rodrigues
Hello Bogdan, Yes, my expected behaviour would be concatenation of the UPDATE queries. The result is exponentially faster than issuing separate queries, regardless of connection pooling.
[28 Feb 2011 11:01]
Bogdan Degtyariov
Yvan, Thanks for your comments. Proposed solution is not working, but I will mark the report as verified.
[28 Feb 2011 11:32]
Bogdan Degtyariov
Yvan, Sorry, the solution is working. Thanks for helping us to fix this issue. I will ask developers to review the patch.
[9 Mar 2011 18:20]
Reggie Burnett
Fixed in 6.0.8, 6.1.6, 6.2.5, 6.3.7+
[11 Mar 2011 14:32]
Tony Bedford
An entry has been added to the 6.0.8, 6.1.6, 6.2.5, and 6.3.7 changelogs: All queries other than INSERT were executed individually instead of as a batch even though batching was enabled for the connection.
Description: All queries other than INSERTs are executed individually instead of as a batch when batching is enabled for a connection. How to repeat: - Populate a DataTable with 100 rows of modified data (for example use a MySqlDataAdapter to fill a table with a SELECT, then modify the rows) - Open a MySqlConnection, enabling batching - Update the table using a MySqlDataAdapter - 100 queries will be issued to the server Suggested fix: --- C:/Users/YVAN~1.HAM/AppData/Local/Temp/command.c-revBASE.svn001.tmp.cs Wed Jan 19 12:33:34 2011 +++ C:/Users/yvan.HAM1/Documents/Visual Studio 2008/Projects/MisMabel Trunk/MySql/Source/command.cs Wed Jan 19 12:33:34 2011 @@ -872,6 +872,8 @@ token = tokenizer.NextToken(); } } + // Otherwise use the command verbatim + else batchableCommandText = CommandText; } return batchableCommandText;