IsCrudCommand(String) is relatively slow in high performance scenarios with big commands that have several line breaks

[villanibr]

Summary of the issue

The method EFCoreSecondLevelCacheInterceptor.EFSqlCommandsProcessor.IsCrudCommand(String) is relatively slow in high performance scenarios with big commands that have several line breaks.
Probably because it first splits the entire string (var lines = text.Split('\n')) and then loop through them, even knowing that usually the CrudMarker will be present in the first couple of lines. The Split is probably the performance offender.

Also, my command is supposed to be ignored by using both SkipCachingCommands and SkipCacheInvalidationCommands, so I suspect the IsCrudCommand call could be bypassed. However I didn't investigate the order of events.

I'm attaching two alternative code suggestions, one for .NET 8 with the use of Span<> and another for .NET 4.6.2. Both code files were generated by ChatGPT o1-mini.

Suggestions.zip

Thanks and regards.

Environment

.NET (Core) SDK version: .NET 8
Microsoft.EntityFrameworkCore version: 8.0.10
EFCoreSecondLevelCacheInterceptor version: 4.8.3
Database: PostgreSQL 16 (irrelevant)
Cache provider: in-memory

Example code/Steps to reproduce:

(run in a loop to measure performance)

const string sql =
"""
UPDATE table
SET
column0 = 'value0',
column1 = 'value1',
column2 = 'value2',
column3 = 'value3',
column4 = 'value4',
column5 = 'value5',
column6 = 'value6',
column7 = 'value7',
column8 = 'value8',
column9 = 'value9'
WHERE id = 1
AND condition0 = 'value0'
AND condition1 = 'value1'
AND condition2 = 'value2'
AND condition3 = 'value3'
AND condition4 = 'value4'
AND condition5 = 'value5'
AND condition6 = 'value6'
AND condition7 = 'value7'
AND condition8 = 'value8'
AND condition9 = 'value9'
""";
var efSqlCommandsProcessor = new EFSqlCommandsProcessor(new XxHash64Unsafe());
var isCrudCommand = efSqlCommandsProcessor.IsCrudCommand(sql);

Output:

Exception message:
Full Stack trace:

[Bykiev]

I believe using compiled regex is the best solution, though your solution is twice faster, but this one is cross-platform and very simple:

private static readonly Regex CrudMarkersRegex = new Regex(@"(?i)\b(merge|insert|update|delete|create)\b", RegexOptions.Compiled);

public bool IsCrudCommand(string text)
{
  if (string.IsNullOrWhiteSpace(text))
      return false;

  return CrudMarkersRegex.IsMatch(text);
}

here is Benchmark.NET results with such query:

INSERT INTO <GCP_PROJECT_NAME>.Test_Tables.Nested_Person_Table2
            (id,
            first_name,
            last_name,
            dob,
            addresses)
        SELECT
          id,
          first_name,
          last_name,
          dob,
          ARRAY_AGG(STRUCT(a1.status,
              a1.address,
              a1.city,
              a1.state,
              a1.zip,
              a1.numberOfYears)) AS addresses
        FROM
          `<GCP_PROJECT_NAME>.Test_Tables.Nested_Person_Table`,
          UNNEST(addresses) AS a1
        GROUP BY
          id,
          first_name,
          last_name,
          dob

:

BenchmarkDotNet v0.14.0, Windows 10 (10.0.19044.3086/21H2/November2021Update)
Intel Core i7-7700 CPU 3.60GHz (Kaby Lake), 1 CPU, 8 logical and 4 physical cores
.NET SDK 9.0.200
  [Host] : .NET 6.0.36 (6.0.3624.51421), X64 RyuJIT AVX2

Toolchain=InProcessEmitToolchain

| Method                 | Mean        | Error     | StdDev    | Rank | Gen0   | Allocated |
|----------------------- |------------:|----------:|----------:|-----:|-------:|----------:|
| IsCrudCommandRegex     |   110.72 ns |  1.682 ns |  1.313 ns |    2 |      - |         - |
| IsCrudCommand          |   585.63 ns |  9.917 ns | 13.575 ns |    3 | 0.5789 |    2424 B |
| IsCrudCommandVillanibr |    53.31 ns |  0.477 ns |  0.423 ns |    1 |      - |         - |

upd: if the query doesn't contain any line break the regex solution is the worst:
SELECT id, first_name, last_name, dob, ARRAY_AGG(STRUCT(a1.status, a1.address, a1.city, a1.state, a1.zip, a1.numberOfYears)) AS addresses FROM <GCP_PROJECT_NAME>.Test_Tables.Nested_Person_Table`

| Method                 | Mean        | Error     | StdDev    | Rank | Gen0   | Allocated |
|----------------------- |------------:|----------:|----------:|-----:|-------:|----------:|
| IsCrudCommandRegex     | 1,789.71 ns | 34.179 ns | 69.818 ns |    4 |      - |         - |
| IsCrudCommand          |   119.63 ns |  2.323 ns |  4.189 ns |    2 | 0.0076 |      32 B |
| IsCrudCommandVillanibr |    59.33 ns |  0.511 ns |  0.453 ns |    1 |      - |         - |

// * Hints *
Outliers
  SplitBenchmark.IsCrudCommandRegex: Toolchain=InProcessEmitToolchain     -> 6 outliers were removed (2.01 us..2.11 us)
  SplitBenchmark.IsCrudCommand: Toolchain=InProcessEmitToolchain          -> 4 outliers were removed (137.07 ns..146.28 ns)
  SplitBenchmark.IsCrudCommandVillanibr: Toolchain=InProcessEmitToolchain -> 1 outlier  was  removed (62.26 ns)

[Bykiev]

We can also change the order of sql instructions to this one:

update
insert
merge
delete
create

[Bykiev]

We can also optimize GetRawSqlCommandTableNames the same way:

private static readonly Regex TableRegex = new Regex(
        @"\b(?:(FROM|JOIN|INTO|UPDATE|MERGE)\s+([\w\d\[\]`\"".]+))",
        RegexOptions.Compiled | RegexOptions.IgnoreCase);

public static SortedSet<string> GetRawSqlCommandTableNames(string commandText)
    {
        var tables = new SortedSet<string>(StringComparer.OrdinalIgnoreCase);

        if (string.IsNullOrWhiteSpace(commandText))
            return tables;
        foreach (Match match in TableRegex.Matches(commandText))
        {
            if (match.Groups.Count > 2) 
            {
                var tableName = match.Groups[2].Value
                    .Replace("[", string.Empty)
                    .Replace("]", string.Empty)
                    .Replace("`", string.Empty)
                    .Replace("\"", string.Empty)
                    .Replace("'", string.Empty)
                    .Split('.').Last() 
                    .Trim();

                if (!string.IsNullOrWhiteSpace(tableName))
                {
                    tables.Add(tableName);
                }
            }
        }

        return tables;
    }

Benchmark.NET:

BenchmarkDotNet v0.14.0, Windows 10 (10.0.19044.3086/21H2/November2021Update)
Intel Core i7-7700 CPU 3.60GHz (Kaby Lake), 1 CPU, 8 logical and 4 physical cores
.NET SDK 9.0.200
  [Host] : .NET 6.0.36 (6.0.3624.51421), X64 RyuJIT AVX2

Toolchain=InProcessEmitToolchain

| Method                              | Mean     | Error     | StdDev    | Rank | Gen0   | Allocated |
|------------------------------------ |---------:|----------:|----------:|-----:|-------:|----------:|
| GetRawSqlCommandTableNames          | 7.027 us | 0.1266 us | 0.1816 us |    2 | 1.1444 |    4832 B |
| GetRawSqlCommandTableNamesWithRegex | 3.877 us | 0.0437 us | 0.0365 us |    1 | 0.1907 |     808 B |

// * Hints *
Outliers
  TableNamesBenchmark.GetRawSqlCommandTableNames: Toolchain=InProcessEmitToolchain          -> 6 outliers were removed (7.90 us..8.57 us)
  TableNamesBenchmark.GetRawSqlCommandTableNamesWithRegex: Toolchain=InProcessEmitToolchain -> 2 outliers were removed (4.03 us, 4.34 us)

[Bykiev]

@VahidN, what do you think about regex solution? I'd like to make a PR with proposed changes with regex.

[Bykiev]

I did more benchmarks and this modified solution is the best:

      public bool IsCrudCommandVillanibr()
      {
          if (string.IsNullOrWhiteSpace(Text))
              return false;

          int length = Text.Length;
          int start = 0;

          while (start < length)
          {
              // Find the end of the current line
              int lineEnd = Text.IndexOf('\n', start);
              if (lineEnd == -1)
                  lineEnd = length; // Last line without a newline character

              // Find the first non-whitespace character in the line
              int firstNonWhitespace = start;
              while (firstNonWhitespace < lineEnd && char.IsWhiteSpace(Text[firstNonWhitespace]))
                  firstNonWhitespace++;

              // Check if the remaining line is long enough to contain any CRUD marker
              foreach (var marker in CrudMarkers)
              {
                  int markerLength = marker.Length;
                  if (lineEnd - firstNonWhitespace >= markerLength)
                  {
                      string substr = Text.Substring(firstNonWhitespace, marker.Length);
                      if (substr.Equals(marker, StringComparison.OrdinalIgnoreCase))
                          return true;
                  }
              }

              // Move to the start of the next line
              start = lineEnd + 1; // +1 to skip the newline character
          }

          return false;
      }

[VahidN]

Sorry! I'm not interested to change a readable and maintainable code to a hard to read and main code for ... x ns or us!!
If you think this library is slow for you, just stop using it.