Performance of MySqlDirect vs MySQL Connector/ODBC

derekt · Post by **derekt** » Thu 23 Mar 2006 02:40

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!

Serious · Post by **Serious** » Thu 23 Mar 2006 09:11

We need your table definition and code in which you execute SELECT query.

derekt · Post by **derekt** » Thu 23 Mar 2006 18:19

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!

Alexey · Post by **Alexey** » Fri 24 Mar 2006 10:28

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:

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);
    }

If uncomment the string

Code: Select all

//while (reader.Read()) {}

MySQLDirect .NET superiority will become obvious.

Guest · Post by **Guest** » Fri 24 Mar 2006 19:33

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.