Case study 13 · Data and infrastructure
Speeding up a report without changing its queries or schema
For a supplier to large US retail chains, the largest customer's sales report went from over fifteen minutes to 0.2 seconds. No saved report had to be rebuilt.
over 15 minutes → 0.2 seconds
Largest customer's report
4 minutes → 0.1 seconds
A second report
No customer had to rebuild a single saved report
The story in 5 steps
Stack
- Amazon Redshift
- S3
- MySQL
- materialized views
01 · Context
The client supplies fashion accessories to large American retail chains, and those chains read their own sales data through a BI tool.
The client supplies fashion accessories to large American retail chains, and those chains read their own sales data through a BI tool. Two things could not be touched: the queries, because the tool generates them, and the schema, because customers have saved reports that depend on it.
The largest customer's report went from over fifteen minutes to two tenths of a second, and a second report went from four minutes to a tenth.
Nielsen Norman Group puts the limit for an uninterrupted flow of thought at 1 second and the limit of attention at 10 seconds, after which users go and do something else. Fifteen minutes is ninety times that 10-second limit, and the report now opens in under 1 second.
02 · Could not change
The report is part of what the client sells: their customers open it to see how their own products are moving.
The report is part of what the client sells: their customers open it to see how their own products are moving.
The queries belong to the BI tool, which generates them, so there was nothing to rewrite. The schema in practice belongs to the customers, because their saved reports are built on it, and changing it would mean asking every customer to rebuild their work. That ruled out both of the usual fixes for a slow report: rewriting the query and remodeling the schema.
What was left was to keep the queries and the schema exactly as they were and change the system they run against.
03 · What I did
I chose Redshift because the client was moving every service to AWS.
I chose Redshift because the client was moving every service to AWS. A warehouse in the same cloud as the rest of the system means simpler transfer, one account, one access model and one team maintaining it. Snowflake or BigQuery would have been just as capable, but they did not fit where the client's infrastructure was heading.
There was no migration in the usual sense, because existing jobs already exported data on over 200 million products from the MySQL source system to S3. My layer picks up those exports and loads them into Redshift.
I chose the distribution and sort keys from the queries the BI tool actually runs. Those keys decide which node each row lives on and the order rows sit in on disk, which no query names, so they could change while the schema stayed exactly as it was. Keys chosen from the shape of the schema alone would have made the warehouse expensive without making it fast.
I moved the heaviest reports onto materialized views with dependent views built on them, refreshed incrementally once a day. Precomputed numbers that drift from their source would be worse than slow ones, so a validation script checks that the views and the source still agree.
04 · Result
Largest customer's report: over 15 minutes → 0.2 seconds
- Largest customer's report: over 15 minutes → 0.2 seconds
- A second report: 4 minutes → 0.1 seconds
- No customer had to rebuild a single saved report
- Daily refresh, validated against the source
Below one second, customers explore the report instead of requesting it and waiting, and in practice that makes it a different product: they change a filter, compare a period and ask the next question in the same sitting.
05 · Differently
Daily refresh was right for this data, which arrives daily anyway.
Daily refresh was right for this data, which arrives daily anyway. If someone asks for fresher numbers, though, the only way to provide them is to rebuild the refresh path. I would have built the rollup so the refresh interval could change without reworking it.
Related services
The services that draw on this work.
- Cloud and DevOpsZero-downtime migrationA reporting warehouse rebuilt underneath a BI tool whose queries and schema had to stay exactly as they were. The report for the largest customer now opens in two tenths of a second, down from more than fifteen minutes, and every customer’s saved reports kept working.
- Web developmentWeb performanceA sales report in a BI tool went from over fifteen minutes to 0.2 seconds, without changing a single query or column.
- Backend, data and integrationsSQL and data warehouse optimizationA US retail supplier whose customers read their own sales through a BI tool, with the queries and the schema both off limits. The largest customer’s report went from over 15 minutes to 0.2 seconds, another from 4 minutes to 0.1 seconds, and no customer rebuilt a saved report.
- How we can workTechnical leadershipRedshift chosen for a fashion supplier’s data warehouse because the client was moving every service to AWS. That kept the warehouse in the same cloud and account as everything else, maintained by the same team.
- How we can workTechnical due diligenceA retail data warehouse where neither the queries nor the schema could be changed. The largest customer’s report dropped from more than 15 minutes to 0.2 seconds, and every saved customer report kept working unchanged.
Related writing
Posts that came out of this project.
Case study 13
Working on a similar problem? We reply within one business day.
The first call takes 30 minutes, and you’re under no obligation.