From 18 Database Subqueries to One JSON Lookup: Dashboard Optimization in ASP.NET Web API
Author - Abdul Rahman (Bhai)
Web API
30 Articles
Table of Contents
What we gonna do?
Your dashboard fires 18 correlated subqueries against PostgreSQL on every single page load — and every user who logs in re-runs every one of them. No cache, no read model, no shortcut: just raw aggregation from scratch, every time, for every concurrent session. That is the situation this article is based on, and it is far more common than most teams admit.
This article walks through a four-layer optimization applied to a real warehouse management dashboard. Each layer solves a distinct problem on the same endpoint:
- Layer 1 — Pre-computed snapshot: Move the 18 aggregation queries from every read to a single JSONB row persisted in PostgreSQL. No new infrastructure — just the database you already have.
- Layer 2 — Source generators: Eliminate runtime reflection on every serialize/deserialize call with System.Text.Json source generators.
- Layer 3 — Format choice: Understand when Protobuf is the right tool and when it is not — and why JSON wins for browser-facing APIs.
- Layer 4 — Redis cache: Add a time-boxed cache in front of the snapshot so repeated reads within the same window never touch PostgreSQL at all.
Each layer is optional and independent — you can stop at any point. The article is structured so you can adopt exactly as much as your application needs.
Why we gonna do?
Every dashboard refresh recomputes 18 aggregations from scratch: TodaysSales, YesterdaysOrders, TotalInventoryValue, ThisMonthTopSellingItem, and 14 more. The handler that produces this looks like this:
public async Task<SummaryDto> Handle(
GetDashboardDetailsQuery request,
CancellationToken cancellationToken)
{
var sql = $@"
SELECT
(SELECT COALESCE(SUM(""BaseAmount"" - ""DiscountAmount"" - ""LoyaltyDiscountAmount""), 0)
FROM public.""Orders""
WHERE ""OrderDate"" >= '{request.StartDate:O}'::timestamptz
AND ""OrderDate"" <= '{request.EndDate:O}'::timestamptz
AND ""Active"" = true AND ""Type"" <> 'Complimentary') AS ""TodaysSales"",
(SELECT COALESCE(SUM(""BaseAmount"" - ""DiscountAmount"" - ""LoyaltyDiscountAmount""), 0)
FROM public.""Orders""
WHERE ""OrderDate"" >= '{request.StartDate.AddDays(-1):O}'::timestamptz
AND ""OrderDate"" <= '{request.EndDate.AddDays(-1):O}'::timestamptz
AND ""Active"" = true AND ""Type"" <> 'Complimentary') AS ""YesterdaysSales"",
(SELECT COUNT(*) FROM public.""Orders""
WHERE ""OrderDate"" >= '{request.StartDate:O}'::timestamptz
AND ""OrderDate"" <= '{request.EndDate:O}'::timestamptz
AND ""Active"" = true AND ""Type"" <> 'Complimentary') AS ""TodaysOrders"",
-- ... 15 more subqueries for YesterdaysOrders, ThisMonthTopSellingItem,
-- TotalCategories, TotalItems, TodaysPurchases, TotalServices,
-- TotalProducts, TotalRawMaterials, TotalItemsInStock,
-- TotalItemsNotInStock, TotalCustomers, TotalStaffs, TotalSuppliers,
-- TotalInventoryValue, TotalBranches
";
var summary = (await readDashboardSummaryRepository
.FromSqlRawAsync(sql, cancellationToken))[0];
summary.Process();
return new SummaryDto { /* map all fields */ };
}
The query re-runs even when no new orders arrived — every dashboard open, every refresh, every user session triggers the full 18-subquery execution. Each subquery is an independent table scan across Orders, OrderItems, Items, Customers, Staffs, Suppliers, and Branches: 18 separate execution paths the query planner has to evaluate and materialise on every single request.
The tradeoff is deliberate: reads become simpler and faster, but writes take on a little extra responsibility. Every write that changes a dashboard metric now updates the snapshot. That is the cost of moving work from the read path to the write path — and for a dashboard that is read far more often than data changes, it is a trade worth making.
Each layer below removes a distinct cost from this endpoint — without changing what the dashboard sees. Layer 1 eliminates the 18 subqueries with a JSONB snapshot. Layer 2 removes reflection overhead from serialization. Layer 3 explains why the wire format choice matters. Layer 4 adds a Redis cache so the database is not touched at all on the hot path.
How we gonna do?
Layer 1: Pre-Computed Snapshot in PostgreSQL
Step 1: Define the snapshot entity with a JSONB column
Store the entire aggregated dashboard result as a single JSONB column in PostgreSQL. This is more flexible than one column per metric — adding a new metric requires no schema migration, only a change to the serialized DTO. The entity is deliberately thin: it carries a freshness timestamp and the serialized payload.
public class DashboardSnapshot
{
public Guid Id { get; private set; } = Guid.NewGuid();
public DateTimeOffset SummaryDate { get; set; }
// Stores the serialized SummaryDto as PostgreSQL JSONB.
// Adding or removing metrics requires no migration — only a DTO change.
public string Data { get; set; } = "{}";
}
Step 2: Register the entity with EF Core
Configure the Data property with HasColumnType("jsonb"). That single configuration is what makes PostgreSQL store and index the column as a binary JSON document rather than plain text.
public class AppDbContext(DbContextOptions<AppDbContext> options) : DbContext(options)
{
public DbSet<DashboardSnapshot> DashboardSnapshots => Set<DashboardSnapshot>();
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<DashboardSnapshot>(entity =>
{
entity.ToTable("DashboardSnapshots");
entity.HasKey(e => e.Id);
// JSONB: schema-flexible, binary-indexed, queryable without full deserialization
entity.Property(e => e.Data).HasColumnType("jsonb").IsRequired();
});
}
}
Step 3: Populate the snapshot on first read
On the first request the snapshot table is empty, so the read-through handler triggers the aggregation inline. It runs all 18 queries, serializes the result into the Data column, and persists the row. Every request after that is a single row fetch and a deserialize.
public async Task<Result<bool, BaseError>> Handle(
SyncDashboardSnapshotCommand request,
CancellationToken cancellationToken)
{
var todaysSales = await context.Orders
.Where(o => o.Active && o.Type != OrderType.Complimentary
&& o.OrderDate >= todayStart && o.OrderDate < tomorrowStart)
.SumAsync(o => o.BaseAmount - o.DiscountAmount - o.LoyaltyDiscountAmount,
cancellationToken);
var yesterdaysSales = await context.Orders
.Where(o => o.Active && o.Type != OrderType.Complimentary
&& o.OrderDate >= yesterdayStart && o.OrderDate < todayStart)
.SumAsync(o => o.BaseAmount - o.DiscountAmount - o.LoyaltyDiscountAmount,
cancellationToken);
// ... compute remaining 16 metrics the same way
var salesImprovement = yesterdaysSales == 0 ? 0
: Math.Round((todaysSales - yesterdaysSales) / yesterdaysSales * 100, 2);
// Serialize the full DTO shape into the JSONB column
var snapshotData = new SummaryDto
{
TodaysSales = todaysSales,
YesterdaysSales = yesterdaysSales,
SalesImprovement = salesImprovement,
// ... remaining fields
SummaryDate = DateTimeOffset.UtcNow
};
var json = JsonSerializer.Serialize(snapshotData);
var existing = await context.DashboardSnapshots
.FirstOrDefaultAsync(cancellationToken);
if (existing is null)
context.DashboardSnapshots.Add(new DashboardSnapshot { SummaryDate = DateTimeOffset.UtcNow, Data = json });
else
{
existing.Data = json;
existing.SummaryDate = DateTimeOffset.UtcNow;
}
await context.SaveChangesAsync(cancellationToken);
return Result.Success<bool, BaseError>(true);
}
Step 4: The read handler with read-through
On a cache miss the read handler looks up the snapshot row. If it is not there yet — first deployment or after a data reset — it triggers the aggregation inline. This is the read-through pattern: the first caller pays the cost once and every caller after that gets the pre-computed result.
public async Task<Result<SummaryDto, BaseError>> Handle(
GetDashboardSnapshotQuery request,
CancellationToken cancellationToken)
{
var existing = await snapshotRepository.FirstOrDefaultAsync(cancellationToken);
if (existing is null)
{
// Read-through: first caller triggers aggregation and populates the snapshot
await mediator.Send(new SyncDashboardSnapshotCommand(), cancellationToken);
existing = await snapshotRepository.FirstOrDefaultAsync(cancellationToken);
if (existing is null)
return Result.Failure<SummaryDto, BaseError>(
UnexpectedError.Create("Failed to initialize dashboard snapshot."));
}
var dto = JsonSerializer.Deserialize<SummaryDto>(existing.Data)!;
return Result.Success<SummaryDto, BaseError>(dto);
}
For high-traffic deployments, pre-warm the snapshot with a BackgroundService that calls SyncDashboardSnapshotCommand at startup — this removes the first-caller latency spike entirely. The read-through remains as a fallback.
Step 5: Keep the snapshot current on writes
Rather than re-running all 18 aggregations after every write, the snapshot is updated surgically. When an order is placed, only the affected metrics change — the order count increments by one and the sales value increases by the order amount. Reads stay fast; write handlers take on a small, targeted update:
context.Orders.Add(newOrder);
await context.SaveChangesAsync(cancellationToken);
// Update only the metrics affected by this write
var snapshot = await context.DashboardSnapshots.FirstOrDefaultAsync(cancellationToken);
if (snapshot is not null)
{
var dto = JsonSerializer.Deserialize<SummaryDto>(snapshot.Data)!;
dto.TodaysOrders += 1;
dto.TodaysSales += newOrder.BaseAmount - newOrder.DiscountAmount - newOrder.LoyaltyDiscountAmount;
snapshot.Data = JsonSerializer.Serialize(dto);
snapshot.SummaryDate = DateTimeOffset.UtcNow;
await context.SaveChangesAsync(cancellationToken);
}
Step 6: The tradeoffs table
┌───────────────────────────┬──────────────────────────────┬──────────────────────────────┐
│ Aspect │ Before (18 subqueries) │ After (JSONB snapshot) │
├───────────────────────────┼──────────────────────────────┼──────────────────────────────┤
│ Dashboard read │ 18 subqueries per load │ 1 row lookup + deserialize │
│ Database work │ Every read │ Only on writes │
│ Stale data risk │ None (always live) │ Brief window possible │
│ Write complexity │ Simple │ Targeted metric update │
│ Schema columns per metric │ None needed (raw SQL) │ None needed (DTO change) │
│ Infrastructure added │ None │ None (same PostgreSQL) │
└───────────────────────────┴──────────────────────────────┴──────────────────────────────┘
Layer 2: Eliminate Reflection with Source Generators
The JSONB snapshot removed 18 database queries from the read path. The remaining cost is serialization — every JsonSerializer.Serialize in the sync command and JsonSerializer.Deserialize in the read handler use reflection at runtime to discover the shape of SummaryDto. At scale, reflection-based serialization adds CPU pressure and extra allocations, and it prevents AOT compilation.
System.Text.Json source generators solve this by generating serialization metadata at build time. The serializer no longer inspects the type on every call — the generated code is a direct, allocation-efficient path that handles the same contract without reflection.
Step 7: Declare the source-generation context
[JsonSerializable(typeof(SummaryDto))]
[JsonSourceGenerationOptions(
PropertyNamingPolicy = JsonKnownNamingPolicy.CamelCase,
GenerationMode = JsonSourceGenerationMode.Serialization | JsonSourceGenerationMode.Metadata)]
public partial class DashboardJsonContext : JsonSerializerContext { }
Pass the context wherever SummaryDto is serialized or deserialized. Two changes — one in the sync command, one in the read handler:
// SyncDashboardSnapshotCommand — serialize with generated metadata:
var json = JsonSerializer.Serialize(snapshotData, DashboardJsonContext.Default.SummaryDto);
// GetDashboardSnapshotQuery — deserialize with generated metadata:
var dto = JsonSerializer.Deserialize<SummaryDto>(
existing.Data,
DashboardJsonContext.Default.SummaryDto)!;
The contract does not change — the JSON shape, property names, and values are identical. What changes
is that the runtime no longer discovers the type on every call. This is especially valuable when the same
endpoint is called hundreds of times per minute by multiple concurrent users, and is
required for AOT-compiled deployments. For a deeper dive into source generation and
custom converters, see
Layer 3: Protobuf — Right Tool, Wrong Context Here
Protobuf produces a compact binary encoding of the same data. For the same SummaryDto payload, Protobuf would be noticeably smaller than JSON — because JSON carries property names as text, while Protobuf uses pre-agreed field numbers. For machine-to-machine communication — internal service calls, background pipelines, export jobs — that size reduction is a genuine win.
For this dashboard endpoint the answer is different. The consumer is a browser. Browsers expect JSON. Introducing Protobuf here means adding a content-negotiation layer, a binary-aware HTTP client, and a decode step on the browser side — none of which the dashboard needs. The right tool for browser-facing APIs is JSON with source generators, not binary wire formats.
Where Protobuf becomes appropriate in the same system: an internal sync job that pushes snapshot data between services, or a mobile background refresh that operates over a metered connection. If SyncDashboardSnapshotCommand were triggered by an event from a separate microservice, that event payload is a strong Protobuf candidate — not the HTTP response the browser reads.
Layer 4: Redis Cache — Stop Hitting PostgreSQL on Every Request
The JSONB snapshot is already a single-row lookup. For most applications that is fast enough. But on a dashboard opened by every user immediately after login — dozens of concurrent requests during a morning rush — even single-row reads accumulate into database pressure. A Redis cache in front of the snapshot absorbs those reads entirely.
Step 8: Register IDistributedCache with Redis
Install the Microsoft.Extensions.Caching.StackExchangeRedis NuGet package and register the distributed cache in Program.cs:
builder.Services.AddStackExchangeRedisCache(options =>
{
options.Configuration = builder.Configuration.GetConnectionString("Redis");
options.InstanceName = "dashboard:";
});
InstanceName is prepended to every key by the cache provider, so the key actually stored in Redis is dashboard:snapshot. Use this full key when inspecting entries with redis-cli or a Redis GUI.
Step 9: Cache-first read handler
Check Redis before touching PostgreSQL. On a hit, the response is serialized JSON from in-memory Redis — microseconds, not milliseconds. On a miss, fall through to the JSONB snapshot, then populate the cache for subsequent requests:
public async Task<Result<SummaryDto, BaseError>> Handle(
GetDashboardSnapshotQuery request,
CancellationToken cancellationToken)
{
const string cacheKey = "snapshot";
// 1. Try Redis first
var cached = await distributedCache.GetStringAsync(cacheKey, cancellationToken);
if (cached is not null)
{
var cachedDto = JsonSerializer.Deserialize<SummaryDto>(
cached,
DashboardJsonContext.Default.SummaryDto)!;
return Result.Success<SummaryDto, BaseError>(cachedDto);
}
// 2. Try the JSONB snapshot (with read-through on first load)
var existing = await snapshotRepository.FirstOrDefaultAsync(cancellationToken);
if (existing is null)
{
await mediator.Send(new SyncDashboardSnapshotCommand(), cancellationToken);
existing = await snapshotRepository.FirstOrDefaultAsync(cancellationToken);
if (existing is null)
return Result.Failure<SummaryDto, BaseError>(
UnexpectedError.Create("Failed to initialize dashboard snapshot."));
}
// 3. Populate Redis for subsequent requests (5-minute TTL)
await distributedCache.SetStringAsync(
cacheKey,
existing.Data,
new DistributedCacheEntryOptions
{
AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(5)
},
cancellationToken);
var dto = JsonSerializer.Deserialize<SummaryDto>(
existing.Data,
DashboardJsonContext.Default.SummaryDto)!;
return Result.Success<SummaryDto, BaseError>(dto);
}
Step 10: Invalidate on writes
The TTL handles time-based staleness automatically. For known writes — a new order, a purchase, an inventory update — delete the cache key immediately after the snapshot sync succeeds. The next read will re-populate both the snapshot and the cache from fresh data:
context.Orders.Add(newOrder);
await context.SaveChangesAsync(cancellationToken);
// Update only the affected snapshot metrics
var snapshot = await context.DashboardSnapshots.FirstOrDefaultAsync(cancellationToken);
if (snapshot is not null)
{
var dto = JsonSerializer.Deserialize<SummaryDto>(
snapshot.Data,
DashboardJsonContext.Default.SummaryDto)!;
dto.TodaysOrders += 1;
dto.TodaysSales += newOrder.BaseAmount - newOrder.DiscountAmount - newOrder.LoyaltyDiscountAmount;
snapshot.Data = JsonSerializer.Serialize(dto, DashboardJsonContext.Default.SummaryDto);
snapshot.SummaryDate = DateTimeOffset.UtcNow;
await context.SaveChangesAsync(cancellationToken);
}
// Remove the stale Redis entry — next read will rebuild it
await distributedCache.RemoveAsync("snapshot", cancellationToken);
The TTL is a safety net for cases where a write path skips invalidation — a bulk import, a migration script, a background fix. The explicit RemoveAsync on known writes ensures the dashboard is fresh within milliseconds of a change, not minutes.
To go deeper on cache expiration strategy, stampede prevention, and null-value handling, read
Step 11: The complete data flow
The diagram below shows all four layers working together — Redis cache, JSONB snapshot, read-through aggregation, and the write-plus-sync-plus-invalidation path:
┌─────────────────────────────────────────────────────────────────────────────────┐
│ GET /dashboard │
├─────────────────────────────────────────────────────────────────────────────────┤
│ │
│ ┌────────┐ ┌──────────┐ ┌──────────────────┐ ┌─────────────┐ │
│ │ Client │ │ API │ │ Redis │ │ JSONB │ │
│ └────────┘ └──────────┘ │ (5 min TTL) │ │ Snapshot │ │
│ └──────────────────┘ └─────────────┘ │
│ │
│ FAST PATH (Redis hit) │
│ ───────────────────────────────────────────────────────────────── │
│ Client ──GET /dashboard──▶ API ──GET snapshot──▶ Redis │
│ Client ◀───SummaryDto────── API ◀──HIT (JSON)─── Redis │
│ (microseconds) │
│ │
│ WARM PATH (Redis miss, snapshot hit) │
│ ───────────────────────────────────────────────────────────── │
│ Client ──GET /dashboard──▶ API ──GET snapshot──▶ Redis │
│ API ◀──────MISS─────── Redis │
│ API ──SELECT LIMIT 1──▶ JSONB Snapshot │
│ API ◀───JSONB row──── JSONB Snapshot │
│ API ──SET snapshot──▶ Redis (TTL 5 min) │
│ Client ◀───SummaryDto────── API │
│ (fast) │
│ │
│ COLD PATH (first load — read-through) │
│ ───────────────────────────────────────────────────────────── │
│ Client ──GET /dashboard──▶ API ──GET snapshot──▶ Redis │
│ API ◀──────MISS─────── Redis │
│ API ──SELECT LIMIT 1──▶ JSONB Snapshot │
│ API ◀────── null ───── JSONB Snapshot │
│ API ──18 aggregations──▶ DB (Orders/Items/…) │
│ API ──UPSERT snapshot──▶ JSONB Snapshot │
│ API ──SET snapshot──▶ Redis (TTL 5 min) │
│ Client ◀───SummaryDto────── API │
│ (one-time cost) │
│ │
│ WRITE PATH (POST /orders — keeps everything fresh) │
│ ───────────────────────────────────────────────────────────── │
│ Client ──POST /orders────▶ API ──INSERT Order──▶ DB │
│ API ──Update metrics──▶ JSONB Snapshot │
│ API ──DEL snapshot──▶ Redis │
│ │
└─────────────────────────────────────────────────────────────────────────────────┘
Summary
Four layers, one endpoint. The dashboard read path evolves from 18 correlated subqueries to a Redis hit that takes microseconds — while staying within your existing PostgreSQL database and standard .NET tooling.
- Layer 1 (Snapshot): Store the aggregated result as a JSONB column in PostgreSQL, updated on writes. No new database required. Adding a new metric to the dashboard requires no migration, only a DTO change.
- Read-through: The first caller after deployment populates the snapshot automatically. Pre-warm with a BackgroundService to eliminate the first-caller latency spike entirely. The read-through remains as a fallback.
- Layer 2 (Source generators): Declare a JsonSerializerContext for SummaryDto to eliminate runtime reflection on every serialize/deserialize call. Required for AOT; beneficial everywhere.
- Layer 3 (Protobuf): Right for internal service-to-machine communication. Wrong for browser-facing APIs — stay with JSON and source generators here.
- Layer 4 (Redis): A 5-minute TTL cache in front of the snapshot absorbs repeated reads without touching PostgreSQL. Invalidate immediately on relevant writes; rely on TTL as a safety net for writes that bypass invalidation.
The caching articles below cover the deeper edges: cache key design, stampede prevention when the
snapshot is missing under concurrent load, and safe null-value handling: