Performance Notes - lobodava/artisan-orm GitHub Wiki

Artisan.Orm is a thin layer over SqlConnection / SqlCommand / SqlDataReader. Most performance characteristics come straight from ADO.NET; this page lists the few decisions that meaningfully affect throughput and latency in real applications.

The cache, ownership, and SQL corrections below describe the source being prepared for 4.1.0. Measure the installed version and actual workload; historical examples are not benchmark guarantees.

Connection pooling is enabled by default and normally reduces repeated connection setup costs.

Creating a repository does not itself open a connection. When a command opens one, pooling can reuse an available physical connection instead of establishing a new authenticated connection. The saving depends on network latency, authentication, server load, and pool state.

Use Pooling=False for a specific test or operational requirement, rather than copying it from a test configuration into an application by accident.

# Production
Data Source=...;Initial Catalog=...;Integrated Security=True;Encrypt=True;TrustServerCertificate=False;
# Configure a trusted server certificate; pooling defaults to True.

# Tests only — see Testing Your Repository
Data Source=...;Pooling=False;...

A repository instance uses one SqlConnection and a mutable Transaction property. A connection-string constructor owns its resources; a repository created around an external transaction borrows that transaction and its connection. Disposing the borrowing repository leaves both resources with their owner. Neither form supports concurrent use of the same repository. Treat it as a unit of work:

• In ASP.NET Core / DI containers — register Scoped, the framework disposes it at the end of each HTTP request. See Using Artisan.Orm in ASP.NET Core.
• In console / scripts — using var repo = ....
• In background workers — using var scope = _scopeFactory.CreateScope(); ....

new SqlConnection(connStr) is cheap (it's a managed object, no I/O). Don't try to "save" by sharing repositories — you trade microseconds of allocation for hours of debugging concurrency bugs.

ReadTo<T> (registered [MapperFor]) and ReadAs<T> (auto-mapping via expression trees) both use delegates for entity construction. Their preparation and lookup costs differ:

• ReadTo<T> invokes your hand-written CreateObject after mapper discovery. Discovery uses reflection, so validate trimming/AOT compatibility separately.
• ReadAs<T> builds and compiles a mapper on the first call for a given runtime type and reader shape. Later calls reuse it after identifying the shape from reader metadata. Per-row cost depends on the generated conversions and assignments.

In 4.1.0 the reader cache uses the actual Type plus ordered field names and field types. The automatic DataTable cache separately includes the actual Type, table name, and ordered column selection. Same-named types from different assemblies and names containing separators no longer collide. These are mapping caches, not query-result caches or SQL schema validation. See Auto-Mapping.

Choose the mapper for the application's contract, then measure cold and repeated calls if startup or throughput is important. A manual mapper may intentionally reshape SQL data; it does not have to mirror C# properties one-to-one.

AddParams discovers procedure parameter metadata and can reuse it. In 4.1.0 its cache identity includes server, database, procedure, connection settings, and authentication scope. SQL-password authentication can share entries for the same SQL principal and settings. Integrated or token-based authentication keeps entries within a particular connection instance; a new connection instance discovers its own metadata.

Only supported two-part schema-qualified procedure names with a known database are cached. Unqualified names, temporary procedures, and an unknown default database are discovered afresh. A cached template retains complete parameter metadata; each command receives clones, without carrying values from a previous invocation.

The public MappingManager.AddSqlParameters / GetSqlParameters string-keyed cache remains available, but AddParams no longer consumes it. Use explicit Add*Param calls when avoiding metadata discovery matters. Cache reuse does not promise automatic detection of a procedure changed after its metadata was cached. Source and regressions: MappingManager.cs, ParameterCacheTest.cs.

For a 50-user list-page query, ReadToList<T> is the right answer. For a 5-million-row export, the streaming variants avoid OOM and let downstream pipelines start working immediately.

Keep the repository and caller-created commands/readers alive through streaming enumeration. In 4.1.0, synchronous repository enumerable shortcuts retain their local command until enumeration completes or its enumerator is disposed. Dispose started enumerators promptly, including after partial consumption. A connection that the caller opened remains caller-owned. See Streaming with IAsyncEnumerable for the corresponding async lifetime and cancellation rules.

BulkCopy usually reduces per-row command overhead for large insert batches. Measure its benefit with the actual row width, indexes, network, transaction, and batch size; there is no universal row-count threshold or speedup ratio.

• Loop calling Save(row) — one round trip per row.
• BulkCopy(rows, "dbo.Table") streams a bulk operation; it is not limited to one network packet or a fixed number of round trips.

For large inserts, tune batchSize together with the transaction policy and log capacity. A batch boundary does not commit an enclosing external transaction or guarantee lock release:

conn.BulkCopy<Record>(rows, "dbo.Records",
    options:   SqlBulkCopyOptions.TableLock,
    batchSize: 5_000);

See BulkCopy for the full options.

The original guidance said:

The source and the target tables MUST have clustered indexes on their join columns.

4.1.0 correction. Indexing the join columns is useful for matching performance; Microsoft recommends suitable source and target indexes, preferably unique and a clustered target index where appropriate. These recommendations do not guarantee insertion order or freedom from deadlocks. The optimizer chooses the plan. See MERGE index guidance.

The graph save executes parent statements before child statements and translates temporary IDs through OUTPUT inserted.Id, source.Id pairs. That key mapping preserves relationships independently of row order. Do not correlate input and output rows by position. To preserve collection order, carry an explicit SortOrder through the TVP and persistent table and read with ORDER BY; a manual mapper can derive the position from the collection without requiring a model property.

For insert-only batches, INSERT ... SELECT ... ORDER BY has a documented identity-allocation guarantee, but not a physical insertion-order guarantee. It does not establish an equivalent guarantee for MERGE. See INSERT ordering limits and the graph-saving page.

Deadlocks are cycles of dependencies between concurrent operations. Reduce their likelihood with consistent object-access order, short transactions, and appropriate indexes/isolation; inspect the deadlock graph when one occurs. A bounded retry repeats the entire rolled-back transaction, with external side effects handled safely. The current sample does not implement automatic retries, and its message-based RAISERROR wrappers replace the SQL error number with 50000. A modern THROW; rethrow preserves the original number for policies that recognize error 1205. See Transactions and Microsoft's deadlock guidance.

When reading very wide rows (large varbinary(max), large nvarchar(max)), the default CommandBehavior allocates the whole row in memory at Read() time. For multi-megabyte blobs, consider:

• cmd.ExecuteReader(CommandBehavior.SequentialAccess) — streams column values as you read them, but you must read columns in order and once each.
• dr.GetStream(ord) / dr.GetTextReader(ord) — true streams over the column.

Artisan.Orm doesn't currently expose SequentialAccess as a flag on its high-level methods; for blob-streaming use RunCommand and configure cmd.ExecuteReader directly inside the lambda.

async doesn't make a single call faster. It makes the calling thread available for other work while SQL Server processes the query.

• In ASP.NET Core, prefer *Async on every read/write call. Otherwise the request thread blocks waiting for the database, and under load the thread pool exhausts itself.
• In console apps and short scripts, sync is usually fine and slightly easier to debug.

The async path adds a tiny per-call overhead (allocating a state machine, a few continuations). For SQL Server queries that take >1 ms — i.e., basically all of them — it's noise.

Most of the above is "cheap to do right from the start." Beyond those, don't guess — dotnet-trace, SQL Server Profiler, or the Activity tracing on Microsoft.Data.SqlClient will tell you where the time actually goes. Common surprises:

• The slow query is one your code calls 50 times in a loop — fix by reading once.
• Connection pool exhaustion under concurrency — usually a leaked repository instance not being disposed.
• Transactions held open across await points — promotes contention; shorten the critical section.
• Autocommit on each Save in a batch — wrap the loop in RunInTransaction.

See also:

• Using Artisan.Orm in ASP.NET Core
• BulkCopy
• Streaming with IAsyncEnumerable
• Testing Your Repository