Monday, September 24, 2007

Connector/Net 5.2 now supports batching

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.

31 comments:

  1. Unfortunately, if I set the .UpdateBatchSize to anything greater than 1... it bombs. And yes, my .UpdateRowSource is None.

    ReplyDelete
  2. great.. if batching is supported..

    ReplyDelete
  3. Batching, hmm.

    ReplyDelete
  4. MySQL 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).

    thanks

    ReplyDelete
  5. Randolph, NJ Licensed ElectricianFebruary 9, 2009 at 7:11 PM

    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 ...

    ReplyDelete
  6. I would support the batching option. More faster and save time :)
    Is it more expensive to use it?

    ReplyDelete
  7. Start the MySQL replication master server by issuing this command:

    shellM> mysqld --ndbcluster --server-id=id \
    --log-bin --binlog-format=ROW &

    ReplyDelete
  8. Los Angeles criminal lawyerFebruary 17, 2009 at 4:41 AM

    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.

    ReplyDelete
  9. Batching 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.

    ReplyDelete
  10. u made my day
    lol
    were did u get it from?

    ReplyDelete
  11. love it
    great post

    ReplyDelete
  12. 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.

    ReplyDelete
  13. Cheap Calling CardsApril 11, 2009 at 4:47 PM

    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.

    ReplyDelete
  14. 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.

    ReplyDelete
  15. Connector/Net 5.1 represents a change in how we package our products.
    Until now, we've shipped our core provider and the Visual Studio
    integration bits as separate downloads.

    ReplyDelete
  16. 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.

    ReplyDelete
  17. This release is an alpha and may contain bugs with some of them possibly being severe.

    ReplyDelete
  18. Free Credit ReportApril 26, 2009 at 2:41 AM

    The 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.

    ReplyDelete
  19. Batching support was a must, I am really glad they implemented that.

    ReplyDelete
  20. MSBuild 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.

    ReplyDelete
  21. Beware of cheap imitations! SpamCop has been protecting the internet community since 1998

    ReplyDelete
  22. batching was must. thanxs god it happened

    ReplyDelete
  23. rapidshare megaupload searchMay 8, 2009 at 2:38 PM

    Today 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.

    ReplyDelete
  24. beware of aware & aware=you atleast for this article ,you give a good ideas but very hard to implement that?

    ReplyDelete
  25. it's an important change.great job

    ReplyDelete
  26. 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)

    ReplyDelete
  27. I must say that its an great improvement..

    ReplyDelete
  28. This is a great upgrade, thanks.

    ReplyDelete
  29. Would you like to post a guest post on my blog?

    ReplyDelete
  30. Good post, I will mention it on my blog.. Cheers

    ReplyDelete
  31. Batch was a very important feature, good thing they included it.

    ReplyDelete