MemberJunction / MemberJunction/MJ

SQLServerDataProvider re-queries view column order + logs on every GraphQL request (5.43)

Open
#2,954 1 comment 0 reactions 0 assignees View on GitHub
Dominant language
TSQL
Stars
29
Forks
6
Avg merge
2d 1h
Merged PRs (30d)
323

Description

## Summary

`SQLServerDataProvider` re-queries `sys.columns` for **all views** and logs on **every GraphQL request**, flooding the MJAPI terminal:

```
SQLServerDataProvider: cached column order for 368 view(s) (save-capture @ResultTable alignment)
```

This is both a **noisy log** (unconditional `LogStatus` on the request hot path) and a **redundant recompute** (the view column-order map is schema-stable, so rebuilding it per request is wasted DB work + map construction).

## Root cause

- Logged in `loadViewColumnOrderCache()` — `packages/SQLServerDataProvider/src/SQLServerDataProvider.ts:463` (fallback at `:466`).
- Invoked from `SQLServerDataProvider.Config()` (`SQLServerDataProvider.ts:423`).
- `Config()` runs on a **fresh provider per request**: `createPerRequestProviders()` (`packages/MJServer/src/context.ts:641`, wired at `index.ts:1106`) does `new SQLServerDataProvider(); await Config(...)` for the read-write provider (`context.ts:652-653`) **and again** for the read-only provider (`context.ts:714-716`). So the full-view `sys.columns` query + log fire 1–2× per request and scale with traffic.

## Provenance

Upstream / 5.43, **not** a specific feature branch. Introduced by commit `4e053506d1` ("match save-capture @ResultTable to the base view's actual column order", 2026-06-24), an ancestor of `next`. `git log` shows no feature-branch changes to `packages/SQLServerDataProvider` — it arrived on feature branches via the `next` merge.

## Recommended fix

**(a) Quiet the hot-path log** — `SQLServerDataProvider.ts:463` (and `:466`): switch the unconditional `LogStatus(...)` to the verbose-gated variant already used elsewhere (e.g. the GraphQL boundary log at `context.ts:609-613`):

```ts
LogStatusEx({ message: `SQLServerDataProvider: cached column order for ${cache.size} view(s) (save-capture @ResultTable alignment)`, verboseOnly: true });
```

`LogStatusEx` / `verboseOnly` live in `packages/MJCore/src/generic/logging.ts:199` (gated by `MJ_VERBOSE`).

**(b) Don't recompute per request (the real fix)** — the view column-order map is process-stable. Hoist `_viewColumnOrderCache` to a `static` (process-level) map and early-return in `loadViewColumnOrderCache()` when already populated, so it builds once per process instead of once per request. This collapses N per-request `sys.columns` queries into one and makes the log fire once as a side effect. Alternatively, seed per-request providers from the global provider's already-built cache in `createPerRequestProviders` (`context.ts:641`).

(a) alone silences the noise; (b) is the correctness/perf fix and makes (a) moot. Both are changes against `next`.

## Key references
- `packages/SQLServerDataProvider/src/SQLServerDataProvider.ts:412-468` (Config + loadViewColumnOrderCache)
- `packages/MJServer/src/context.ts:641-668`, `index.ts:1106` (per-request provider construction)
- `packages/MJCore/src/generic/logging.ts:199` (`LogStatusEx` / `verboseOnly`)
- Introducing commit: `4e053506d1`

Contributor guide

Open the contributing guide

Research direction

Start with packages/SQLServerDataProvider/src/SQLServerDataProvider.ts:412-468, then trace provider construction in packages/MJServer/src/context.ts:641-668 and index.ts:1106. Read LogStatusEx in packages/MJCore/src/generic/logging.ts:199. Done means the view-column query and status log no longer run redundantly for each request, while verbose logging remains available.

Written by the indexing model from the issue text.

Assessment

Tech stack
graphql, sql, typescript
Domain
backend, databases, performance
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
52/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.