One ADO Cursor Mode Sat Under 314 Broken Stats Pages

Stats pages were rendering empty across 314 customer sites, and every affected surface went through the same helper. We got the count the next day from an A/B on a separate ticket. Every site we sampled was broken on master and clean on the develop branch carrying the fix.

An hour in the SQL

We went to the queries first. They used GROUP BY across a large activity table, and the timeouts looked like a query-performance problem. We spent about an hour on that path. What pulled us off it was the failure itself. It was identical on every page, with no per-site variance. A query plan going bad under load doesn’t look like that. A shared wrapper doing something the queries never agreed to does.

What the shared recordset helper was doing

Most stats pages in the production app fetch their recordsets through one shared helper. It is old enough that we can’t say when it was written. It opens an ADO Recordset against the connection with adOpenKeyset and adLockReadOnly.

Keyset cursors date from a time when server-side result iteration made sense. The driver opens a cursor on the server, holds it open, and fetches rows on demand. For a simple SELECT returning a few hundred rows from a single table, that works. It’s slightly wasteful but functional.

A GROUP BY aggregate has to scan millions of activity rows and hash them into counts before it returns anything. The driver tries to open the cursor before the query has produced a first row. Past a few seconds of waiting, the ADO layer blows up and returns no data. The page gets an empty recordset and renders nothing.

The stats pages hadn’t always been broken. They worked at smaller data volumes. At the volume the site runs now, the aggregate queries take long enough that the cursor contract fails every time.

Moving to forward-only fetch helpers

Two newer helpers in the codebase carry none of the keyset baggage. One executes a query and returns a disconnected array. The other does the same with a row limit. Both open adOpenForwardOnly, fetch everything into memory, and close the connection. There’s no server-side cursor state, and no driver timeout while it waits on a half-finished aggregate.

Rewriting the old helper calls to the new ones sounds mechanical. It wasn’t, because the old helper returns an ADO Recordset object and the new ones return a plain array. That meant touching consumer code in several places. Pages that called .Fields("column_name") on the result had to move to array-index access. Pages that used .MoveNext loops had to become For loops.

None of that is conceptually hard, but one missed reference gives you a runtime error on a page that used to work. Every consumer had to be read carefully and tested.

The ninety-second timeout is a labeled band-aid

The aggregate query is still slow. A forward-only fetch doesn’t make a GROUP BY across millions of rows fast. What it changes is the failure mode: the page waits, then renders, instead of failing immediately with an empty result.

We added a 90-second command timeout on the affected queries as an explicit band-aid. The number didn’t come from profiling. It is large enough that the query usually finishes, and small enough that a runaway query doesn’t hold a connection open forever. A comment next to it carries the date and the follow-up ticket that owns the real work. That work is scoped around either a covering index on the activity table or a pre-aggregated summary table the stats pages read instead of computing on demand. Until one of those ships, the 90 seconds is the bridge. The comment says so, so the next person reading it won’t take a stopgap for a decision.

Why the helper got the fix and the pages didn’t

Some bugs get fixed at the call site. This one couldn’t be, and the 314 tells you why. Starting from “this specific stats page is slow” would have meant profiling one query, maybe adding one index, and moving on. The other 313 surfaces would still be broken. All of them trace to the same shared helper, and that is what says to fix the codepath and let the fix propagate.

We reached the right place because we looked at the error pattern before any individual query. Every affected page failed with the same symptom, and that symmetry was the clue. Once we had it, finding the helper and reading how it opened its cursor took about ten minutes.

What the data volume collected

The helper had sat in the codebase for years, doing its job well enough that nobody had a reason to revisit the cursor mode it picked at the start. It became invisible the way working infrastructure does.

It stopped working because the data around it grew. The keyset cursor contract that holds at 50,000 rows doesn’t hold at 5,000,000. The assumption baked into adOpenKeyset was always conditional on the query returning its first row before the ADO layer gave up waiting. The activity table eventually grew past the point where any aggregate over it could meet that condition.

A shared wrapper whose cursor contract assumes a fast first row is deferred outage debt. The volume collected on it on May 22, and all 314 surfaces went in the same pass.

When many pages fail with the same symptom, look for a shared component before tuning individual queries. Find the shared data-access code that was written for smaller data volumes and hasn’t been checked since. And where a stopgap like a timeout goes in, label it with a date and the ticket that owns the real fix.

dxdev LESSON · paste into your AI coding agent
LESSON: Keyset cursor debt collected by data volume
SOURCE: dxdev.com/blog/314-stats-pages-one-bad-cursor
WHAT HAPPENED: A shared recordset helper opened keyset ADO cursors on every query it served. At small data volumes the contract held. Once aggregate queries over a large activity table grew slow enough that the driver timed out waiting for the first row, every surface that used the shared helper returned an empty result set. The failure was uniform across 314 pages because the bug was in the abstraction, not in any individual query.
THE RULE: A shared data-access helper that opens a server-side cursor imposes that cursor contract on every query it wraps, including aggregates that cannot meet it at scale.
CHECK MY CODE, then report PASS / FAIL with file:line for each:
  1. Any shared query helper that opens a keyset or dynamic server-side cursor (adOpenKeyset, adOpenDynamic, CursorType=1/2, or equivalent in your ORM/driver) on queries that may include GROUP BY, ORDER BY on large tables, or window functions.
  2. Any command or query timeout shorter than the slowest realistic aggregate over your largest table, with no comment explaining how the number was derived.
  3. Any call site that receives a live cursor object and iterates it with driver-managed row fetching rather than materializing the result into an array or list before use.
THEN PRINT: a table (check, PASS/FAIL, evidence, fix) + a verdict (applies / partially / OUT_OF_SCOPE / no) + the single most important next action.