Hi,
I'm executing a "select *" from a table with about 10,000 rows in a loop. I'm seeing that using MySQL Connector/ODBC version 3.51 has a perf advantage over the MySqlDirect of about 15%. I'm perplexed as to why this may be so since MySqlDirect would have direct access to MySql db without going through ODBC layer . Can some experts comment on it?
On the other hand, MySqlDirect beats using ODBC by about 7 times when I execute "select count(*)". Can some experts also comment on this?
Thanks!
Performance of MySqlDirect vs MySQL Connector/ODBC
The [b]select[/b] query code:
static void Main(string[] args)
{
for (int i = 0; i < 100; ++i)
{
MySqlConnection mySqlConnection = new MySqlConnection("User Id=blah;Database=blahdb;Host=[i]ipAddress[/i];Password=blah"); ;
MySqlDataReader reader = null;
try
{
mySqlConnection.Open();
MySqlCommand command = new MySqlCommand("select * from cust", mySqlConnection);
command.CommandType = CommandType.Text;
reader = command.ExecuteReader();
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
finally
{
if (reader != null)
{
reader.Close();
}
mySqlConnection.Close();
}
}
}
[b]Table Definition[/b]:
CREATE TABLE `cust` (
`userID` int(11) NOT NULL auto_increment,
`dID` int(11) NOT NULL default '0',
`userp` varchar(100) NOT NULL default '',
`store` varchar(100) NOT NULL default '',
`enabled` int(11) NOT NULL default '0',
`notify` tinyint(4) default '1',
`notify_newonly` tinyint(4) NOT NULL default '1',
`pChanged` tinyint(4) NOT NULL default '0',
`KeepCopy` tinyint(4) NOT NULL default '1',
`LastAccessed` datetime default NULL,
`QLastAccessed` datetime default NULL,
`DateCreated` datetime NOT NULL default '0000-00-00 00:00:00',
`LastUpdated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_T
IMESTAMP,
`primaryEmail` varchar(255) NOT NULL default '',
`FirstName` varchar(50) default NULL,
`LastName` varchar(50) default NULL,
`sfilter` tinyint(4) NOT NULL default '1',
`vFilter` tinyint(4) NOT NULL default '1',
`pFilter` tinyint(4) NOT NULL default '1',
`uFilter` tinyint(1) NOT NULL default '1',
`cFilter` tinyint(4) NOT NULL default '1',
`DateDeleted` datetime default '0000-00-00 00:00:00',
`LastUpdateBy` int(11) default NULL,
`notify_lang` tinyint(4) NOT NULL default '0',
`gID` int(11) NOT NULL default '0',
`dir_svc_enable` tinyint(4) NOT NULL default '0',
`pDate` timestamp NOT NULL default '0000-00-00 00:00:00',
`UILanguage` tinyint(4) default NULL,
`p` varchar(100) default NULL,
PRIMARY KEY (`userID`),
UNIQUE KEY `primaryEmail` (`primaryEmail`),
KEY `dID` (`dID`),
FULLTEXT KEY `account_search` (`primaryEmail`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
Thanks!
static void Main(string[] args)
{
for (int i = 0; i < 100; ++i)
{
MySqlConnection mySqlConnection = new MySqlConnection("User Id=blah;Database=blahdb;Host=[i]ipAddress[/i];Password=blah"); ;
MySqlDataReader reader = null;
try
{
mySqlConnection.Open();
MySqlCommand command = new MySqlCommand("select * from cust", mySqlConnection);
command.CommandType = CommandType.Text;
reader = command.ExecuteReader();
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
finally
{
if (reader != null)
{
reader.Close();
}
mySqlConnection.Close();
}
}
}
[b]Table Definition[/b]:
CREATE TABLE `cust` (
`userID` int(11) NOT NULL auto_increment,
`dID` int(11) NOT NULL default '0',
`userp` varchar(100) NOT NULL default '',
`store` varchar(100) NOT NULL default '',
`enabled` int(11) NOT NULL default '0',
`notify` tinyint(4) default '1',
`notify_newonly` tinyint(4) NOT NULL default '1',
`pChanged` tinyint(4) NOT NULL default '0',
`KeepCopy` tinyint(4) NOT NULL default '1',
`LastAccessed` datetime default NULL,
`QLastAccessed` datetime default NULL,
`DateCreated` datetime NOT NULL default '0000-00-00 00:00:00',
`LastUpdated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_T
IMESTAMP,
`primaryEmail` varchar(255) NOT NULL default '',
`FirstName` varchar(50) default NULL,
`LastName` varchar(50) default NULL,
`sfilter` tinyint(4) NOT NULL default '1',
`vFilter` tinyint(4) NOT NULL default '1',
`pFilter` tinyint(4) NOT NULL default '1',
`uFilter` tinyint(1) NOT NULL default '1',
`cFilter` tinyint(4) NOT NULL default '1',
`DateDeleted` datetime default '0000-00-00 00:00:00',
`LastUpdateBy` int(11) default NULL,
`notify_lang` tinyint(4) NOT NULL default '0',
`gID` int(11) NOT NULL default '0',
`dir_svc_enable` tinyint(4) NOT NULL default '0',
`pDate` timestamp NOT NULL default '0000-00-00 00:00:00',
`UILanguage` tinyint(4) default NULL,
`p` varchar(100) default NULL,
PRIMARY KEY (`userID`),
UNIQUE KEY `primaryEmail` (`primaryEmail`),
KEY `dID` (`dID`),
FULLTEXT KEY `account_search` (`primaryEmail`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
Thanks!
According to our performance tests results MySQLDirect .NET exceeds OleDb, ODBC drivers and is no worse than Connector/NET provider from MySQL AB.
Our test code:
If uncomment the string MySQLDirect .NET superiority will become obvious.
Our test code:
Code: Select all
private static TimeSpan Chronometer(IDbConnection conn) {
if (conn.State != ConnectionState.Open)
conn.Open();
DateTime start = DateTime.Now;
try {
for (int i = 0; i < 100; ++i) {
IDbCommand command = conn.CreateCommand();
command.CommandText = "select * from cust";
command.CommandType = CommandType.Text;
using (IDataReader reader = command.ExecuteReader());
//while (reader.Read()) {}
}
}
finally {
conn.Close();
}
return DateTime.Now - start;
}
static void Main(string[] args)
{
System.Data.Odbc.OdbcConnection odbcConnection = new System.Data.Odbc.OdbcConnection();
odbcConnection.ConnectionString = "DSN=mysource;user id=root;initial catalog=test;password=root;Port=3308";
odbcConnection.Open();
Console.WriteLine(Chronometer(odbcConnection).TotalMilliseconds);
MySql.Data.MySqlClient.MySqlConnection netConnection = new MySql.Data.MySqlClient.MySqlConnection();
netConnection.ConnectionString = "User Id=root;Database=test;Password=root;Port=3308;Host=server";
netConnection.Open();
Console.WriteLine(Chronometer(netConnection).TotalMilliseconds);
CoreLab.MySql.MySqlConnection mySqlConnection = new CoreLab.MySql.MySqlConnection();
mySqlConnection.ConnectionString = "User Id=root;Database=test;Password=root;Port=3308;Host=server";
mySqlConnection.Open();
Console.WriteLine(Chronometer(mySqlConnection).TotalMilliseconds);
}
Code: Select all
//while (reader.Read()) {}
Yes, with the "while (reader.Read())" statement in, the perf of MySqlDirect exceeds that of MySql by about 20%.
However, without that statement, it was a push.
Thanks for your prompt reply and on hindsight, it probably makes more sense to do something with the data returned by ExecuteReader in my initial test than to just throw it away.
However, without that statement, it was a push.
Thanks for your prompt reply and on hindsight, it probably makes more sense to do something with the data returned by ExecuteReader in my initial test than to just throw it away.