MemberJunction / MemberJunction/MJ

BaseEngine: lazy-load heavy columns with LRU cache to reduce cold-load payload

Open
#2,554 4 comments 0 reactions 2 assignees Claimed by @AN-BC View on GitHub
enhancement important priority: low
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.