SQL and data warehouse optimization · Assessment
Faster warehouse reports without touching queries or schema
A written review for reports that take minutes when the queries and schema can’t change. One retail supplier’s report went from over 15 minutes to 0.2 seconds.
FormatWritten assessment
TimelineDate agreed up front
PricingFixed-price assessment · hourly work
Proof3 case studies
The assessment in 4 parts
01 · Signs you need it
If two of these apply, this is a good fit.
- A report your customers open takes minutes to load
- Your BI tool generates the queries, so there is nothing for you to rewrite
- Customers have saved reports built on the schema, so the schema can’t change either
- Someone has suggested moving to another warehouse, but it isn’t clear whether that would help
- You have precomputed tables, and nobody is sure they still match the source data
02 · What you get
What the document covers:
- Where the time goes, measured on the queries your system runs and the reports people wait for.
- What can change underneath the queries and the schema: keys, indexes, materialized views and how often they refresh.
- How to keep precomputed data correct: a check against the source, and a refresh interval that matches how often new data arrives.
- Which changes would not pay off yet, with the reason for each.
03 · How we decide
STEP 01Queries and schema stay as they are.Your customers’ saved reports depend on both, so all the work happens in the layer the queries read from.
STEP 02Keys come from the query history.Distribution and sort keys are chosen from the queries the system actually runs. Keys picked from the schema alone can leave you with a warehouse that is expensive and still slow.
STEP 03Every precomputed view is checked.A fast view that has drifted from its source gives customers wrong numbers, which is worse than a slow report. Each view gets a check that it still agrees with the data it was built from.
STEP 04The refresh interval can change later.Daily refresh is often right until someone asks for fresher numbers. We build the refresh so its interval can change without rebuilding it.
04 · If we find nothing
You still get the measurements of where the time goes and the reasons the current setup can stay as it is for now.
Proof
Where we’ve done this before
Case study 13 · Data15 min → 0.2 sA 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.Read the case study →Case study 04 · MarketplaceZero outagesWhen we took over a marketplace whose hosting couldn’t keep up, we indexed its twenty slowest queries in the first two months and made them 90% faster.Read the case study →Case study 05 · Marketplace2M / dayFor advertisers on a US marketplace, the yearly report opens in under a second even with over 900 listings, because it reads only the daily rollups.Read the case study →
FAQ
Questions about this service
We set the date once we have seen the query history and the list of slow reports, and write it into the offer.
Read access to the warehouse or database, its query history, and a list of the reports people wait for.
Usually not. The retail supplier’s warehouse was picked because the rest of its system was moving to AWS rather than for its features. The speedup came from the keys and materialized views built in it.
No. The schema stays as it is, so saved reports keep working. None of the retail supplier’s customers had to rebuild one.
Yes. The keys, views and checks the document recommends are development work, which we bill by the hour.
Next step
Describe the problem and we’ll come back with three options.
The first call takes 30 minutes, and you’re under no obligation.