Data Warehouse Performance: How to Estimate Query Time

Data warehouse performance is mostly about how much data the database must read, how fast storage can deliver it, and how much work the query asks the server to do. That is the practical answer. The less practical answer is staring at a slow dashboard while the progress bar behaves like it has joined a trade union.

If a query is fast today, that does not mean it will be fast in 3 years. Data grows. Indexes age. Reports multiply. Users discover filters and then immediately stop using them. A good performance estimate helps you plan before the warehouse starts making noises like an old printer.

Start with the query workload, not the server specification

The first question is what the query has to read. A simple aggregation over a large fact table can be slower than a more complex query over a small filtered dataset.

SELECT STATE_CODE,
  PRODUCT_CODE,
  PERIOD_CODE,
  SUM(AMOUNT)
AS AMOUNT
FROM FACT_TABLE
GROUP BY STATE_CODE,
  PRODUCT_CODE,
  PERIOD_CODE

This is a full scan and aggregation. If the fact table grows every month, the query time will usually grow with it.

Estimate row size before estimating query time

A useful estimate starts with row size. In a retail star schema, fields such as STATE_CODE, PRODUCT_CODE, PERIOD_CODE, and AMOUNT may add up to roughly 45 bytes per row before database overhead.

Real storage also includes row metadata, pages, indexes, compression settings, and database-specific structures. The row is never just the row. Databases like to accessorise.

Use table size and disk speed for a rough estimate

With 10 million rows at 45 bytes each, the raw table data is roughly 450,000,000 bytes. If storage can read 50 MB per second, the optimistic read time is:

450,000,000 / (50 * 1,048,576) = 8.58 seconds

That number is useful, but too optimistic. It ignores indexes, caching, CPU work, grouping, sorting, concurrent users, and temp space.

Measure the real table size in the database

The better approach is to create a realistic test table, populate it with realistic data, count the rows, and ask the database how much space it actually uses.

In SQL Server, sp_spaceused shows reserved size, data size, and index size:

EXEC sp_spaceused 'FACT_TABLE';

Microsoft documents sp_spaceused with the exact syntax and options. Other databases have equivalent catalogue views or system functions.

Check indexes, joins, and aggregation strategy

Data warehouse performance problems often come from predictable causes: full scans, unsuitable indexes, expensive joins, wide date ranges, and reports that ask for more history than anyone reads.

For warehouse schema design, see star schema optimisation. For loading and validating warehouse data, see loading data into a data warehouse.

Use ETL automation to keep performance predictable

Performance is not only a database problem. Bad input data creates bad warehouse loads. Bad warehouse loads create bad reports. Then everyone blames the dashboard, because dashboards cannot defend themselves.

Advanced ETL Processor validates data, transforms records, loads warehouse tables, and schedules jobs automatically. It is self-hosted, so your data stays in your environment. There are $0 row transfer fees and $0 execution fees.

FAQ: data warehouse performance

How do you estimate data warehouse query time?

Estimate query time by measuring table size, expected rows scanned, storage read speed, and query execution work. Then test the same query with realistic data during quiet and busy periods.

Why do data warehouse queries get slower over time?

Queries usually slow down because fact tables grow, indexes become less effective, reports scan wider date ranges, and more users run queries at the same time.

Does row size affect data warehouse performance?

Yes. Larger rows mean more data must be read from storage and processed by the database. Even small increases matter when the fact table contains millions of rows.

Should I rely on disk speed calculations alone?

No. Disk speed calculations are useful for rough planning, but real query time also depends on indexes, joins, sorting, grouping, caching, CPU, memory, and concurrent users.

When should ETL be part of performance tuning?

ETL should be part of performance tuning when bad source data, duplicate records, poor transformations, or manual load steps create slow or unreliable warehouse loads.

If your data warehouse is getting slower every month, measure it now. Waiting until reporting day is like checking the brakes after the hill.