Bug #45699 MySqlHelper.EscapeString() performance can be improved
Submitted: 24 Jun 2009 11:05 Modified: 8 Jul 2009 14:29
Reporter: Franco A. Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S5 (Performance)
Version:6.0 OS:Any
Assigned to: CPU Architecture:Any

[24 Jun 2009 11:05] Franco A.
Description:
EscapeString code does escaping by calling string.Replace a couple of times, this looks to me like a performance bottleneck because for every line a new string gets allocated and another one goes to the garbage collector.
I tried using a StringBuilder and, if my tests are correct, I got a great performance improvement.

How to repeat:
Here's a snippet class that computes time for 1milion strings using the provider and my version.
My results are 44 sec for the first version, 18 sec for the second:

using System;
using System.Collections.Generic;
using System.Text;

public class MyClass
{
	public static void Main()
	{
		string s = @"wefdfmsad fsd fsd   sdfsdf s fw eerf wef 45 tet h4687ir '' 45t4rg ' ' 'e rtg 45t45t "
			+ "\"\"\"\"\"\"iunhoi\"ion\"oin\"oin\"\"oin  \"\"\"\"p popompm \"\"\" wsrfs f we f34rfg 45gerg "
			+ "\\popok\\pokpok \\ \\ \\ \\\\ po m \\\\ \\\\ompom rgt 4erg 45g`````oinp```ponpo`pom```````` "
			+ "pomm´´´´´ ´ ´´pmpoom´´´´´pompom´´´´popio3m5rg 45 geh3 56htwge rt245g56ygergeggggggggge´´´´´´"
			+ "’’’’’’’’ponpoompo’’’ ’ ’pom po lo kjbojb98inl ’’’pj09i op’09j’’’’09u0inuog’’’  ’’0o9unio o’’"
			+ "fwr sdfe rtgefg e5 y4r gdfgdf ‘‘‘‘‘‘piojp p‘‘‘jpoj‘oi‘‘‘‘piojpu09j‘‘‘‘pojpoj09jhn‘‘‘09u0'9jp";
		
		string s1, s2;
		
		s1 = EscapeString(s);
		s2 = EscapeStringNew(s);
		WL(s1);
		WL(s2);
		if (s1 != s2)
		{
			WL("Strings are different!");
			return;
		}
		
		DateTime dt1 = DateTime.Now;
		for (int i = 0; i < 1000000; i++)
		{
			s1 = EscapeString(s);
		}
		WL(DateTime.Now - dt1);
		
		DateTime dt2 = DateTime.Now;
		for (int i = 0; i < 1000000; i++)
		{
			s1 = EscapeStringNew(s);
		}
		WL(DateTime.Now - dt2);
		
		RL();
	}
	
	public static string EscapeString(string value)
	{
		value = value.Replace("\\", "\\\\");
		value = value.Replace("\'", "\\\'");
		value = value.Replace("\"", "\\\"");
		value = value.Replace("`", "\\`");
		value = value.Replace("´", "\\´");
		value = value.Replace("’", "\\’");
		value = value.Replace("‘", "\\‘");
		return value;
	}
	
	public static string EscapeStringNew(string value)
	{
		StringBuilder sb = new StringBuilder();
		for (int i = 0; i < value.Length; i++)
		{
			if (value[i] == '\\' 
				|| value[i] == '\''
				|| value[i] == '\"'
				|| value[i] == '`'
				|| value[i] == '´'
				|| value[i] == '’'
				|| value[i] == '‘')
				sb.Append("\\");
			sb.Append(value[i]);
		}
		return sb.ToString();
	}
	
	#region Helper methods

	private static void WL(object text, params object[] args)
	{
		Console.WriteLine(text.ToString(), args);	
	}
	
	private static void RL()
	{
		Console.ReadLine();	
	}
	
	private static void Break() 
	{
		System.Diagnostics.Debugger.Break();
	}

	#endregion
}

Suggested fix:
Use a StringBuilder like this:

	public static string EscapeStringNew(string value)
	{
		StringBuilder sb = new StringBuilder();
		for (int i = 0; i < value.Length; i++)
		{
			if (value[i] == '\\' 
				|| value[i] == '\''
				|| value[i] == '\"'
				|| value[i] == '`'
				|| value[i] == '´'
				|| value[i] == '’'
				|| value[i] == '‘')
				sb.Append("\\");
			sb.Append(value[i]);
		}
		return sb.ToString();
	}
[24 Jun 2009 11:07] Franco A.
Sample file given also as text

Attachment: EscapeString.cs (text/plain), 2.22 KiB.

[30 Jun 2009 21:58] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/77590
[30 Jun 2009 22:11] Reggie Burnett
fixed in 5.2.7+, 6.0.5+
[8 Jul 2009 14:29] Tony Bedford
An entry was added to the 5.2.7 and 6.0.5 changelogs:

The EscapeString code carried out escaping by calling string.Replace multiple times. This resulted in a performance bottleneck, as for every line a new string was allocated and another was disposed of by the garbage collector.