Performance Tips

Guide to writing efficient queries and optimizing ConjureDB performance for game applications.

ConjureDB is an in-memory database designed for game clients where every microsecond matters. This guide covers practical techniques for writing fast queries, choosing the right indexes, eliminating allocations, and keeping your game running at a stable frame rate.

See also: Indexing · Compiled Queries · Portable Interpreter · PGO · Database Engine

---

Table of Contents

• Compiled Queries vs Runtime Queries
• Index Selection Guide
• Query Optimization
• Zero-Allocation Patterns
• Understanding Compiler Warnings
• PGO Workflow Summary
• Memory Management
• Unity-Specific Performance
• Common Anti-Patterns
• Benchmarking Your Queries
• Game-Specific Patterns

---

Compiled Queries vs Runtime Queries

The single most impactful performance decision is whether your queries are known at build time. Compiled (schema query) declarations are lowered into optimized C# methods during dotnet build with zero runtime parsing or planning — see Compiled Queries for the canonical reference. Queries that are not known at build time (config-delivered, server-authored) run through the portable interpreter lane described below, which executes pre-compiled, pre-verified bytecode rather than re-parsing or re-compiling on every call.

Always Use query for Production Code

// ✅ GOOD — declared in a .conjure schema, compiled at build time, zero overhead at runtime
query GetTopPlayers(minLevel: int, n: int) -> Player[] {
    from Players | filter Level > @minLevel | sort -Score | take @n
}

// Call the generated method on the entity set
Player[] top = context.Players.GetTopPlayers(minLevel, n);

Performance Comparison

Aspect	Compiled Query (AOT)	Portable Interpreter (config-delivered)
Parse + bind + optimize	Build time (zero at runtime)	Pre-compiled to bytecode — no per-call parse/bind/optimize
Index selection	Pre-computed optimal path	Baked into the bytecode (index-aware)
Allocation	Zero (NoAlloc variants)	Allocation-lean (no per-call plan build)
Best for	Build-time-known hot-path queries	Queries delivered later as content/config
Suitable for dev tools	✅ Yes	✅ Yes

Config-Delivered Queries: the Portable Interpreter Lane

There is a third case the table above does not cover: queries that are not known at build time — they ship later as content/config (LiveOps tuning, server-authored queries). You cannot compile those to C# ahead of time, and on iOS/IL2CPP you cannot JIT or emit code at runtime to handle them either. For exactly this case ConjureDB has a portable interpreter lane that executes a pre-compiled, pre-verified bytecode with no JIT, no runtime codegen, and no reflection.

The current measured lane is allocation-lean (≤ 152 B/query in the 1000/1000/500-row Players/Orders/Products vs-SQLite corpus, 0 B for a point lookup), O(1) for keyed lookups (~49 ns in the vs-SQLite harness), index-aware for secondary-index range/lookup cases, faster than SQLite on all 23 measured relational shapes, and bit-for-bit at parity with the AOT lane. It is a deliberate second lane, not a fallback for failed compilation.

Keep your build-time-known, hot-path queries in the compiled query lane (the throughput ceiling); reach for the portable lane only for genuinely config-delivered queries. See Portable Interpreter for when each lane runs and the full performance expectations.

NoAlloc Variants for Zero-Allocation Hot Paths

Every collection-returning compiled query automatically generates ...NoAlloc() and ...ForEach<TConsumer>() helper surfaces (scalar- and dictionary-returning queries do not — their result is already a value or has no pooled container). Use these in performance-critical paths where even a List<T> allocation is unacceptable:

// Standard — allocates a List<T> internally
IEnumerable<Player> GetTopPlayers(int minLevel, int n);

// NoAlloc — pooled outer result, no List<T> allocation
ConjureDB.Collections.DisposableQueryResult<Player> GetTopPlayersNoAlloc(int minLevel, int n);

// Streaming callback — lowest-allocation public surface
void GetTopPlayersForEach<TConsumer>(ref TConsumer consumer, int minLevel, int n)
    where TConsumer : struct, ConjureDB.Query.IQueryConsumer<Player>;

// Usage in a game loop — zero GC pressure in the outer result container
using var result = context.Players.GetTopPlayersNoAlloc(minLevel: 50, n: 10);
var span = result.Span;

for (int i = 0; i < span.Length; i++)
{
    ref readonly var player = ref span[i];
    RenderPlayer(player);
}

---

Index Selection Guide

Choosing the right index type is the difference between O(1) and O(n). ConjureDB provides specialized index structures for every common access pattern.

When to Use Each Index Type

Query Pattern	Recommended Index	Complexity	Why
Exact key lookup (filter GuildId == 42)	LookupIndex	O(1)	Hash-based equality, non-unique keys
Unique key lookup (filter Email == "...")	UniqueIndex	O(1)	Hash-based, enforces uniqueness constraint
Range queries (filter Price >= 10 and Price <= 50)	SortedSetIndex	O(log n + k)	Tree-based range scan
Sorted enumeration (`sort -Score	take 10`)	SortedSetIndex	O(log n)
Category + sorted (`filter GuildId == @g	sort -Score	take 10`)	GroupedSortedIndex
EXISTS range check (does order X have qty > 100?)	RangeLookupIndex	O(1)	Sparse min/max arrays
Pre-computed totals (sum Score where GuildId == @g)	AggregationIndex	O(1)	Incrementally maintained aggregates
Multiple grouped aggregates (sum, avg, count per guild)	UniversalAggregationIndex	O(1) per metric	Pre-computed grouped stats
Small, rarely-mutated sorted data	SortedListIndex	O(log n) lookup	Dense array, cache-friendly iteration

Index Overhead

Every index consumes memory and adds update cost. For per-index-type memory overhead, update-cost complexity, and the general "when NOT to index" guidance (small tables, write-heavy/read-light columns), see Indexing — that is the canonical reference.

From a query-tuning standpoint, two additional access-pattern cases argue against an index even when the canonical guidance is satisfied:

• Columns only used in projections — indexes help filters and sorts, not select.
• Low-selectivity columns (e.g., bool IsActive on a table where 95% are active) — the index returns nearly all rows; a scan is comparable.

Rule of thumb: Remove any index that no query uses — it adds memory and per-write update cost with no read benefit.

---

Query Optimization

Filter Before Join

The optimizer pushes filters below joins and reorders inner joins automatically — predicate pushdown and inner-join reordering are default, unconditional transformations that do not require PGO data. For a side-local predicate like filter Age > 18, both forms below compile to the same physical plan:

# The optimizer pushes the filter below the join...
from Users
| join Orders o (Id == o.UserId)
| filter Age > 18

# ...so this converges to the same plan
from Users
| filter Age > 18
| join Orders o (Id == o.UserId)

Writing filters early still makes intent clearer, so prefer the second form for readability — just don't expect it to change the plan.

Reading Entities: Prefer the Whole Entity Over a Projection

This is the opposite of the disk/columnar-database rule. Entities are stored as immutable structs, so reading one back as is is the cheapest possible read: the compiler hands you the stored struct by reference — a zero-copy ReadOnlySpan<T> (All().Span), a ref readonly (TryFindByIdRef / FindByIdRefUnchecked), a QueryDirectByRef enumerable, or a ForEach<TConsumer>(in T) callback — with no per-row copy or construction.

A select that returns a subset of columns is not free, and for the "give me these rows" case it is usually slower. The projected shape is not a type that exists in storage, so the compiler must materialize a new row object for every result (new SomeProjectedRow { ... } per row) — adding a construction per row and forfeiting the by-ref / zero-copy access to the stored entity.

# ✅ GOOD for reading entities — returns the stored struct by-ref, zero per-row copy
from Players
| filter GuildId == @guildId

# ⚠️ Materializes a new projected row per result — no faster (often slower) than reading the whole entity
from Players
| filter GuildId == @guildId
| select Id, Name, Score

Reach for select to shape the result contract, not to shrink it for speed:

• compute derived columns (select Name, Score * 2 as DoubleScore),
• rename or reorder columns for the caller,
• combine columns across joined tables (where there is no single stored entity to return),
• expose a deliberately narrow DTO as a public API surface.

Column pruning does still cut work for intermediate operators: a join, sort, or window that would otherwise carry wide rows only carries the columns downstream stages actually need, so projecting inside a large pipeline can help. What does not hold here is the "fewer columns → less to read → faster" intuition for the final entity read — with resident, immutable, by-ref entities there is nothing to save, and building a new row shape costs more.

Use TAKE to Limit Results

Unbounded queries can produce unexpectedly large result sets. Always use take when you only need a subset:

# ❌ BAD — sorts entire table, returns everything
from Players
| sort -Score

# ✅ GOOD — sorts and returns only top 10
from Players
| sort -Score
| take 10

When a take limit is present, the compiler can use a heap-based TopN algorithm (O(n log k) where k = limit) instead of a full O(n log n) sort. For k ≤ 64 and when all selected columns are unmanaged primitive types, the TopN heap is stack-allocated with zero GC pressure; otherwise it falls back to a list-backed heap.

Prefer Indexed Lookups Over Full Scans

The compiler selects indexes automatically when available. Ensure the columns you filter on have appropriate indexes:

// ✅ With a LookupIndex on GuildId — O(1) hash lookup
query GetGuildPlayers(guildId: int) -> Player[] {
    from Players | filter GuildId == @guildId
}

// ❌ Without an index on Name — O(n) full scan
query FindByName(name: string) -> Player[] {
    from Players | filter Name == @name
}

// Call the generated methods on the entity set
Player[] guildPlayers = context.Players.GetGuildPlayers(guildId);
Player[] byName = context.Players.FindByName(name);

If you see compiler warning UM7001 (full scan on large table), add an index on the filtered column.

Leverage Pre-Sorted Indexes

When you have a SortedSetIndex on the sort column, the compiler eliminates the sort operator entirely:

// SortedSetIndex on Score → sort is free (pre-cached ordering)
query GetTopPlayers(n: int) -> Player[] {
    from Players | sort -Score | take @n
}

// GroupedSortedIndex on (GuildId, Score) → group access + pre-sorted iteration
query GetTopGuildPlayers(g: int, n: int) -> Player[] {
    from Players | filter GuildId == @g | sort -Score | take @n
}

Use Aggregation Indexes for Metrics

Don't scan tables to compute sums and counts. Use pre-computed aggregation indexes:

// ❌ BAD — scans a guild's players to compute the sum on every call
query GetGuildTotalScore(guildId: int) -> long {
    from Players | filter GuildId == @guildId | aggregate { Total = sum Score }
}

// ✅ GOOD — per-key aggregation index on Score, grouped by GuildId, O(1) read
struct table Player(plural: Players, persistence: local) {
    GuildId: int
    Score: int

    @@index(fields: [GuildId], name: "ScoreByGuild", kind: aggregation, value: Score)
}
// O(1) per guild: context.Players.ScoreByGuild.GetSum(guildId)

For multiple metrics per group (sum + average + count together), use universal_aggregation:

// O(1) per guild — no GROUP BY scan required
struct table Player(plural: Players, persistence: local) {
    GuildId: int
    Score: int

    @@index(fields: [GuildId], name: "GuildScoreStats", kind: universal_aggregation, value: Score)
}

---

Zero-Allocation Patterns

In game loops running at 60 FPS, you have ~16.6 ms per frame. Every heap allocation adds GC pressure that eventually causes frame-rate hitches. ConjureDB provides multiple zero-allocation access patterns.

NoAlloc Query Overloads

Every collection-returning compiled query has ...NoAlloc() and ...ForEach<TConsumer>() helpers (scalar- and dictionary-returning queries do not):

// Allocating version — creates a List<T>
IEnumerable<Player> GetTopPlayers(int n);

// NoAlloc version — pooled outer result
ConjureDB.Collections.DisposableQueryResult<Player> GetTopPlayersNoAlloc(int n);

// Use the pooled span directly
using var result = context.Players.GetTopPlayersNoAlloc(n: 20);
var buf = result.Span;

for (int i = 0; i < buf.Length; i++)
{
    ref readonly var p = ref buf[i];
    // Use p.Name, p.Score, etc. — no copies
}

Ref Access (FindById, Enumeration)

Use ref readonly access to avoid copying entity structs:

// O(1) lookup returning a ref readonly — no struct copy.
// TryFindByIdRef reports via the out flag whether the id was present.
ref readonly var player = ref context.Players.TryFindByIdRef(playerId, out bool found);
// use `player` only when `found` is true

// Zero-copy enumeration over all entities
ReadOnlyMemory<T> all = context.Players.All();
var span = all.Span;
for (int i = 0; i < span.Length; i++)
{
    ref readonly var entity = ref span[i];
    // Direct field access, no allocation
}

Zero-Allocation Index Enumeration

Secondary indexes provide QueryDirect and QueryDirectByRef methods that return struct enumerables:

// Struct enumerable — no heap allocation
var directResults = context.Players.GuildId_Lookup.QueryDirect(guildId);
foreach (var player in directResults) { /* ... */ }

// By-ref struct enumerable — no copies of large structs
var refResults = context.Players.GuildId_Lookup.QueryDirectByRef(guildId);
foreach (ref readonly var player in refResults) { /* ... */ }

Value-Type Results

For scalar queries, prefer value-type returns to avoid boxing:

// Returns int directly — no boxing, no allocation
query GetMaxLevel() -> int {
    from Players | aggregate { MaxLevel = max Level }
}

// Returns long — no boxing
query GetGuildTotalScore(g: int) -> long {
    from Players | filter GuildId == @g | aggregate { Total = sum Score }
}

---

Understanding Compiler Warnings

The ConjureDB compiler produces structured warnings (UM7xxx) when it detects suboptimal query patterns. These warnings are actionable — each one points to a specific performance issue with a concrete fix.

Plan Warnings Reference

Warning	Description	Impact	How to Fix
UM7001	Full scan on large table	O(n) scan instead of indexed access	Add a LookupIndex or SortedSetIndex on the filtered column
UM7002	Cartesian product (JOIN without condition)	O(n × m) cross join	Add a join condition: join Orders o (Id == o.UserId)
UM7004	Sort without LIMIT on large result set	Full O(n log n) sort with unbounded output	Add take N to enable heap-based TopN, or add a SortedSetIndex
UM7005	Correlated subquery not decorrelated	Per-row re-evaluation, O(n × m)	Rewrite as a join or use exists with an indexed predicate
UM7007	Nested loop join on large tables	O(n × m) without index	Add a LookupIndex on the join key, or enable PGO for strategy selection
UM7008	Full sort on large dataset	O(n log n) with no limit or index	Add take N, add a SortedSetIndex, or use PGO
UM7009	Index recommendation from the Index Advisor — experimental, opt-in Info (requires enabling the experimental index-access analyzer)	Advisor detected a missing index opportunity	Review the recommendation and add the suggested index
UM7010	Semi/Anti join fell back to NestedLoop	EXISTS not fully decorrelated or no index	Add index on the correlated column or restructure the subquery
UM7011	Nested collection allocation in projection	Per-entity List<T> allocation, O(n × m)	Flatten with a join instead of nested collections
UM7013	PGO profile untrusted	Stale or low-quality profile → heuristic fallback	Recollect the profile on a representative workload
UM7014	PGO profile untrusted — behavioral strategy override (SkipSort/NoOptimize) was blocked (Info, not Warning)	Planner ignores the untrusted override and uses its default strategy	Recollect a trusted profile on a representative workload
UM7015	Correlated scalar subquery fallback	Per-row evaluation of scalar subquery	Rewrite as join + aggregation or decorrelate manually
UM7016	Heuristic planning fallback	Missing statistics or metadata	Provide PGO data or manual hints via query attributes
UM7018	PGO profile data inconsistency	Invalid key range or conflicting data in profile	Recollect the profile; check for data corruption

Join-Specific Diagnostics

Warning	Description	How to Fix
JOIN0001	No applicable join strategy for predicate	Ensure join predicate uses equality on indexed columns
JOIN0004	PGO join strategy hint unavailable	The hinted strategy doesn't exist for this join; remove or change the hint

Interpreting Warnings in Build Output

dotnet build -c Release
# warning UM7001: Full scan on table 'Players' (estimated 10,000 rows).
#   Consider adding an index on column 'Name'.
# warning UM7004: Sort on 'Score' without LIMIT.
#   Consider adding 'take N' or a SortedSetIndex on 'Score'.

Address UM7001 and UM7002 warnings first — they indicate the most severe performance issues (full scans and cartesian products).

---

PGO Workflow Summary

Profile-Guided Optimization lets the compiler make data-driven decisions instead of relying on heuristics. The four-step instrument → run → export → rebuild workflow, plus profile handling, is documented canonically in PGO; from a performance standpoint, the table below shows what PGO improves.

What PGO Improves

Decision	Without PGO	With PGO	Typical Speedup
Aggregation strategy	Hash dictionary	DenseArray (stackalloc)	3–8×
TopN implementation	Runtime branching	ConstantHeap (stackalloc)	1.5–2×
Join strategy	Heuristic guess	Index lookup (observed FK range)	2–5×
Sort operator	Always emitted	Eliminated when pre-sorted	∞ (zero cost)
Output buffer	4 resizes (grow-double)	Pre-allocated to average result count	1.2–1.5×
Packed-key width	Conservative ulong	Narrowest type for observed range	Memory savings

PGO has minimal impact on small tables (< 100 rows) and simple scan-filter-project queries where the heuristic default is already close to observed values. For per-query @planning(...) manual hints (without full PGO) and the complete hint reference, see PGO.

---

Memory Management

Capacity Pre-allocation

Set accurate initial capacities in your struct table schema declarations to avoid runtime resizing. The primary index doubles capacity when exhausted — each doubling copies the entire array:

// ❌ BAD — default capacity, resizes multiple times as players join
struct table Player(plural: Players, persistence: local) { ... }

// ✅ GOOD — pre-allocate for expected steady-state count
struct table Player(plural: Players, persistence: local, capacity: 10000) { ... }

For secondary indexes, pre-size with EnsureCapacity(maxId, valueCapacity):

context.Players.EnsureCapacity(maxId: 10_000, valueCapacity: 10_000);

Memory Budget

Set a memory budget to track pressure across all tables:

var context = DbContextBuilder<GameDbContext>.Create()
    .WithMemoryBudgetMB(256)
    .Build();

Monitoring Memory Usage

Use GetMemoryReport() and GetHealthReport() to inspect runtime memory:

var report = context.GetMemoryReport();
Console.WriteLine($"Total: {report.TotalBytes / 1024 / 1024} MB");
Console.WriteLine($"Tables: {report.TableBytes / 1024 / 1024} MB");
Console.WriteLine($"Materialized: {report.MaterializedBytes / 1024 / 1024} MB");
Console.WriteLine($"Budget: {report.BudgetBytes / 1024 / 1024} MB");
Console.WriteLine($"Pressure: {report.BudgetPressureLevel} ({report.BudgetUsageRatio:P1})");

foreach (var set in report.Sets)
{
    Console.WriteLine($"  {set.Name}: {set.EntityCount} entities, " +
                      $"{set.PrimaryIndexBytes / 1024} KB");
}

foreach (var view in report.MaterializedViews)
{
    Console.WriteLine($"  {view.Name}: {view.VisibleRowCount} rows, " +
                      $"{view.TotalBytes / 1024} KB");
}

var health = context.GetHealthReport();
Console.WriteLine($"Worker: {health.WorkerState}");
Console.WriteLine($"Entities: {health.TotalEntityCount}");
Console.WriteLine($"Memory: {health.EstimatedMemoryBytes / 1024 / 1024} MB");

---

Unity-Specific Performance

IL2CPP Considerations

ConjureDB is designed for IL2CPP compatibility. Key constraints:

• No reflection — generated code uses direct field access, not PropertyInfo.
• No dynamic — all types are statically resolved at compile time.
• No Expression<T> — queries compile to plain C# methods, not expression trees.
• AOT-safe construction — DbContextBuilder<T>.Create() is the standard path. Keep the public TContext(DbConfiguration) constructor so the builder can bind to it under IL2CPP:

var context = DbContextBuilder<GameDbContext>.Create()
    .WithDataDirectory(Application.persistentDataPath + "/db")
    .Build();

GC Pressure Reduction

The primary source of GC pauses in Unity games is frequent small allocations that accumulate in Generation 0. ConjureDB provides zero-allocation paths for every hot operation:

Operation	Allocating API	Zero-Alloc API
Query results	IEnumerable<T> return	Get...NoAlloc() / Get...ForEach<TConsumer>()
Index lookup	Query(key) → IEnumerable<T>	QueryDirect(key) → struct enumerable
Entity access	FindById(id) → copy (throws on miss)	ref readonly via TryFindByIdRef / FindByIdRefUnchecked
Full iteration	foreach on IEnumerable	All() → ReadOnlyMemory<T>.Span

Rule of thumb: In your Update() loop, use only NoAlloc query helpers and ref readonly access.

Frame Budget Management

Typical frame budgets:

Target FPS	Frame Budget	Recommended DB Budget
60 FPS	16.6 ms	≤ 1 ms for all DB operations
30 FPS	33.3 ms	≤ 3 ms for all DB operations
120 FPS	8.3 ms	≤ 0.5 ms for all DB operations

Strategies for staying within budget:

1. Pre-compute with reactive queries — let the worker thread maintain query results. Read Current in the game loop (O(1), zero allocation):

reactive query TopPlayers() -> Player[] {
    from Players | filter Level > 10 | sort -Score | take 20
}

// Cache the reactive query once — each call constructs a fresh ReactiveQuery
var topPlayers = context.Players.TopPlayers();

// In Update() — O(1) read, zero allocation
ReadOnlySpan<Player> top = topPlayers.Current;

2. Amortize writes across frames — batch mutations and commit once per frame, not per operation:

// ❌ BAD — commit per item pickup
void OnItemPickup(Item item)
{
    context.BeginTransaction();
    context.Items.Add(item);
    context.Commit();
}

// ✅ GOOD — batch mutations, commit once per frame
void LateUpdate()
{
    if (_pendingItems.Count == 0) return;

    context.BeginTransaction();
    foreach (var item in _pendingItems)
        context.Items.Add(item);
    context.Commit();
    _pendingItems.Clear();
}

Async Commit for Smooth Frames

Use CommitAsync to avoid blocking the main thread on journal flush:

// Non-blocking commit — journal flush happens in background
await context.CommitAsync(forceFlush: false);

// Blocking commit with durability guarantee (for save points)
await context.CommitAsync(forceFlush: true);

Mobile Battery Awareness

On mobile, reduce I/O when battery is low:

var context = DbContextBuilder<GameDbContext>.Create()
    .WithBatteryStatusProvider(new UnityBatteryProvider())
    .WithSnapshot(snap => snap
        .AutomaticSnapshotInterval(TimeSpan.FromMinutes(5)))
    .Build();

ConjureDB automatically defers non-critical I/O operations (periodic snapshots, journal compaction) when the battery provider reports low charge.

---

Common Anti-Patterns

Anti-Pattern	Problem	Solution
LINQ in Update() loop	Allocation per frame (iterators, closures)	Compiled query with NoAlloc() / ForEach<TConsumer>() helper
String concatenation in filters	GC pressure from temporary strings	Use @parameters in DSL queries
Unbounded queries (no take)	Memory spike, O(n log n) sort	Always add take N to limit results
Missing indexes on join keys	O(n × m) nested loop join	Add LookupIndex on foreign key columns
Row-by-row Update() in a loop	Per-call overhead; a DbSet isn't even enumerable	Use a set-based mutation (update … | where … | set …), or a batch Update(list) reading via All().Span
Commit per mutation	Worker thread overhead per commit	Batch the writes (Update(list) / Add(list)) and Commit() once per frame
Ignoring compiler warnings	Undetected full scans, cartesian products	Fix every UM7xxx warning before shipping
Over-indexing (index every column)	Wasted memory, slower writes	Only index columns used in filters/sorts/joins
Scanning for aggregates	O(n) per frame for sum/count	Use AggregationIndex or UniversalAggregationIndex
Allocating results you don't read	GC pressure from unused List<T>	Use scalar return types or NoAlloc helpers

Anti-Pattern Deep Dives

Updating Many Rows

Don't loop and update row-by-row. (Iterating a DbSet directly doesn't even compile — it is not IEnumerable<T>; read via All().Span. And a per-row Update() in a loop pays per-call overhead.)

The idiomatic way to change many rows is a declarative set-based mutation: declare it once in your schema and the engine updates every matching row in bulk — no manual enumeration, no "collection was modified" hazard, no risk of wiping unset columns:

// .conjure schema — a mutation is the recommended way to change many rows
mutation BumpHighLevelScores() -> int =
    update Player
    | where Level > 50
    | set Score = Score + 100

// One call, one bulk update; returns the number of rows changed
context.BeginTransaction();
int changed = context.Players.BumpHighLevelScores();
context.Commit();

When the new values must be computed in C#, collect them and apply a batch update in a single call (the Update(list) overload is more efficient than one Update() per row). Read via All().Span, and copy the whole entity with with so you don't zero out the columns you didn't set:

var updates = new List<Player>();
var players = context.Players.All().Span;
for (int i = 0; i < players.Length; i++)
{
    ref readonly var p = ref players[i];
    if (p.Level > 50)
        updates.Add(p with { Score = p.Score + 100 }); // copy all fields, change one
}

context.BeginTransaction();
context.Players.Update(updates); // bulk update — one call, not one per row
context.Commit();

Over-Indexing

struct table Player(plural: Players, persistence: local) {
    // ❌ BAD — indexes on columns never used in queries
    Name: string @index(name: "ByName", kind: lookup)            // Never filtered
    Email: string @index(name: "ByEmail", kind: lookup)          // Never filtered
    CreatedAt: DateTime @index(name: "ByCreatedAt", kind: sorted_set) // Never sorted

    // ✅ GOOD — only index what you query
    GuildId: int @index(name: "ByGuild", kind: lookup)           // Used in: filter GuildId == @g
    Score: int @index(name: "ByScore", kind: sorted_set)         // Used in: sort -Score | take 10
}

---

Benchmarking Your Queries

Compiler Trace Levels

To inspect the compiler's plan decisions for a declared query, run the code generator with both --dump-plan=<method> and --dump-plan-report=<path> (see Plan Dumping via CLI below). This writes a Summary-level trace describing the plan chosen for that query, for example:

query GetTopPlayers() -> Player[] {
    from Players | sort -Score | take 10
}

Internally the tracer defines four levels; the CLI report always runs at Summary. Normal and Verbose are internal levels exercised by compiler tests, not an authorable knob:

Level	What It Captures
None	No output
Summary	Stage boundaries and final decisions (the level the CLI report emits)
Normal	IR snapshots between compilation stages (internal only)
Verbose	Full pipeline trace: parsing → binding → optimization → planning → emission (internal only)

Plan Dumping via CLI

Inspect the physical plan for any compiled query:

dotnet run --project ConjureDB.CodeGen.Manual -- ./Game.Data --dump-plan=GetTopPlayers

Output shows the operator tree with cost estimates. The exact operators depend on the query text and available indexes; for the GetTopPlayers example above, expect a scan over Players plus sort/top-N planning rather than a parameterized filter unless your query actually includes one.

Scan(Players) ...
    Sort(...) ...
        TopN(...) ...

Key things to look for:

• strategy=IndexedRange — good, using an index
• strategy=FullScan — bad, scanning all rows
• Sort(strategy=NoOp) — good, sort eliminated (pre-sorted data)
• cost=0 — operator is free (eliminated by optimizer)

Measure With BenchmarkDotNet

Always measure query performance with BenchmarkDotNet — never an ad-hoc Stopwatch loop. Hand-rolled Stopwatch timing is unreliable for the sub-millisecond work these queries do: it has no warmup/JIT isolation, is polluted by GC and background noise, applies no statistical treatment, and routinely reports figures that are off by an order of magnitude. BenchmarkDotNet handles warmup, isolation, and variance for you:

[MemoryDiagnoser]
[SimpleJob(RuntimeMoniker.Net80)]
public class QueryBenchmarks
{
    private GameDbContext _context;

    [GlobalSetup]
    public void Setup()
    {
        _context = DbContextBuilder<GameDbContext>.Create()
            .WithDataDirectory("./bench_data")
            .Build();
        // Populate with representative data
    }

    [Benchmark(Baseline = true)]
    public IEnumerable<Player> TopPlayers_Allocating()
        => _context.Players.GetTopPlayers(minLevel: 10, n: 10);

    [Benchmark]
    public int TopPlayers_NoAlloc()
    {
        using var result = _context.Players.GetTopPlayersNoAlloc(minLevel: 10, n: 10);
        return result.Span.Length;
    }

    [GlobalCleanup]
    public void Cleanup() => _context.Dispose();
}

Run with:

dotnet run -c Release --project ConjureDB.Benchmarks

---

Game-Specific Patterns

Inventory System

Entity design:

struct table InventoryItem(plural: Inventory, persistence: local, capacity: 50000) {
    Id: int @id
    PlayerId: int
    Rarity: int
    Quantity: int
    ItemTemplateId: int
    EnchantLevel: int

    // Indexes declared together at the bottom read more clearly than inline field annotations
    @@index(fields: [PlayerId], name: "ByPlayer", kind: lookup)
    @@index(fields: [PlayerId], name: "ByPlayerAndRarity", kind: grouped_sorted, range: Rarity)
    @@index(fields: [PlayerId], name: "ItemCount", kind: universal_aggregation, value: Quantity)
}

Common queries:

// All items for a player — O(1) lookup
query GetPlayerItems(pid: int) -> InventoryItem[] {
    from Inventory | filter PlayerId == @pid
}

// Total item count — O(1) aggregation
query GetTotalItemCount(pid: int) -> int {
    from Inventory | filter PlayerId == @pid | aggregate { Total = sum Quantity }
}

// Rarest items first — O(1) group + O(k) iteration, pre-sorted
query GetRarestItems(pid: int, n: int) -> InventoryItem[] {
    from Inventory | filter PlayerId == @pid | sort -Rarity | take @n
}

Performance characteristics:

• Player items lookup: O(1) via the lookup index → microseconds
• Item count: O(1) via the universal_aggregation index → nanoseconds
• Rarest items: O(1) group access via the grouped_sorted index → microseconds

Leaderboard

Entity design:

struct table Player(plural: Players, persistence: local, capacity: 100000) {
    Id: int @id
    Name: string
    Score: int
    GuildId: int

    @@index(fields: [Score], name: "Score_Sorted", kind: sorted_set)
    @@index(fields: [GuildId], name: "ScoresByGuild", kind: grouped_sorted, range: Score)
    @@index(fields: [GuildId], name: "GuildTotalScore", kind: universal_aggregation, value: Score)
}

Queries:

// Global Top-N — O(n) with SortedSet pre-sorted, O(k) iteration
query GetGlobalLeaderboard(n: int) -> Player[] {
    from Players | sort -Score | take @n
}

// Guild Top-N — O(1) group + O(k), zero sorting
query GetGuildLeaderboard(g: int, n: int) -> Player[] {
    from Players | filter GuildId == @g | sort -Score | take @n
}

// Top guilds by total score — O(k) from pre-ranked aggregation
query GetTopGuilds(n: int) -> GuildRanking[]
@planning(max_group_key_value: 10000) {
    from Players | group GuildId (aggregate { Total = sum Score }) | sort -Total | take @n
}

For reactive leaderboards (auto-updating UI):

reactive query TopPlayersLive() -> Player[] {
    from Players | sort -Score | take 20
}

// Cache the reactive query once, then read Current each frame
var live = context.Players.TopPlayersLive();

// In Update() — always current, zero allocation
ReadOnlySpan<Player> top = live.Current;

Config Tables (Read-Only Patterns)

Game config (item templates, level requirements, skill trees) is loaded once and read frequently:

struct table ItemTemplate(plural: ItemTemplates, persistence: none, capacity: 5000) {
    Id: int @id
    Name: string
    Rarity: int
    BasePrice: int

    @@index(fields: [Rarity], name: "ByRarity", kind: sorted_set)
}

Optimization tips for config tables:

• Use persistence: none — no snapshot/journal overhead since data is loaded from game files.
• Set capacity exactly — no resizing since the dataset is fixed.
• Use UniqueIndex for lookups by external ID (e.g., template string ID).
• Prefer SortedListIndex over SortedSetIndex for small, never-mutated tables — dense array iteration is cache-friendlier.

// Pre-load all config in one transaction
context.BeginTransaction();
foreach (var template in LoadFromGameFiles())
    context.ItemTemplates.Add(template);
context.Commit();

// All subsequent reads are O(1) or O(log n) — zero allocation

Real-Time Multiplayer State

For multiplayer games with frequent state updates:

Transaction batching:

// ❌ BAD — commit per player update (20 commits/tick for 20 players)
foreach (var update in networkUpdates)
{
    context.BeginTransaction();
    context.Players.Update(update);
    context.Commit(); // triggers worker sync each time
}

// ✅ GOOD — one bulk update + one commit per network tick
context.BeginTransaction();
context.Players.Update(networkUpdates); // batch overload (Player[] / List<Player>) — one call, not one per row
context.Commit();                       // one worker sync for the whole batch

Delta subscriptions for state sync:

// Subscribe to changes and send only deltas to the network layer
context.Players.Subscribe((in StateChange<Player> change) =>
{
    switch (change.Type)
    {
        case ChangeType.Update:
            NetworkManager.SendDelta(change.Id, change.NewItem);
            break;
        case ChangeType.Add:
            NetworkManager.SendSpawn(change.NewItem);
            break;
        case ChangeType.Remove:
            NetworkManager.SendDespawn(change.Id);
            break;
    }
});

Use reactive queries for derived state:

// Nearby enemies — auto-maintained by worker thread
reactive query NearestEnemies() -> Enemy[] {
    from Enemies | filter IsAlive == true | sort Distance | take 10
}

// Cache the reactive query once (each call builds a fresh ReactiveQuery)
var nearest = context.Enemies.NearestEnemies();

// In Update() — current snapshot, zero allocation, O(1)
var enemies = nearest.Current;

---

Performance Checklist

Before shipping, verify every item:

• All game-loop queries use query (no runtime queries in hot paths)
• Hot-path queries use NoAlloc() / ForEach<TConsumer>() helpers
• All UM7xxx compiler warnings are resolved
• table capacity matches expected steady-state entity count
• Every filtered/sorted/joined column has an appropriate index
• No indexes on columns that are never queried
• Aggregation metrics use AggregationIndex, not full-table scans
• Mutations are batched — one Commit() per frame, not per operation
• Reactive queries are used for UI-bound data (leaderboards, HUD metrics)
• PGO profile is collected on representative workload (not synthetic data)
• Memory budget is set via WithMemoryBudgetMB()
• CommitAsync(forceFlush: false) is used for non-critical writes
• Mobile builds use WithBatteryStatusProvider()
• IL2CPP contexts keep a public TContext(DbConfiguration) constructor so DbContextBuilder<T>.Create() remains valid

---

See Also

• Indexing Reference — comprehensive index type documentation
• Compiled Queries — schema query declaration reference and patterns
• PGO — full Profile-Guided Optimization workflow
• Database Engine — DbContext, DbSet<T>, transactions, persistence
• Reactive Queries — incremental view maintenance
• Query Language — DSL syntax reference