Bug #45699 MySqlHelper.EscapeString() performance can be improved

• Submitted: 24 Jun 2009 13:05
• Modified: 8 Jul 2009 16:29
• Reporter: Franco A.
• Status: Closed
• Category: Connector/Net
• Severity: S5 (Performance)
• Version: 6.0
• OS: Any

[24 Jun 2009 13: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 13:07] Franco A.

Sample file given also as text

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

[30 Jun 2009 23: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

[1 Jul 2009 0:11] Reggie Burnett

fixed in 5.2.7+, 6.0.5+

[8 Jul 2009 16: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.