--- title: "Performance Notes" description: "How RoomSharp keeps generated DAO, query, transaction, and bulk-insert paths fast." canonical: "https://roomsharp.dev/docs/v0.5.4/performance" source: "src/content/v0.5.4/performance.mdx" --- # Performance Notes RoomSharp is optimized around a simple rule: move as much work as possible to compile time, then keep runtime database paths explicit, provider-aware, and allocation-conscious. This page explains the performance model behind generated DAOs, QueryExtensions, transactions, bulk inserts, and provider-specific tuning. ## Performance Model RoomSharp performance comes from several layers working together: | Layer | What RoomSharp Optimizes | |-------|--------------------------| | Source generator | Emits concrete DAO and database implementations at build time. | | Command paths | Reuses prepared command shapes and session-scoped command instances where possible. | | Mapping | Uses generated mapping metadata and IL-based materializers for typed reads. | | Transactions | Keeps transaction/session ownership explicit and avoids shared mutable command state. | | Bulk inserts | Routes generated collection inserts through provider-aware batch insert engines. | | Providers | Uses dialect-specific SQL, identifier formatting, FTS predicates, and connection behavior. | Performance depends on provider, schema, indexes, transaction boundaries, storage device, connection string, and result shape. Always benchmark the workload that matters to your application. ## Generated DAO Hot Paths Generated DAO methods avoid the main costs that usually appear in runtime-driven ORMs: - No runtime reflection to discover entity properties. - No dynamic method lookup for DAO methods. - SQL parameter binding is emitted into generated code. - Entity materializers are generated or cached once per shape. - Parallel mode uses session-scoped commands instead of shared command fields. - Serialized transaction paths use normalized transaction commands and cached command clones. The result is a predictable execution path: method call, command preparation/binding, provider execution, and typed materialization. ## Mapping And Materialization RoomSharp's IL mapping layer is designed for typed reads: - `ReaderKind` selects typed reader calls such as `GetInt32`, `GetString`, and provider-aware boolean handling. - Value-type mapping avoids unnecessary boxing in generated materializers. - Constructor and setter-based projections are compiled into direct materializers. - `ILFactoryRefReader` supports ref-reader paths used by generated code where applicable. This matters most for repeated queries and range reads where materialization overhead can dominate after the database has returned rows. ## Command Reuse And Transactions The 0.5 line improved generated command handling: - Parallel generated DAOs use `DbSession.GetOrCreateCommand(...)` for session-local command reuse. - Serialized generated DAOs avoid shared command state during transactions by cloning command templates for the active transaction connection. - Transaction command clones are cached by command reference identity inside the transaction scope. - Generated `DisposeAsync` paths avoid unnecessary async state machines where no asynchronous work is needed. - SQL Server insert fallback delegates are generated only when the fallback path can actually be used. These details are internal, but they matter for high-throughput workloads because command reuse must stay fast while preserving connection and transaction boundaries. ## Bulk Insert Engine Generated insert methods route collection inputs through `BatchInsertEngine` where possible. Batch inserts improve throughput by: - Reusing command shape and parameters. - Reducing per-row command creation. - Keeping transaction behavior explicit. - Using provider-specific bulk paths where available. - Avoiding avoidable allocations in repeated insert loops. Use generated collection insert methods for large insert workloads: ```csharp [Dao] public interface IUserDao { [Insert] Task InsertAllAsync(IReadOnlyList users, CancellationToken cancellationToken = default); } ``` For very large server-provider imports, validate the provider-specific bulk path and transaction size in your own environment. ## QueryExtensions Performance QueryExtensions is intended for ad-hoc, reporting, and fluent query scenarios. It is still optimized, but its cost model is different from generated DAOs: - Expression-based fluent queries require parsing expression trees. - String-based fluent APIs now validate and quote identifiers in safe SQL-building paths. - Filtered includes can pass simple relation filters into relation-load SQL where possible. - Raw SQL helpers avoid DAO generation but shift SQL ownership to the caller. - `RowReader` is available when you need low-level row access and want to control materialization manually. Use generated DAOs for your core application data paths. Use QueryExtensions when flexibility, reporting, raw SQL, streaming, or include composition matters more than compile-time DAO ownership. ## Benchmark Snapshot The sample benchmark project is in: ```text samples/RoomSharpVsDapper.Benchmarks ``` The current local report was produced with BenchmarkDotNet on Windows 11, .NET 10, and an 11th Gen Intel Core i5-11300H. Representative results: | Scenario | RoomSharp | Dapper | Notes | |----------|-----------|--------|-------| | Query by id | 5.7-7.1 us | 11.3-14.5 us | RoomSharp also allocated about 1.3 KB vs 2.6 KB. | | Query range, 10 rows | 23.7 us | 32.7 us | Lower allocations in the RoomSharp path. | | Query range, 100 rows | 148.0 us | 185.4 us | Lower allocations in the RoomSharp path. | | Query range, 1000 rows | 1.68 ms | 1.75 ms | Similar timing, RoomSharp allocated less. | | Batch insert, 100 rows | 5.09 ms | 5.97 ms | RoomSharp allocated about 123 KB vs 299 KB. | | Batch insert, 1000 rows | 15.78 ms | 29.38 ms | RoomSharp allocated about 1.10 MB vs 2.96 MB. | | Batch insert, 5000 rows | 47.60 ms | 65.06 ms | RoomSharp allocated about 5.51 MB vs 14.86 MB. | | Join query, 10 rows | 48.5 us | 53.0 us | RoomSharp allocated less. | | Join query, 50 rows | 145.3 us | 124.9 us | Dapper was faster in this shape; RoomSharp allocated less. | The best reading of the benchmark is not "RoomSharp wins every row." The useful signal is that generated DAO paths are competitive, batch inserts are strong, and allocations are consistently lower in many measured paths. ## Practical Tuning ### Prefer Generated DAOs For Stable Paths For core domain queries, prefer generated DAO methods: ```csharp [Query("SELECT Id, Email, Name FROM users WHERE Email = :email")] Task FindByEmailAsync(string email, CancellationToken cancellationToken = default); ``` Generated DAOs give RoomSharp the most room to optimize command binding and materialization. ### Select Only Needed Columns ```csharp // Wider row shape. [Query("SELECT * FROM users WHERE Id = :id")] // Narrower row shape. [Query("SELECT Id, Email, Name FROM users WHERE Id = :id")] ``` Narrower projections reduce data transfer and materialization work. This matters most in range queries and joins. ### Use Indexes For Lookup Columns ```csharp [Entity(TableName = "users")] [Index(Value = ["Email"], Unique = true)] public class User { [PrimaryKey(AutoGenerate = true)] public long Id { get; set; } public required string Email { get; set; } } ``` RoomSharp can generate fast code, but the database still needs good indexes for selective queries. ### Batch Related Writes In Transactions ```csharp await db.RunInTransactionAsync(async cancellationToken => { await db.UserDao.InsertAsync(user1, cancellationToken); await db.UserDao.InsertAsync(user2, cancellationToken); }, cancellationToken); ``` Transactions reduce commit overhead and keep multi-step writes consistent. ### Choose The Right Concurrency Mode Use `[Database(ConcurrencyMode = ConcurrencyMode.Serialized)]` when you want one-at-a-time database execution with simpler connection behavior. Use `[Database(ConcurrencyMode = ConcurrencyMode.Parallel)]` when the workload benefits from concurrent sessions and your provider/database can handle it. Parallel mode is not automatically faster for every workload. Measure with your provider, schema, and deployment settings. ### Tune SQLite For App Workloads ```csharp var db = RoomDatabase.Builder() .UseSqlite("app.db") .SetJournalMode(JournalMode.WAL) .EnableMultiInstanceInvalidation() .SetAutoCloseTimeout(TimeSpan.FromMinutes(5)) .Build(); ``` WAL can improve read/write behavior for SQLite workloads. Auto-close can help desktop and mobile-style applications release idle connections. ### Use Provider Pooling For Server Databases For SQL Server, PostgreSQL, and MySQL, connection pooling is normally controlled by the provider connection string: ```csharp var db = RoomDatabase.Builder() .UseSqlServer("Server=.;Database=App;Min Pool Size=5;Max Pool Size=100;") .Build(); ``` Tune pool size based on request concurrency, transaction duration, and database limits. ## Profiling Guidance Use external and provider-supported measurement tools rather than relying on a RoomSharp-specific query executor hook. Recommended options: - Run the sample benchmarks in `samples/RoomSharpVsDapper.Benchmarks`. - Add small BenchmarkDotNet benchmarks around your own DAOs and QueryExtensions code. - Use `ILogger` through `RoomDatabase.Builder().SetLogger(...)` for lifecycle, migration, transaction, and runtime diagnostics. - Subscribe to the `RoomSharp` `ActivitySource` when using OpenTelemetry or `System.Diagnostics.Activity`. - Measure database-side query plans with your provider tools, such as SQL Server execution plans, PostgreSQL `EXPLAIN`, MySQL `EXPLAIN`, or SQLite `EXPLAIN QUERY PLAN`. Example local timing around an application operation: ```csharp var sw = Stopwatch.StartNew(); var users = await db.UserDao.GetActiveUsersAsync(cancellationToken); sw.Stop(); logger.LogInformation( "Loaded {Count} users in {ElapsedMs} ms", users.Count, sw.Elapsed.TotalMilliseconds); ``` This keeps profiling explicit and avoids changing the DAO execution path. ## Version Notes ### v0.5.4 - Transaction/session behavior was updated across runtime helpers and QueryExtensions. - Filtered include paths can push simple relation filters into relation loading. - QueryExtensions expression grouping and identifier validation were improved. - FTS SQL generation is provider-aware for MySQL and SQL Server. ### v0.5.3 and v0.5.2 - Generated DAO command ownership was simplified. - Parallel generated DAOs use session-scoped command reuse. - Serialized transaction paths normalize transaction commands and command clone cleanup. - Insert generation avoids unnecessary fallback delegate allocations where possible. - Relation loading caches metadata and validates relation definitions earlier. ### v0.5.0 - Smart entity lifecycle features added soft delete, encryption, and audit behavior to generated paths. - Generated query paths automatically apply soft-delete filtering and encrypted-property decryption where configured. ## Checklist - Use generated DAOs for stable, high-traffic data paths. - Use QueryExtensions for ad-hoc SQL, reporting, streaming, and relation include composition. - Keep frequently queried columns indexed. - Prefer explicit projections over `SELECT *` when result shape matters. - Batch multiple writes and wrap related writes in transactions. - Measure serialized vs parallel concurrency with your provider. - Use provider connection pooling for server databases. - Use BenchmarkDotNet and database query plans for serious performance decisions. ## Next Steps - [Batch Insert Engine](/docs/v0.5.4/batch-insert) - Learn about bulk operations. - [Transactions](/docs/v0.5.4/transactions) - Optimize write grouping and transaction boundaries. - [Concurrency](/docs/v0.5.4/concurrency) - Choose serialized or parallel execution. - [QueryExtensions](/docs/v0.5.4/query-extensions) - Understand fluent, raw, and relation include paths.