MemberJunction / MemberJunction/MJ
BaseEngine: lazy-load heavy columns with LRU cache to reduce cold-load payload
- Dominant language
- TSQL
- Stars
- 29
- Forks
- 6
- Avg merge
- 2d 1h
- Merged PRs (30d)
- 323
Description
## Problem
`ComponentMetadataEngine` loads **150 MB** of data into the browser on every page refresh, causing a **~20s cold-load delay** for only **656 rows** across 5 entities. The vast majority of this payload comes from `nvarchar(MAX)` columns on `MJ: Components` that are rarely needed by the engine's primary consumers:
| Column | Size | Needed for index/lookup? |
|--------|------|--------------------------|
| `Specification` | 108 MB | No — full JSON specs with source code |
| `FunctionalRequirementsVector` | 18 MB | No — embedding vectors |
| `TechnicalDesignVector` | 18 MB | No — embedding vectors |
| `TechnicalDesign` | 3.6 MB | No — prose descriptions |
| `FunctionalRequirements` | 2.1 MB | No — prose descriptions |
| `Description` | 0.2 MB | Useful for display |
| **Name + metadata columns** | **~0.03 MB** | **Yes** |
Without heavy columns, the load would be **~0.2 MB instead of 150 MB** — a ~750x reduction. The ~20s cold load would likely drop to under 1s.
### Root cause
The engine uses `ResultType: 'entity_object'`, which forces `ProviderBase.PreRunView()` to override `Fields` with ALL entity fields. There's no way to selectively exclude columns. Every consumer pays the cost of the heaviest consumer's needs.
### Impact
- **Every page refresh** in MJExplorer triggers a ~20s cold load before any component can render
- **150 MB held in browser memory** — most of it never accessed
- The pattern affects any engine loading entities with large text/blob columns
- Skip-Brain's `SkipComponentEngine` inherits this via composition — server-side it's tolerable but still wasteful
### Who accesses the heavy columns
**In MJ (browser-side):**
- `component-manager.ts` reads `Specification` when loading **local** (non-registry) components — but only for the single component being loaded, not all 593
- `component-registry-service.ts` reads `Specification` when resolving local component specs
- Vector columns are **not accessed anywhere** in MJ
**In Skip-Brain (server-side):**
- `SkipComponentEngine` reads `Specification`, `FunctionalRequirements`, `TechnicalDesign` for spec parsing
- Reads vector columns for similarity search in `GetQualityComponents()`
## Proposed Solution: Lightweight bulk load + LRU cache for full records
### Core pattern (applicable to any BaseEngine subclass)
1. **Engine bulk load uses a lightweight projection** — only loads core/index columns needed for lookups, filtering, and display (ID, Name, Namespace, Type, Status, etc.)
2. **Heavy columns loaded on-demand** — when a caller needs the full record (e.g., `Specification`), it's fetched individually and stored in an **LRU cache**
3. **LRU cache evicts** old full records to keep memory bounded — only recently-accessed components keep their heavy data in memory
### For ComponentMetadataEngine specifically
**Lightweight load (always cached, ~0.2 MB):**
- ID, Name, Namespace, Version, Type, Status, Title, Description
- SourceRegistryID, SourceRegistry, LastSyncedAt, ReplicatedAt
- HasCustomProps, HasRequiredCustomProps, HasCustomEvents, RequiresData, DependencyCount
**On-demand via LRU cache:**
- Specification (108 MB total, but only 1-5 components accessed at a time)
- FunctionalRequirements, TechnicalDesign
- FunctionalRequirementsVector, TechnicalDesignVector
### API change
```typescript
// Current — always returns full entity with all columns
const component = engine.FindComponent(name, namespace, registry);
const spec = component.Specification; // Already loaded (150 MB cost)
// Proposed — lightweight by default, full record on demand
const component = engine.FindComponent(name, namespace, registry);
// component.Specification is null (not loaded)
const fullComponent = await engine.GetFullComponent(component.ID); // LRU cache hit or DB fetch
const spec = fullComponent.Specification; // Only this one record fetched
```
### BaseEngine-level support
This pattern could be generalized in `BaseEngine` so any engine can declare:
```typescript
const c: Partial[] = [{
Type: 'entity',
EntityName: 'MJ: Components',
PropertyName: "_components",
CacheLocal: true,
// New options:
ExcludeFields: ['Specification', 'FunctionalRequirements', 'TechnicalDesign',
'FunctionalRequirementsVector', 'TechnicalDesignVector'],
OnDemandCacheSize: 50 // LRU cache for full records
}];
```
This would benefit any entity with large text/blob columns (Templates with `TemplateText`, AI Prompts with prompt bodies, etc.).
## Affected code paths requiring changes
### MJ
- `packages/MJCoreEntities/src/engines/component-metadata.ts` — engine config + new LRU cache + `GetFullComponent()` method
- `packages/MJCore/src/generic/baseEngine.ts` — optional `ExcludeFields` / `OnDemandCacheSize` support
- `packages/MJCore/src/generic/providerBase.ts` — support `Fields` with `ResultType: 'entity_object'` (or use `simple` + selective full-record fetch)
- `packages/React/runtime/src/component-manager/component-manager.ts` — use `GetFullComponent()` instead of reading `.Specification` from cached array
- `packages/React/runtime/src/registry/component-registry-service.ts` — same
### Skip-Brain
- `packages/shared/src/components/SkipComponentEngine.ts` — adapt to new engine API; may want to pre-warm LRU cache for vector operations or use its own full-load strategy server-side
## Alternatives considered
1. **SQL view excluding heavy columns** — works but creates entity schema divergence and doesn't generalize
2. **Two separate engine configs (light/full)** — requires callers to know which to use; LRU is transparent
3. **Strip only vector columns (quick win)** — saves 36 MB immediately but doesn't address the 108 MB Specification column
## Telemetry context
Measured via Chrome DevTools Network tab and MJ's built-in TelemetryManager (Admin > Diagnostics > Performance):
- `RunViewsWithCacheCheckQuery` batch: 11.8 MB response, **20.46s total** — this single GraphQL request batches multiple engines' RunView calls; the server doesn't respond until all queries in the batch complete, so the slowest query (ComponentMetadataEngine's unfiltered load of 150 MB across 5 entities) gates the entire response
- ComponentMetadataEngine telemetry event: 12.8s for 656 rows, 5 entities, 0 cache hits — this measures the client-side processing portion; the full round-trip including serialization, network transfer of 11.8 MB, and deserialization accounts for the 20.46s total
- `GetRegistryComponent` (separate issue): 12s for a single component fetch from Skip registry
Contributor guide
Assessment
This issue has not been assessed yet.