Just finished checking in initial support for batching in 5.2. Even though MySQL doesn't support batching in the protocol, the connector sends updates and deletes in multi-statement fashion (read: sent at the same time but separated by a semi-colon). For inserts the connector tries to be a little smarter. If the insert command is simple and uses a simple VALUES(...) block, then it will construct a multi-value INSERT statement of the form INSERT INTO <table> (col, col, ..) VALUES (...), (...), (...). This is significantly faster for the server than sending the inserts in multi-statement mode.
For those not familiar with .NET, we have an object called a DataTable. This object contains columns and rows and is what the user uses to insert, update, and delete data on the client side. When the user wants to update the data on the server, this table (or a series of these tables) is passed to the MySqlDataAdapter.Update method. A data adapter object holds references to at most 4 different commands, one each for select, update, insert, and delete.
For each changed row in a data table, the data adapter will choose the appropriate command and ask that command to execute giving it the values in that data row as parameters. A user can set the data adapter's UpdateBatchSize to tell the system how many commands should be sent at one time in a "batch". Setting it to 0 tells it to send as many as the server will allow.
Batching saves tons of time when you have many rows of the same operation in a data table. For one it saves on network round trips. To get an idea of how much faster batching is I ran a simple test. I inserted 100,000 rows into a data table and then setup a data adapter to do the insert to a database running on Ubuntu in a virtual pc I was running (just so the network round trip wasn't just to localhost).
Without batching: 1 minute 47 seconds
With batching: 4.5 seconds.
Yea.
Unfortunately, if I set the .UpdateBatchSize to anything greater than 1... it bombs. And yes, my .UpdateRowSource is None.
ReplyDeletegreat.. if batching is supported..
ReplyDeleteBatching, hmm.
ReplyDeleteMySQL Connector/ODBC 3.5 provides a fast and reliable way for connecting with a MySQL database server via ODBC (Open DataBase Connectivity, a standard method to connect a DBMS - DataBase Management System - with the database server).
ReplyDeletethanks
MySQL Connector/Net 5.2.0 - Find Help in our db.mysql.announce Forum. ... Added support for batching to MySqlDataAdapter. Batching is fully supported ... Perfmon counters are now created using Wix code instead of a managed custom action ...
ReplyDeleteI would support the batching option. More faster and save time :)
ReplyDeleteIs it more expensive to use it?
Start the MySQL replication master server by issuing this command:
ReplyDeleteshellM> mysqld --ndbcluster --server-id=id \
--log-bin --binlog-format=ROW &
MySQL Connector/Net 5.2.0 a new version of the all-managed .NET driver for MySQL has been released. This release is an alpha and may contain bugs with some of them possibly being severe. You are strongly urged to not use this release in production environments. This release is intended only for testing and feedback purposes.
ReplyDeleteBatching is a serialized processing of a set of messages that allows for optimizations with respect to database round trips. A batch is a unit of work that is atomic; that is, it either all succeeds or all fails. If one operation in a batch succeeds but another operation fails, all the operations that make up the batch are invalidated and must be repeated.
ReplyDeleteu made my day
ReplyDeletelol
were did u get it from?
love it
ReplyDeletegreat post
MySQL is the world's most popular open source database software, with over 100 million copies of its software downloaded or distributed throughout its history. With its superior speed, reliability, and ease of use, MySQL has become the preferred choice for Web, Web 2.0, SaaS, ISV, Telecom companies and forward-thinking corporate IT Managers because it eliminates the major problems associated with downtime, maintenance and administration for modern, online applications.
ReplyDeleteMySQL Connector/Net 5.2.0 a new version of the all-managed .NET driver
ReplyDeletefor MySQL has been released. This release is an alpha and may contain
bugs with some of them possibly being severe. You are strongly urged to
not use this release in production environments. This release is
intended only for testing and feedback purposes.
The ODBC driver accepts OPTION=268435456 (FLAG_NO_BINARY_RESULT) in the connection string to prevent .net from getting an array instead of character data. We switched over to the .net connector and surprise!: the connection string DOES NOT accept the "OPTION" parameter in the connection string. As a result, the old char[] arrays reared their ugly heads again.
ReplyDeleteConnector/Net 5.1 represents a change in how we package our products.
ReplyDeleteUntil now, we've shipped our core provider and the Visual Studio
integration bits as separate downloads.
MySQL Connector/Net 5.2.0 a new version of the all-managed .NET driver
ReplyDeletefor MySQL has been released. This release is an alpha and may contain
bugs with some of them possibly being severe. You are strongly urged to
not use this release in production environments. This release is
intended only for testing and feedback purposes.
This release is an alpha and may contain bugs with some of them possibly being severe.
ReplyDeleteThe Microsoft .NET Framework version 2.0 (x86) redistributable package installs the .NET Framework runtime and associated files required to run applications developed to target the .NET Framework v2.0.
ReplyDeleteBatching support was a must, I am really glad they implemented that.
ReplyDeleteMSBuild has the ability to divide item collections into different categories, or batches, based on item metadata, and run a target or task one time with each batch.
ReplyDeleteBeware of cheap imitations! SpamCop has been protecting the internet community since 1998
ReplyDeletebatching was must. thanxs god it happened
ReplyDeleteToday I played with batching. I started by looking through the source to see how Reggie and the crew implemented it. To my joy I found that the code that enables this feature is not just in the data adapter.
ReplyDeletebeware of aware & aware=you atleast for this article ,you give a good ideas but very hard to implement that?
ReplyDeleteit's an important change.great job
ReplyDeleteI inserted 100,000 rows into a data table and then setup a data adapter to do the insert to a database running on Ubuntu in a virtual pc I was running (just so the network round trip wasn't just to localhost)
ReplyDeleteI must say that its an great improvement..
ReplyDeleteThis is a great upgrade, thanks.
ReplyDeleteWould you like to post a guest post on my blog?
ReplyDeleteGood post, I will mention it on my blog.. Cheers
ReplyDeleteBatch was a very important feature, good thing they included it.
ReplyDelete