From 2 hours to 9 minutes: re-engineering an enterprise data process

This work predates Datasmarts. It is founder experience from a prior enterprise engagement, delivered as a senior software engineer on an in-house team, not a Datasmarts client engagement. It appears here because it is the clearest example of the kind of database work Datasmarts still takes on, and it is written in the first person because the database layer was my own contribution to a larger team effort.

Challenge

A critical data process took two hours to run, for a client whose business was measured in billions. Two hours is not merely slow. It sets the floor on how often the process can run at all, which sets the floor on how fresh the data downstream of it can ever be, and it turns any failure into a two-hour retry.

Approach

I went after the query layer rather than the data model. On a system already in production at that scale, changing the schema means coordinating a migration across everything that depends on it, and the cost of that coordination is usually larger than the cost of the slow process. The query layer, by contrast, can be rewritten and validated against the existing data with no downstream contract change at all.

That constraint is what made the work tractable: the gain had to come from how the data was being asked for, not from how it was stored.

Solution

I re-engineered the complex SQL queries at the heart of the process, optimizing the database layer in place. The process kept the same inputs, the same outputs and the same schema. What changed was the work the database had to do to produce them.

Results

The critical data process went from 2 hours to 9 minutes. Same data, same result, same schema, and a runtime that no longer dictated how the rest of the system was scheduled.

What made it hard

The constraints were the difficulty. This was a production system for a multi-billion dollar client, so the process could not be taken offline to experiment on, and the data model could not move. Every improvement had to be provably equivalent to what it replaced, because a faster process that returns a slightly different answer is not an improvement, it is a defect with better performance numbers.

One thing I will not do here is reconstruct the diagnosis. My notes from the time record the constraint, the work and the result, not the sequence of hypotheses that got me there, and a plausible-sounding reconstruction would be fiction. The measured facts are the two runtimes.