Bug #36836 TokenizeSql adding query overhead and causing high CPU for larger queries
Submitted: 20 May 2008 23:30 Modified: 11 Jul 2008 13:27
Reporter: Maxim Mass Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S5 (Performance)
Version: OS:Any
Assigned to: CPU Architecture:Any
Tags: optimization, patch provided, TokenizeSql

[20 May 2008 23:30] Maxim Mass 
Description:
The TokenizeSql method goes through every character in the query and appends it to a stringbuilder as well as accessing relatively expensive properties.

How to repeat:
Call a lengthy query in a tight for loop and attach a profiler. Examine time spent in TokenizeSql.

The top three hits are:
100.00 % TokenizeSql - 7434.7 ms - 3000 calls - MySql.Data.MySqlClient.Statement.TokenizeSql(String)
  34.77 % Append - 2584.9 ms - 1386900 calls - System.Text.StringBuilder.Append(Char)
  13.55 % get_Connection - 1007.8 ms - 2762500 calls - MySql.Data.MySqlClient.MySqlCommand.get_Connection()
  13.43 % get_ParameterMarker - 998.3 ms - 2759500 calls - MySql.Data.MySqlClient.MySqlConnection.get_ParameterMarker()

Suggested fix:
Remove the stringbuilder and keep track of current position int he query. Remove redundant calls to expensive properties.

With patch:

100.00 % TokenizeSql - 58.307 ms - 2306 calls - MySql.Data.MySqlClient.Statement.TokenizeSql(String)
  24.52 % InternalSubStringWithChecks - 14.299 ms - 7117 calls - System.String.InternalSubStringWithChecks(Int32, Int32, Boolean)
[20 May 2008 23:32] Maxim Mass 
Patch based on connector v5.14

Attachment: TokenizeSqlPatch.txt (text/plain), 3.97 KiB.
[26 May 2008 11:50] Tonci Grgin 
Hi Maxim and thanks for your report. Before going on vacation I notified Reggie of it and he'll take a look soon as we are in process of speeding-up c/NET right now.
[27 May 2008 21:01] 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/47111
[27 May 2008 21:03] Reggie Burnett 
Fixed in 5.3
[11 Jul 2008 13:27] Tony Bedford 
An entry has been added to the 5.3.0 changelog:

The TokenizeSql method was adding query overhead and causing high CPU utilization for larger queries.