Bug #103292 Poor Driver Read Performance
Submitted: 12 Apr 2021 19:27 Modified: 13 Apr 2021 3:27
Reporter: Mike Miller Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / NET Severity:S5 (Performance)
Version:8.0.23 OS:Any
Assigned to: CPU Architecture:Any

[12 Apr 2021 19:27] Mike Miller
Description:
The NativeDriver's ReadColumnValue method performs very poorly due to the repeated construction of the GUID regex (see code below and at https://github.com/mysql/mysql-connector-net/blob/8.0/MySQL.Data/src/NativeDriver.cs).  In my benchmarking, more than 95% of the time in MySqlDataReader.Read seems to be taken by that constructor. 

<code>
public IMySqlValue ReadColumnValue(int index, MySqlField field, IMySqlValue valObject)
{
  long length = -1;
  bool isNull;
  Regex regex = new Regex(@"(?i)^[0-9A-F]{8}[-](?:[0-9A-F]{4}[-]){3}[0-9A-F]{12}$"); // check for GUID format

  if (nullMap != null)
  {
    isNull = nullMap[index + 2];
    if (!MySqlField.GetIMySqlValue(field.Type).GetType().Equals(valObject.GetType()) && !field.IsUnsigned)
      length = packet.ReadFieldLength();
  }
  else
  {
    length = packet.ReadFieldLength();
    isNull = length == -1;
  }

  if ((valObject.MySqlDbType is MySqlDbType.Guid && !Settings.OldGuids) &&
    !regex.IsMatch(Encoding.GetString(packet.Buffer, packet.Position, (int)length)))
  {
    field.Type = MySqlDbType.String;
    valObject = field.GetValueObject();
  }

  packet.Encoding = field.Encoding;
  packet.Version = version;
  var val = valObject.ReadValue(packet, length, isNull);

  if (val is MySqlDateTime d)
  {
    d.TimezoneOffset = field.driver.timeZoneOffset;
    return d;
  }

  return val;
}
</code>

How to repeat:
This affects any read query, although obviously the fraction of runtime will vary based on the specifics of the query, database deployment, etc.

Suggested fix:
Making the regex a static field of the NativeDriver class would fix repeated construction on every call of ReadColumnValue.  This shouldn't be a problem, as Regex instances are thread-safe and immutable (see https://docs.microsoft.com/en-us/dotnet/standard/base-types/thread-safety-in-regular-expre...)
[13 Apr 2021 3:02] Daniel Valdez
Duplicated of Bug#101714.
[13 Apr 2021 3:27] Mike Miller
Thanks for marking as duplicate, and apologies for not finding that myself!