Bug #8387 Connecting with NAMES and character_set_results can be up to 18 times slower.
Submitted: 9 Feb 2005 6:36 Modified: 22 Jun 2005 21:13
Reporter: Lynn Eriksen Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:1.0.4 OS:Windows (WinXP/Win2003)
Assigned to: Reggie Burnett CPU Architecture:Any

[9 Feb 2005 6:36] Lynn Eriksen
Description:
When executing:

cmd.CommandText = "SET NAMES " + charSet + "; SET character_set_results=NULL";
cmd.ExecuteNonQuery();

line 178 in Driver.cs causes a server performance penalty over network (lan) to server).

How to repeat:
Reggie, I did some testing per the thread on the third:

I edited out line 178 in Driver.cs:

// now tell the server which character set we will send queries in and which charset we
// want results in
	if (version.isAtLeast(4,1,0)) 
	{
		cmd.CommandText = "SET NAMES " + charSet + "; SET character_set_results=NULL";
		//cmd.ExecuteNonQuery();
}

I simply did not execute the non query.

Here are some rough results:

With out the query (in seconds):

0.006022
0.006208
0.007446
0.009333

With the query:

0.141408
0.140769
0.135088
0.193539

That's 18 times slower.

I made a compile and I have implemented in my project. It's a forum/community site - so speed is of the essence.

It now works very fast. I am using windows XP. The server in on a network server. My development machine in WinXp. The server is on Win2k. The website on Win2K3. I am connection through a private lan to the 4.1.9 server. Caching and tracing of domain names is turned off. 

I will report this on the bug listing.

Suggested fix:
Please make turned off by default and optional.

I'm curriuous if this is a connector/net problem, ado.net problem, or MySQL server problem.
[27 Feb 2005 2:10] Lynn Eriksen
This does not happen with 4.1.10.
[22 Jun 2005 21:13] Reggie Burnett
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

I changed the code to only do the set names if the connection or client charset is different than what we already have.  However, I am still setting the results charset to NULL to prevent the server from converting all results to a single charset.  

I could add some type of connection string option that would a user would set if they are sure they are always going to be using the charset on the server.  However, with connection pooling I am not sure how much this will help.  If you are seeing perf problems, perhaps you need to better tune your connectoin pools?