LTCP / Pricing Engine & ERP Core — Interview Revision Sheet¶
The one-line summary¶
Redesigned a 6000-line pricing method making 8-10 sequential DB calls per product using a Load-Transform-Compute-Persist pattern with batch context loading and NCalc compiled expressions, reducing pricing from 5s to 100ms, orders from 4s to 50ms, invoices from 3s to 50ms, and offline file generation from 10 mins to 8s.
The Before State — Know This Cold¶
What CalculateActualPrice actually was¶
- ~6000 lines, one method
- 8-10 sequential DB calls per product
- For a 25-product search: up to 250 DB calls
- Loaded ~1500 columns across those calls, used ~120
- Used
DataTablethroughout — no typed models - 70+ string replacements to build formula strings like
2*(3+4) - Spun up a full C# compilation environment per calculation via
CSharpScript.EvaluateAsync— the.EvaluateAsyncwas misleading, it was doing enormous synchronous work per call - To accommodate this in production: DB server was physically in the same rack as the application server — a hardware workaround for a software problem
The retry loop — a separate bug entirely¶
decimal? calculatedPrice = null;
int count = 0;
do {
calculatedPrice = _priceTypeFormulaService.Calculate(formulaModel);
count++;
} while (calculatedPrice == null && count <= 100)
Three distinct problems with this:
1. The legacy method returned null on exception — not on a genuinely transient condition
2. The failure was deterministic given the same input — if it threw once, it threw identically every time
3. Therefore this loop ran up to 100 times doing pure wasted work before returning the same null it would have returned on attempt 1
Nobody knew how often this was running to 100. No logging, no visibility. Completely blind. This loop was made irrelevant by the LTCP rewrite — batch operation replaces per-row calculation entirely.
Why it got this way¶
No documented domain rules. Engineers wrote defensive fetch-everything code under uncertainty — if you don't know exactly what a calculation needs, you load everything that might be needed. Over years this accumulated into an untouchable method. It was a dark cave. Nobody touched it.
The insight that unlocked the rewrite: looked at what it was actually doing vs what it appeared to be doing. It was doing three simple things: a waterfall to decide which price type to use, a router to early-exit where formula wasn't needed, and sequential fetches from product-related tables followed by in-memory computation. The complexity was in the DB call pattern, not the business logic.
The Iterative Journey — Two Phases¶
Phase 1 — Bulk load as stopgap¶
Before understanding the full shape of the problem, replaced per-product DB calls with bulk-loaded replicas:
// Before
oProduct = _productDal.Get(id)
// After (phase 1)
oProduct = productBulkRecords.Where(x => x.ProductId == id)
Result: 24s → 5s on dev/staging (remote DB), roughly 1s on prod (co-located DB). Better, but not satisfying. The Roslyn cost remained, the column bloat remained, the string replacement chain remained.
Phase 2 — Full LTCP rewrite¶
Sat down with the method, ran it in debug mode, observed exactly what columns were actually used across all its DB calls. Found: 120 columns needed out of ~1500 loaded. Wrote two clean batch queries that fetch exactly those 120 columns for the entire product batch. The full pattern:
Load: Two batch queries regardless of batch size. One for general price type data, one for special/manual price types (fetched conditionally and in parallel if applicable). Fetches exactly what Compute needs, nothing more.
Transform: Context builder takes the raw loaded data and shapes it into a lean in-memory context object the pricing calculator understands. No DB calls after this point. The context object is the only thing Compute ever sees.
Compute: Pure function. No DB awareness by design, not by convention. Takes the context object, applies business rules, produces results. Own DI boundary — designed so it could be extracted as a standalone service without touching anything else.
Persist: Batch write. No per-item round trips.
The LTCP Pattern — What Each Phase Does¶
| Phase | What happens | Key constraint |
|---|---|---|
| Load | Two batch queries for the whole batch | Fixed at 2 regardless of batch size |
| Transform | Raw data → typed in-memory context object | No DB calls |
| Compute | Business rules applied to context | No DB awareness |
| Persist | Batch write results | No per-item round trips |
Why this matters architecturally: eliminates N+1 at the architectural level, not just the query level. The compute layer is provably free of DB calls by design — it physically cannot make them because it has no access to any DB-connected service. Services are split into pure data-loading and pure computation layers, enabling independent testability.
NCalc — Why It Replaced Roslyn¶
What Roslyn (CSharpScript) was doing wrong¶
CSharpScript.EvaluateAsync spins up a full C# compilation environment per call. Every pricing calculation was compiling a small C# expression from scratch — full Roslyn pipeline, syntax tree, compilation, IL emission, execution. This is designed for scripting and interactive use, not for tight loops evaluating expressions at request time.
What NCalc does differently¶
Purpose-built expression evaluator. Not a general-purpose compiler. Takes a formula string, parses it once, compiles it to an internal representation, caches the compiled formula. Subsequent calls with the same formula string hit the cache and skip the parse/compile step entirely.
Why formula configurability made hardcoding impossible¶
Formulas come from the DB. They are configurable per price-type and client, and change without a deployment. The legacy system already required dynamic evaluation — CSharpScript was the broken implementation of a correct requirement. NCalc is a fast implementation of the same requirement. Hardcoding was never on the table.
NCalc singleton caching — current state¶
Registered as singleton in dev. Compiled formula cache persists across requests instead of rebuilding per call. Benefit: ~17ms per cache-hit call eliminated. Cache key is derived from the formula string itself — if a formula changes in DB, the new string is a different key, recompiles fresh, caches separately. Old entry for old formula string sits orphaned but harmless.
Cache growth: grows as formulas change over time, old entries never evicted. Not a problem — formula cardinality is naturally bounded by how many distinct price-type/formula combinations the business actually has. B2B system, finite and slow-changing. If it ever became a concern, a cache eviction strategy is a couple of days of work, not a redesign.
Current status: validated in dev, awaiting roll-out. Benefit is real but small (17ms on a 100ms baseline). Impact only visible after cache warms under real sustained traffic, not in short dev tests. Sequenced after higher-priority work.
The 1-vs-3 DB Call Structure¶
General price type (default path): 1 DB call. This covers the majority of products.
Special price type (specially negotiated price) or Manual price type: fetched conditionally, only when applicable. Rare configuration. Not worth widening the hot-path query for cold-path data.
If both special and manual apply: fetched in parallel, not sequentially. Max 3 calls total (1 general + 1 special + 1 manual).
Why not just fetch all three always: deliberate query-shape decision. Don't widen the hot-path query for data that's only needed on rare configurations. Keep the common path lean.
Scalability — Know the Actual Number¶
25 products: 100ms (prod)
~1000 products (40x batch): 120ms (prod)
Growth is sub-linear. The marginal cost as batch size increases comes from formula evaluation across more products (Compute phase), not from the DB layer — DB calls stay fixed at 1-3 regardless of batch size. The bottleneck moved from I/O to compute, which scales far better.
Numbers — Lock These Down¶
All prod figures (remote dev/staging DB makes legacy exponentially worse due to call count):
| Operation | Before | After |
|---|---|---|
| Pricing (prod) | 5s | 100ms |
| Pricing (dev/staging) | 24s | 300ms |
| Orders | 4s | 50ms |
| Invoices | 3s | 50ms |
| Descriptions | 2s | 50ms |
| EPOS offline file gen | 10 mins | 8s (cold start, typically ~5s) |
| EPOS cold start | 40s | 6s |
Use prod numbers as headline figures. If asked why staging shows a larger before-number: legacy's per-product call sprawl gets punished harder by network latency. Co-located prod DB masked the true cost.
Context-First Model — Applied Everywhere¶
The LTCP pattern became the base for all subsequent refactors:
ERP Core pricing and descriptions: direct application — two batch queries, in-memory context, NCalc compute, batch persist.
EPOS offline file generation: same pattern applied to product catalog across all price types. File generator was previously calling the old pricing engine once per price-type per SKU across 14,000 products — it was one of the largest hidden consumers of the unoptimized pricing path. Now uses the same batch-context approach.
Order processing and invoice generation: context-first model with async side-effect offloading. Build order context once, compute everything from it, push side effects (inventory update, notification triggers, etc.) to RabbitMQ async rather than blocking the order path.
Reuse story: "I built this pattern for pricing, then applied the same Load→Transform→Compute→Persist discipline wherever the same DB-call sprawl existed." That's a coherent narrative across the whole resume, not isolated wins.
Async Side-Effect Offloading — RabbitMQ vs Hangfire¶
RabbitMQ: events that need to be processed by other services or that other consumers need to react to. Fan-out, inter-service communication, domain events.
Hangfire: background jobs that need tracking, retry with visibility, scheduling. Single-service background work where you want a UI to monitor job state, retry failed jobs manually, schedule recurring tasks. What Hangfire gives you that raw queuing doesn't: job history, retry UI, scheduling — operational visibility on background work.
The split: use RabbitMQ where the event has external consumers or needs fan-out. Use Hangfire where it's internal background work that needs operational management.
Follow-up Questions to Be Ready For¶
"Walk me through the before and after architecture." Before: per-product sequential DB calls, Roslyn per calculation, DataTable, 70+ string replacements, 5s per pricing call. After: two batch queries regardless of batch size, typed in-memory context object, NCalc compiled expressions, compute layer with no DB awareness, batch persist. 5s → 100ms on prod.
"Why NCalc over just hardcoding the formulas?" Formulas are configurable in DB, change without deployment. Legacy already required dynamic evaluation — NCalc is a fast correct implementation of a requirement that existed from the start.
"What happens at 10x batch size?" 40x test: 100ms → 120ms. Sub-linear growth. DB calls stay fixed (1-3 regardless of batch size), marginal cost is formula evaluation in Compute phase — compute scales far better than I/O.
"What's next on this system?" NCalc singleton caching — implemented in dev, bounded ~17ms win, cache key is formula string so changes in DB naturally create new cache entries. Pending roll-out, sequenced against other work.
"What caused the 10-minute offline file generation time?" Hidden dependency on the old pricing engine. File generator called price calculation once per price-type per SKU across 14,000 products. Each call hit the old 8-10 DB calls per product path. The file generator was inheriting the full cost of the unoptimized pricing path thousands of times in a loop. LTCP eliminated this entirely.
"What was the retry loop doing?" Null-on-exception from the old method, retried up to 100 times on a deterministic failure. No backoff, no distinction between transient and permanent failure, no logging. Completely blind. Made moot by LTCP — batch operation replaces per-row calculation, the loop's execution context no longer exists.