MySQL Bugs: #67913: ODBC connector performance problem

Bug #67913	ODBC connector performance problem
Submitted:	16 Dec 2012 2:31	Modified:	21 Jan 2013 9:51
Reporter:	Rayson Lee	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / ODBC	Severity:	S5 (Performance)
Version:	5.2.2, 5.1.11	OS:	Windows (Windows 7 and 8 64-bit)
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any
Tags:	ODBC, performance, problem, slow

Description:
No matter 32-bit 5.1.11 or 32-bit/64-bit ODBC version 5.2.2 perform very slowly under Windows 7 and Windows 8 64-bit OS.
Server version: MySql 5.5 64-bit
Problem did not exist for MySQL ODBC Connector 32-bit version 5.1.8

How to repeat:
Replace ODBC driver with different versions and compare performance

Can you set "Don't use INFORMATION_SCHEMA for Metadata" in Details -> Metadata driver setting?

It can be a server problem because INFORMATION_SCHEMA database in the server has performance problems. The metadata can also be retrieved in other ways, so using the above option tells the driver to do so. However, when it is enabled the driver might not return all metadata.

Bogdan, thanks for your response.
Checked the option as requested but problem still persist.

When testing the driver version 5.2 we have not noticed any significant performance difference with the older versions.

If by any chance you performed debugging or profiling your application did you notice in which particular ODBC function the delays tended to occur?

Also, enabling the ODBC trace in ODBC Administrator causes performance drop.
Please make sure it is disabled.

I did not enable ODBC trace nor did any debugging / profiling on the application. The ODBC commands I used was just connect, query and fetch results. Didn't notice which particular function is causing the problem. I'm not sure if that matters, but I do all testings on standalone machine running Traditional Chinese version of Windows 7 & 8 64-bit OS. Just changed the ODBC driver from 32-bit 5.1.8 to 32-bit/64-bit 5.2.2 will have significant performance difference.

Have you been able to track down what queries/operations affect the performance most of all? Or perhaps you can give us your test case, so we could compare the different driver versions?

Thanks for the feedback and the code example.
Unfortunately, I was not able to repeat the problem with the performance.
I created a test project (uploaded below bug67913.zip) using your VB code and received almost identical results that differs with each other within the measurement error +/- 5ms.

My test case creates a table with five columns:

CREATE TABLE bug67913 (id int primary key auto_increment, col1 varchar(32), col2 int, col3 text, col4 timestamp)

and inserts 50000 rows with randomly generated text and numeric data.
[Click "Create Data" once and wait for ~5-10 sec till "Done!" is displayed]

After the table and data is prepared we can start measuring the performance.
In the text input you can place DSN names that correspond to MySQL ODBC Driver 5.1 or 5.2 and click "Run Test" to see how much time it takes for this driver to get 50000 rows from the table we have just prepared.

I did 5 test runs for each (5.1/5.2w) driver and the results are the same around 3650 ms.

Test case

Attachment: bug67913.zip (application/zip, text), 12.85 KiB.

Thanks for your testing C# program. I compiled it with VS2010 and run on Windows 8 machine with ODBC 5.1.8 32-bit driver and ODBC 5.2.2w 32-bit driver. The results are 1469ms vs 2605ms. Attached screen capture for your reference.

Bug 67913 test result

Attachment: debug.jpg (image/jpeg, text), 205.19 KiB.

The bug is verified.

The result of Connector/ODBC 5.1.8: 1860ms
The result of Connector/ODBC 5.2.3: 2964ms

Interesting thing: when running with MySQL Server 5.5.15 there is no difference between ODBC Driver 5.1.8 and 5.1.11. When running MySQL Server 5.5.27 the difference is very noticeable.

Thank you for trying other server version. So strange!

I have got the same performance issue, and a simple test case that shows the problem.

I have a PC with MySQL ODBC connector version 5.1.8 installed and version 5.3.4 (the latest). The code below just connects 100 times to a local MySQL server, and then disconnects ; it shows a HUGE performance difference:

CSCRIPT //NoLogo myodbc_performance.js
Performance ODBC 5.1.8 : 100xOpen/Close in : 209 ms
Performance ODBC 5.3.4 : 100xOpen/Close in : 102357 ms

Here's the sample js code (myodbc_performance.js):

var strRootPassword="";

// First loop with driver MySQL ODBC 5.1.8 (fast)
var objStart1 = new Date();
for (var n = 1; n <= 100 ; n++)
{
  var objODBC518Connection = WScript.CreateObject("ADODB.Connection");
  objODBC518Connection.Open("Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=mysql;UID=root;Password="+strRootPassword+";");
  objODBC518Connection.Close();
  objODBC518Connection = null;
  delete objODBC518Connection;
}
var objEnd1 = new Date();
WScript.Echo("Performance ODBC 5.1.8 : 100xOpen/Close in : " + (objEnd1-objStart1).toString(10)+" ms");

// Second loop with driver MySQL ODBC 5.3.4 (slow - just like ANY driver after 5.1.8)
var objStart2 = new Date();
for (var n = 1; n <= 100 ; n++)
{
  var objODBC534Connection = WScript.CreateObject("ADODB.Connection");
  objODBC534Connection.Open("Driver={MySQL ODBC 5.3 ANSI Driver};Server=localhost;Database=mysql;UID=root;Password="+strRootPassword+";");
  objODBC534Connection.Close();
  objODBC534Connection = null;
  delete objODBC534Connection;
}
var objEnd2 = new Date();
WScript.Echo("Performance ODBC 5.3.4 : 100xOpen/Close in : " + (objEnd2-objStart2).toString(10)+" ms");

Please note that ALL drivers I've tested have the same performance issue (after 5.1.8).

Simple javascript test case (100x open and close)

Attachment: myodbc_performance.js (application/javascript, text), 1.15 KiB.