Feature Req: Add configuration option (and implementation) to use CompiledQuery in Linq2Db

[NatElkins]

Description:

This issue proposes an enhancement to Akka.Persistence.Sql to leverage Linq2DB's CompiledQuery feature. This aims to improve performance by reducing query compilation overhead, especially for frequently executed queries.

1. What are Linq2DB Compiled Queries?

Linq2DB compiled queries allow pre-compilation of LINQ expressions into executable SQL. When a query is first executed, Linq2DB translates the LINQ expression tree into a SQL query and caches the execution plan. Subsequent executions of the same query (even with different parameters) reuse this cached plan, bypassing the need for repeated translation and optimization. This can lead to significant performance gains, particularly for queries that are executed frequently or are complex.

2. Potential Use Cases in Akka.Persistence.Sql:

Based on our analysis of the Akka.Persistence.Sql codebase, several areas could benefit from CompiledQuery.Compile. However, given the discussions in linq2db/linq2db#3266, we should prioritize read-heavy operations and approach Data Manipulation Language (DML) operations with caution.

• High-Priority (Read Operations):

  • SqlReadJournal.cs:
    • CurrentEventsByPersistenceIdQuery
    • CurrentEventsByTagQuery
    • EventsByPersistenceIdQuery (live stream)
    • EventsByTagQuery (live stream)
    • CurrentPersistenceIdsQuery
    • PersistenceIdsQuery (live stream)
  • Journal DAO Read Operations (BaseByteArrayJournalDao.cs and specific implementations):
    • HighestSequenceNrAsync: Query to retrieve the highest sequence number.
    • MessagesStream (or equivalent methods for fetching messages): Queries involved in fetching messages for a persistence ID.
  • Snapshot Store DAO Read Operations:
    • Queries for loading snapshots.

• Lower-Priority / Requires Careful Evaluation (DML Operations):

  • Journal DAO DML Operations:
    • WriteMessagesAsync: Queries for inserting journal entries.
    • DeleteMessagesToAsync: Queries for deleting messages.
    • UpdateSequenceNrAsync (if it involves UPDATE statements that could be compiled).
  • Snapshot Store DAO DML Operations:
    • Queries for saving and deleting snapshots.

  Rationale for Caution with DML: As noted in linq2db/linq2db#3266, there have been historical issues and discussions regarding the stability and benefits of CompiledQuery with asynchronous DML operations (UpdateAsync, InsertAsync, DeleteAsync). While some issues might be version-specific, a Linq2DB team member noted that "DML operations support in compiled query was never introduced" formally. Performance gains for DML with compiled queries also seem less consistent than for read queries. Therefore, thorough testing and validation are critical if applying CompiledQuery to DML.

3. Updating Benchmarks:

To validate the performance improvements and ensure no regressions, the existing benchmark suites should be updated:

• Akka.Persistence.Sql.Benchmarks/SqlServer/SqlServerCsvTagBenchmark.cs:
  • Focus on adding benchmark methods for the read journal queries using CompiledQuery.Compile.
  • Compare performance (execution time, allocations) against non-compiled versions.
• Akka.Persistence.Sql.Benchmark.Tests/SqlJournalPerfSpec.cs:
  • Adapt tests exercising the high-priority read operations (e.g., recovery, specific read scenarios) to use compiled queries (conditionally via the toggle).
  • If DML operations are attempted with CompiledQuery, dedicated benchmarks measuring their performance and stability (correctness, absence of exceptions like 'Sequence ... cannot be converted to SQL') are crucial.
  • Continue to measure throughput (msg/s), latency, and memory allocations (using DotMemoryUnit).
• New Benchmark Class (Optional but Recommended):
  • A dedicated CompiledQueryBenchmark could still be useful for isolating tests, especially if exploring DML compilation, ensuring a focused environment to catch any issues.

4. API Changes and Toggling Behavior:

The proposal to use a configuration flag remains sound and becomes even more important given the nuanced behavior with DML.

• Configuration Setting:

  • Introduce use-compiled-queries = false in akka.persistence.journal.sql, akka.persistence.snapshot-store.sql, and akka.persistence.query.journal.sql.
  • Defaulting to false ensures backward compatibility.
  • Consider separate flags for read and DML operations if initial DML support is experimental: e.g., use-compiled-read-queries = true and use-compiled-dml-queries = false. This would allow enabling compiled reads more broadly while DML remains off by default or for specific database providers.

• Internal Implementation:

  • The DAO and SqlReadJournal classes will use this flag(s) to switch between compiled and standard query execution paths.
  • The example provided previously for internal implementation remains relevant.

  // Example in a DAO class (for a read query)
  private readonly bool _useCompiledReadQueries;
  private static readonly Func<IDataContext, string, long, IAsyncEnumerable<JournalRow>> _getMessagesCompiled =
      CompiledQuery.Compile((IDataContext dc, string persistenceId, long fromSeqNr) =>
          dc.GetTable<JournalRow>()
            .Where(r => r.PersistenceId == persistenceId && r.SequenceNumber >= fromSeqNr)
            .OrderBy(r => r.SequenceNumber)
            .AsAsyncEnumerable());
  
  public MyDao(PluginSettings settings, ...)
  {
      _useCompiledReadQueries = settings.UseCompiledReadQueries; // or general UseCompiledQueries
  }
  
  public IAsyncEnumerable<JournalRow> GetMessagesAsync(string persistenceId, long fromSequenceNr, ...)
  {
      if (_useCompiledReadQueries)
      {
          return _getMessagesCompiled(GetDataConnection(), persistenceId, fromSeqNr);
      }
      // ... else standard query
  }

Next Steps:

1. Investigate the current Linq2DB version used in Akka.Persistence.Sql and review any relevant Linq2DB release notes since issue #3266 was active.
2. Implement the configuration flag(s).
3. Phase 1: Focus on implementing CompiledQuery.Compile for the high-priority read operations listed.
4. Update/add benchmarks for these read operations.
5. Thoroughly test Phase 1 changes across all supported database providers.
6. Phase 2 (Optional/Conditional): Evaluate and, if deemed stable and beneficial, implement CompiledQuery.Compile for DML operations. This phase would require extensive testing due to the points raised in linq2db/linq2db#3266.
7. Document the new configuration option(s) and clearly state the recommendations and any known limitations, especially regarding DML.