One model, three different kinds of waiting

Fix the process inside the reporting model, so that refreshing it, reading a report built on it, and working on it all stop costing what they currently cost.

The scope is set by the practitioner’s own words: the primary ask is to fix the process in the model. Not to buy more capacity, not to change tool, and not to reduce what the model reports. Those appear in the comparison and are scored, but the instruction is about the work the model does to itself on every cycle.

The target, in the practitioner’s words: "not more than 5 secs, waiting for visuals (at least 80% of the time)", "5 mins refresh", "10 secs to open".

So three figures against three of the needs. A visual answers a click in five seconds or less, at least eighty per cent of the time. A refresh completes in five minutes, from an hour. The model opens in ten seconds, from ten minutes, which is the most demanding of the three by a wide margin and the one most likely to decide whether the others are reachable at all.

The eighty per cent is deliberate and it is not the same as an average. It says that one interaction in five is allowed to miss, which for somebody clicking through a report fifty times a day is ten slow interactions daily. That is a defensible trade and it is a choice rather than an oversight, and it carries one obligation: the answer reports the distribution rather than the mean, because a mean of four seconds can hide a tail that is the entire complaint.

No figure is set for the response while editing. That need stands in the brief without a number, which is honest rather than an omission, and an answer should report it against the baseline even though it is not being graded against a threshold.

How the figures may be reached is constrained by the practitioner’s other words on the target: cheapest cost for quality. Quality is held rather than traded, and two routes to the numbers are therefore closed. Speed may not be bought with capacity, and it may not be bought by removing capability, which matters here because capability has already been given up once and the current timings are what remains after that concession. An option that hits five seconds by dropping a data range has not met the target, it has changed the question.

A performance complaint arrives as a single grievance: it is slow. It is almost never one problem, and taking it apart before doing anything else is the highest value move available, because the three parts have different populations, different costs and quite possibly different causes.

Refreshing is scheduled work that nobody waits on directly. Rendering is what happens when a reader clicks something, and it is paid by the largest population. Editing is what the maintainers experience, a small group carrying a heavy individual load. They share a model and they may share nothing else.

The consequence that matters: refresh time and render time can move in opposite directions. Pre-computing more at refresh makes reports faster and refreshes slower. Simplifying the refresh can push work into query time and slow every reader down. An answer that optimises one without measuring the others has not improved the system, it has moved the cost onto somebody with less standing to complain.

This is the piece of the brief most directly transferable. A reader with an entirely different stack can take this decomposition, apply it to their own complaint, and be further forward before they have measured anything.

Seven observations, all reported from the live function, none of them yet timed. That distinction is why the last column exists. "Up to ten minutes" and "ten minutes routinely" point at different causes, and an hour that is one long operation is a different problem from an hour of short operations queueing.

Two rows deserve attention beyond their own line. Occasional failures are treated here as evidence rather than as incidents: intermittent failure in a system under memory pressure is telling you something about the cause. And the last row is the one that is easiest to miss, because it is not a delay at all.

Capability has already been given up. Some data ranges are unavailable to some roles, which means scope was narrowed at some point to keep the thing usable. Every timing in this brief is therefore what remains after a concession that no performance metric records. Any answer that narrows scope further is doing the same thing again, and the brief requires that to be declared rather than counted as a win.

Import mode against SQL Server with a small amount of spreadsheet data. Inside: summary tables holding millions of rows, and query chains containing steps and joins. More than one report is served from the one model.

Three rows are findings rather than description. A summary table with millions of rows is either summarising at a grain that removes almost nothing or is not a summary. Transformation inside the model means that work is repeated in full on every refresh rather than performed once at the source. And a model serving several audiences has to satisfy the widest requirement any of them holds, so the heaviest consumer sets the cost for everybody.

The last two rows are the ones to establish first and the ones most often skipped, and together they carry the most transferable finding in this instruction.

This model was a minor consumer, around a tenth of the platform, while a different and much larger one was the problem. That larger model was taken through exactly this kind of improvement and has performed well ever since. Meanwhile this one grew past it, and is now worse than the original problem ever was. Nothing raised a hand while that happened.

Two lessons come out of it and neither is about this model. The first is that a properly diagnosed repair holds, so the anxiety about whether a fix will stick is usually misplaced. The second is that fixing an instance does not change the practice that produced it, so the estate simply grows another one, and the absence of anything watching means it is discovered by its victims rather than by its owners. The thing worth building may not be a faster model at all. It may be the trigger that fires when any model starts climbing.

Six candidate causes, listed before any evidence is gathered rather than after, so that the answer cannot quietly settle on whichever one is cheapest to fix.

Writing the disproof next to each is the discipline that makes the list worth anything. Any hypothesis can be confirmed by somebody motivated to confirm it, particularly in a system where almost any change produces some improvement. Stating in advance what would rule each one out is the only practical defence, and it costs nothing at this stage and is impossible later.

Note that these are not mutually exclusive and the answer should not assume one winner. The likeliest outcome in a model in this condition is that two or three are true at once, with different weights against each of the three needs.

Every figure in this brief is a report. Nothing has been traced, timed or profiled. The answer closes these before it scores anything.

The effort column makes the point that this is cheap. The whole list is a few days of one person’s time against a problem costing many people a part of every day. There is no version of this decision in which measuring first is the expensive option.

The first row is the one that decides the weights, and it is the one nobody thinks to ask for. Until somebody counts how many people wait and how often, the relative importance of the three needs is an assumption, and every weight resting on it is an assumption too. It takes half a day.

Six options are in the comparison. Naming them here rather than in the answer is deliberate: choosing which alternatives you score against is the second way to rig a rubric, and unlike the weights, nobody audits it.

Do nothing, and keep paying what is currently being paid. It is in the set because it is the option actually being exercised, and because its cost has never been added up.

Add capacity. More memory, a larger tier, faster hardware. In the set because it is what somebody credible will argue for, because it is the fastest to execute, and because an answer that does not score it honestly will not be believed. It should be scored on what it fixes and on what it leaves untouched.

Repair the model in place, without changing where the processing happens.

Move the processing, so the model stops performing work on every cycle that could be performed once elsewhere.

Split the model, so that audiences with different requirements stop paying for each other.

Rebuild, treating the current model as evidence about requirements rather than as a thing to be corrected.

Two exclusions, stated rather than implied. Changing the reporting tool is excluded: the symptoms described are not properties of the tool, and the same design would reproduce them elsewhere, so it would be an expensive way of not answering the question. Reducing what the model reports is excluded as a first resort, because capability has already been given up once and doing it again is a decision for the people who lose the data rather than for a performance project.

These weights are a draft and the table says so. They are the argument rather than the arithmetic, so they belong to the practitioner, and nothing is scored until he has confirmed or changed them.

One dependency is worth naming because it is unusual. The first two weights cannot honestly be set until somebody counts the readers. They are drafted on the assumption that report readers outnumber maintainers heavily, which is normally true and is not yet established here. If the reader population turns out to be small, the first weight is wrong and the answer should say so rather than score against a number it has quietly stopped believing.

Honesty of measurement carries ten per cent and functions as a veto, because every figure in this brief is currently a report, and an improvement nobody can demonstrate on a clock is not one.

Reporting does not stop. The outputs the operation and the commissioners depend on keep being produced, on time, throughout whatever is changed.

Report layout and the insight on the page do not change. This is a performance instruction, not a redesign, and a change that forces every reader to be retrained has failed a requirement rather than met one.

No figure changes meaning without it being declared. If a redesign alters how a measure is calculated, that is a definition change, it goes through the register, and the people who rely on it are told. A performance project does not quietly restate history.

A baseline is captured before anything changes, for all three needs, timed and recorded. This constraint exists because the temptation to start fixing on the first day is very strong and the baseline is unrecoverable once the work begins.

Any capability given up is declared as a cost against the gain, with a named person agreeing to it. Scope reduction is a legitimate answer and an illegitimate way of hitting a target quietly.

Every improvement is demonstrated on a clock by somebody other than the person who made the change.

And the answer reaches a finding on every hypothesis, including those it rejects. Silence on a hypothesis is not a finding.

The first risk is why this brief opens with three needs rather than one problem. They arrive as a single complaint, one number is far easier to report than three, and the collapse back into one is the most likely way this goes wrong while looking like it went right.

The last is not technical and is the most likely to be fatal. Every summary table and every step in those chains was somebody competent solving a real problem under a deadline. An answer written as a list of defects will be resisted by the people whose knowledge it depends on, and it will be resisted quietly, which is the hardest kind to argue with.

A recorded baseline for all three needs, taken before any change: refresh duration, rendering time across the reports in use, and a fixed editing sequence, all timed.

A count of who waits and how often, because the weights depend on it and it takes half a day.

The six unknowns closed, with the evidence attached rather than summarised.

A finding on every hypothesis, including those rejected, with what proved or ruled out each one, and an explicit position on whether more than one is true at once.

A statement of cause, in one sentence, that somebody could disagree with. "A number of factors" is not a finding.

All six comparator options scored against the confirmed weights, including doing nothing and including buying capacity, each costed as a range with its basis given.

A before and after per need, reported separately, including any need that did not move. One number improving is not permitted to stand for the other two.

A sequenced plan in the order the work would be done, with the measurement that closes each stage and the point at which the approach would be abandoned if the numbers did not move.

What it costs the people doing it, in days, and what stops being delivered while it happens.

What changes about how the next model is built, and what makes that hold without anybody having to remember it. This is the part most often left out and the part that decides whether the problem returns.

And it is written so that somebody with a different stack can follow the reasoning and apply it to their own model. That is a requirement of this instruction rather than a courtesy, because the point of the exercise is to be useful to people who will never see this one.