---
title: "Performance Notes"
description: "Understanding RoomSharp''s performance optimizations"
canonical: "https://roomsharp.dev/docs/v0.5.3/performance"
source: "src/content/v0.5.3/performance.mdx"
---

# Performance Notes

Understanding RoomSharp's performance optimizations.

## Core Performance Features

RoomSharp is designed for maximum performance through several key innovations:

### 1. Zero Reflection

All database operations use source-generated code. **No runtime reflection, no IL weaving** - just pure, compiled C#.

- Entity mapping generated at compile-time
- DAO implementations fully generated
- No dynamic method invocation

### 2. IL-Based Mapping (ILFactory)

Version 0.4.0+ introduced high-performance IL code generation:

- **Zero boxing** for value types
- **Direct property setters** (no reflection)
- **Span-based fast paths** for arrays and collections
- **Type-safe reading** with `ReaderKind`

### 3. Batch Insert Engine

Delivers **2X-5X faster** performance for bulk operations:

- Zero allocations in hot loops
- Prepared statement reuse
- Optimized column name arrays (`static readonly ImmutableArray`)
- Fast paths for different collection types

## Benchmark Results

See detailed benchmarks on the Benchmarks page. Key highlights:

| Operation | RoomSharp | Dapper | Improvement |
|-----------|-----------|--------|-------------|
| Single Insert | ~0.05ms | ~0.06ms | ~15% faster |
| Single Query | ~0.03ms | ~0.035ms | ~14% faster |
| Batch Insert (1000) | ~18ms | ~35ms | ~48% faster |
| Batch Insert (5000) | ~82ms | ~170ms | ~52% faster |

## Memory Efficiency

### Minimal Allocations

- Static column name arrays prevent repeated allocations
- Prepared statements reused across operations
- Value types mapped without boxing
- Span-based reading for collections

### Auto-Close Connections

```csharp
var db = RoomDatabase.Builder<AppDatabaseImpl>()
    .UseSqlite("app.db")
    .SetAutoCloseTimeout(TimeSpan.FromMinutes(5))
    .Build();
// Connection auto-closes after 5 minutes idle
// Reopens transparently on next use
```

## Optimization Techniques

### 1. Use Async Methods

```csharp
// ❌ Blocks thread
var user = db.UserDao.GetById(123);

// ✅ Async, non-blocking
var user = await db.UserDao.GetByIdAsync(123);
```

### 2. Batch Operations

```csharp
// ❌ Slow: Individual inserts
foreach (var user in users)
    db.UserDao.Insert(user);

// ✅ Fast: Batch insert
db.UserDao.InsertAll(users);
```

### 3. Use Transactions

```csharp
// ❌ Multiple disk writes
db.UserDao.Insert(user1);
db.UserDao.Insert(user2);

// ✅ Single transaction, one commit
await db.RunInTransactionAsync(async () => {
    await db.UserDao.InsertAsync(user1);
    await db.UserDao.InsertAsync(user2);
});
```

### 4. Optimize Queries

```csharp
// ❌ Returns all columns
[Query("SELECT * FROM users WHERE id = :id")]

// ✅ Only needed columns
[Query("SELECT id, name FROM users WHERE id = :id")]

// ✅ Use indexes
[Index(Value = ["email"], Unique = true)]
[Query("SELECT * FROM users WHERE email = :email")]
```

### 5. Connection Pooling

For server applications with SQL Server/PostgreSQL/MySQL:

```csharp
// Connection string with pooling
var db = RoomDatabase.Builder<AppDatabaseImpl>()
    .UseSqlServer("Server=...;Min Pool Size=5;Max Pool Size=100;")
    .Build();
```

## Performance Improvements by Version

### Version 0.4.1 (Latest)

- High-performance BatchInsertEngine (2-5X faster)
- Zero allocations in hot loops
- Prepared statement reuse
- DAO generator routes collections to BatchInsertEngine automatically

### Version 0.4.0

- IL-based mappers (no boxing, no allocations)
- Span-driven fast paths
- ReaderKind for typed reads
- Dialect-aware boolean handling
- Normalized column name resolution

## Profiling

### Custom Query Executor

Implement `IQueryExecutor` to add profiling:

```csharp
public class ProfilingQueryExecutor : IQueryExecutor
{
    private readonly ILogger _logger;

    public ProfilingQueryExecutor(ILogger logger)
    {
        _logger = logger;
    }

    public T ExecuteQuery<T>(IDbCommand cmd, Func<IDbCommand, T> handler)
    {
        var sw = Stopwatch.StartNew();
        try
        {
            return handler(cmd);
        }
        finally
        {
            sw.Stop();
            _logger.LogInformation(
                "Query executed in {ElapsedMs}ms: {CommandText}",
                sw.ElapsedMilliseconds,
                cmd.CommandText
            );
        }
    }
}

var db = RoomDatabase.Builder<AppDatabaseImpl>()
    .UseSqlite("app.db")
    .SetQueryExecutor(new ProfilingQueryExecutor(logger))
    .Build();
```

## Best Practices Summary

- ✅ Use async methods for all I/O operations
- ✅ Use batch operations for multiple inserts/updates
- ✅ Wrap multiple operations in transactions
- ✅ Add indexes on frequently queried columns
- ✅ Select only needed columns
- ✅ Use connection pooling for server apps
- ✅ Enable WAL mode for SQLite
- ✅ Profile with custom query executor

## Next Steps

- [Batch Insert Engine](/docs/v0.5.3/batch-insert) - Learn about bulk operations
- [Transactions](/docs/v0.5.3/transactions) - Optimize with transactions