Bug #36836 TokenizeSql adding query overhead and causing high CPU for larger queries
Submitted: 21 May 2008 1:30 Modified: 11 Jul 2008 15:27
Reporter: Maxim Mass
Status: Closed
Category:Connector/Net Severity:S5 (Performance)
Version: OS:Any
Assigned to: Target Version:
Tags: patch provided, TokenizeSql, optimization
Triage: D4 (Minor)

[21 May 2008 1: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)
[21 May 2008 1:32] Maxim Mass 
Patch based on connector v5.14

Attachment: TokenizeSqlPatch.txt (text/plain), 3.97 KiB.
[26 May 2008 13: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 23: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 23:03] Reggie Burnett 
Fixed in 5.3
[11 Jul 2008 15: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.