I've spent the last few days working on a support case where a customer was comparing the speed of Connector/Net with an application that uses our native C api. His test was using a server side prepared statement to insert 3 sets of values in a loop that executed 600,000 times. The table is a simple 3 column table and the SQL looks like this:
INSERT INTO test VALUES (@p1, @p2, @p3), (@p4, @p5, @p6), (@p7, @p8, @p9)
He was seeing 34,529 records / second using libmysql.dll but only about 24,000 records / second using Connector/Net. He understandably wanted to know why there was such a difference and if we could do something about it. Absolutely!
We solved this problem through a combination of optimizations made to our code and some changes made to the testing application. I won't say much about the optimizations. We made some obvious changes like moving a lot more of the prepared statement work to the prepare phase and out of the execute phase. We also discovered that BitConverter.ToBytes() is a lot slower than our hand-coded methods for reading and writing integer values. We replaced usages of ArrayList with generic equivalents (it's pretty amazing how much faster generics are actually).
One of the bigger changes we made was to use a second hash for parameter name lookups. When you are talking about loops with lots of iterations even the smallest inefficiencies can kill you. Since .NET supports name lookup for parameters we use a hashtable to map parameter names to indices (we made this change when one of our customer was complaining that parameter lookup was slow on one of his commands that had > 30,000 parameters. Yeah.). Name lookup should be case-insensitive so we use a case-insensitive comparer with our hashtable. So the optimization we made this time is to use two hash tables. The first is case sensitive and is checked first. If that fails then we check the second and return failure only if both fail.
That's what we did in our code. We also made some changes to the testing app to make sure the comparison is truly apples to apples. Let's first look at the loop code in each case.
|
|
|
|
Don't get hung up on the output of these being slightly different. The point here is that the C code is using simple pointer arithmetic during each loop where the C# code is doing array lookup. The array lookup code is quite a bit slower because the get accessor for the parameter collection does some bounds checking so that a nicer exception will be thrown in the event of an out-of-bounds index.
Another change that was made to the testing app was to disable the command timeout in the C# code. This is a small point but when the command timeout is greater than zero then a timer is constructed and started during each execution. In this case that is 600,000 timers! Again, since the C code doesn't provide any type of command timeout it's not a true apples-to-apples comparison.
So, in a nutshell, benchmarking against libmysql is sort of like racing a jet engine. Very fast but not a great deal of "safety features". Still, after many hours with dotTrace the results speak for themselves.
C app = 35,294 records per second.
C# app = 35,321 records per second.
Reggie, good deal!
ReplyDelete30,000 params?!?!
Great new Reggie,
ReplyDeleteWhat version of the .net connector will these changes be in?
Pleb,
These changes will appear in 5.3 which we will push out on an accelerated timeframe. 5.2 is too close to GA to include this many changes.
ReplyDeletenice one... lolz... hehehe. great codes.
ReplyDeleteHello Reggie,
ReplyDeleteI've tested the perf of the connection pooling in 5.2.3 and was a little badly surprised. So that everybody can "enjoy" the discussion, I've left a post on the Mysql connector/net forum (see http://forums.mysql.com/read.php?38,224630,224630#msg-224630 ) and was wondering if the change you speak in this post would improve things...
If possible, I'd be willing to test connector/net v5.3 in our app to see the impact.
As well, if you want more details on our code, feel free (it's a log analysis application and so we need to write records at a minimum rate of thousands of records by second).
Hello again Reggie,
ReplyDeleteStill looking for some perf improvment, I've run a test to compare the insertion of records in batch between MySqlDataAdapter and a custom query I build using a StringBuilder.
Comparison gives the single INSERT statement with multiple values 2.5 faster that MySqlAdapter... Do I do something wrong? I've left a post with more details on http://forums.mysql.com/read.php?38,224747,224747#msg-224747 .
Thanks in advance for your time and help!
Christian
Thanks in advance for your time and help!
ReplyDeleteGreat comparison reggie.
ReplyDeleteThis concept comes under advance engineering..50% of Instrumentation Engineer will involve in this concept..
regards,
<a href="http://www.best-mobile-contracts.co.uk/pay-as-you-go.aspx">pay as you go mobile phones</a>
Sir i am having a small doubt..
ReplyDeleteIs count++ or count+=1 are same??
If not can you please explain me the difference?
thank you sir..
I am surprised you willing took that on. But, I am sure it was a interesting experience. My what fun we have these days debugging the code. I know I wouldn't of lasted 20 minutes. That's why I have pay genius' like you to do it for me. You learn what you do best and do it and ask others to help for the thins you dont.
ReplyDeleteGood job guys.
Thank you very much..
ReplyDeleteIf possible, I'd be willing to test connector/net v5.3 in our app to see the impact.
As well, if you want more details on our code, feel free (it's a log analysis application and so we need to write records at a minimum rate of thousands of records by second).
Hello again Reggie,
ReplyDeleteStill looking for some perf improvment, I've run a test to compare the insertion of records in batch between MySqlDataAdapter and a custom query I build using a StringBuilder.
Hello..
ReplyDeleteThis concept is from advanced engineering.These changes will appear in 5.3 which we will push out on an accelerated timeframe. 5.2 is too close to GA to include this many changes
<a href="http://www.ultimatetruck.com/departments/Tonneau_Covers.aspx">Tonneau Covers</a>
thanks for nice post
ReplyDeleteloves it
They are racing but Google is still the Top of all.
ReplyDeletegreat post, i always looking for this kind of post.
ReplyDeleteGood post.
ReplyDeletehello sir
ReplyDeletei am having one doubt in connectivity between oracle and vb,when i try to connect the database its asking Service name.how can i find out my service name sir
Thanks
findoptimalhealth
<a href="http://www.findoptimalhealth.com/hawaiian-noni-juice.php">findoptimalhealth</a>
Excellent job. Race that darn jet engine! I'd have to argue with you on some points but all in all very well structured article. Cheers and regards.
ReplyDeleteI'd be willing to test connector/net v5.3 in our app to see the impact.
ReplyDeleteAs well, if you want more details on our code, feel free (it's a log analysis application and so we need to write records at a minimum rate of thousands of records by second).
Excellent job. Race that darn jet engine! I'd have to argue with you on some points but all in all very well structured article. Cheers and regards.
ReplyDeleteThanks so much for sharing and making us all think a little more about your nice content
ReplyDeleteWow, this article really makes sense especially for helping us for showing some codes and I really appreciate it. Thanks!
ReplyDeleteHoping that this will not be the lat post that I could be read written by you. Thanks for this information.
ReplyDeleteThank you for sharing. This was a very interesting read.
ReplyDelete