Self-service reporting without hurting your production database: replicas, views, indexes and timeouts

By Razi Syed. Sample code: github.com/dotnetreport/dotnetreport

You ship self-service reporting, people love it, and three weeks later someone in ops asks why checkout got slow every weekday at 9 a.m. The answer is usually a finance user running "all orders, all time, grouped by customer" against the same database your app writes to, then exporting it to Excel. Twice.

Ad hoc reporting changes who writes your queries. Before, every query against production went through code review. Now the person composing a query is a user clicking columns together, and they have no idea which table has 40 million rows. You can't review those queries, so you have to make the environment safe for them instead.

This post is the checklist I use, with the specifics of how Dotnet Report (which I work on — disclosure) talks to your database, because you can't tune a workload you don't understand. Most of it applies to any reporting tool, including one you build yourself.

First, know what a report actually runs

When a user opens a report on screen, the engine in Dotnet Report's DotNetReportApiController does two things per page:

-- 1. a total for the pager
SELECT COUNT(*) FROM ( <the report query, ORDER BY stripped> ) AS countQry

-- 2. the page itself
<the report query> ORDER BY <sort> OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY

(PostgreSQL and MySQL get LIMIT ... OFFSET ... instead.) So paging the screen is cheap if the query underneath is cheap — but the count still has to touch every matching row.

Three other paths are heavier, and they're the ones that hurt:

• Exports — Excel, PDF, Word, CSV and XML all call the same GetDataTable helper, which runs the full report query unpaged.
• Scheduled emails run those same exports at fixed times, often many at once (I covered the scheduler in Scheduled PDF and Excel report emails in ASP.NET Core with Quartz.NET).
• Stored-procedure reports (EXEC ... / CALL ...) can't be wrapped in OFFSET/FETCH, so the whole result set comes back and is paged in memory.

One more detail worth knowing: SQL Server won't do OFFSET/FETCH without an ORDER BY, so when a (non-distinct) report has no sort the engine adds one — ORDER BY NEWID() on SQL Server, RANDOM() on PostgreSQL. On a big table that's a full sort just to show page one. Giving the report a default sort on an indexed column is a free win.

1. Point reporting at a read replica

The single biggest lever is to stop running reports on the primary at all. Reporting is read-only and tolerates a few seconds of lag; that's exactly what replicas are for.

In Dotnet Report the data connection is configured on the /dotnetsetup screen, and it has two modes: Build (enter server, database and credentials) or Key (name a connection string from your app's configuration). Use Key, and give reporting its own entry:

"ConnectionStrings": {
  "AppDb": "Server=sql-primary;Database=Shop;User ID=app;Password=...;",
  "ReportingReplica": "Server=sql-ag-listener;Database=Shop;ApplicationIntent=ReadOnly;User ID=reporting_ro;Password=...;"
}

ApplicationIntent=ReadOnly is the standard SQL Server switch that routes a connection through an Always On availability group listener (or Azure SQL read scale-out) to a readable secondary. On PostgreSQL or MySQL, just point the host at the replica.

Two things to know about how this is stored. The key is resolved through DotNetReportHelper.GetConnectionString(key), which reads the host's IConfiguration (the API controller calls DotNetReportHelper.UseConfiguration(configuration)), so the value can come from environment variables or a secrets provider rather than a file. But when you save the connection on /dotnetsetup, the resolved string is written into appsettings.dotnetreport.json under the dotNetReport section. So: keep that file out of source control, and re-save the connection if the string changes.

If you can't have a replica yet, a separate reporting database fed by your existing ETL, CDC or log shipping gets you most of the same isolation.

2. Use a read-only login, even on the replica

Give reporting its own principal with read access and nothing else:

-- SQL Server
CREATE LOGIN reporting_ro WITH PASSWORD = '<strong password>';
CREATE USER reporting_ro FOR LOGIN reporting_ro;
ALTER ROLE db_datareader ADD MEMBER reporting_ro;

Dotnet Report already refuses anything but a single read statement for custom SQL tables — IsReadOnlySelectSql requires SELECT/WITH, rejects multiple statements, sp_/xp_ procedures and keywords like INSERT, UPDATE, EXEC and INTO. Treat that as one layer. The database permission is the layer that holds even if something upstream is wrong.

A dedicated login also makes reporting visible: you can find its sessions, its expensive queries and its waits in one filter instead of guessing which traffic came from the report builder.

3. Give users views, not raw tables

Users composing queries will join things you'd never join and filter on columns you never indexed. Narrow what they can reach. In the setup screen you can expose database views alongside tables, and I'd lean heavily on them:

CREATE VIEW rpt.OrderSummary AS
SELECT  o.OrderId, o.TenantId, o.OrderDate, o.Status,
        c.CustomerName, r.RegionName,
        o.TotalAmount
FROM    dbo.Orders o
JOIN    dbo.Customers c ON c.CustomerId = o.CustomerId
JOIN    dbo.Regions   r ON r.RegionId   = c.RegionId;

A view like this does three things: it pre-writes the joins correctly (so nobody builds a fan-out join that multiplies rows), it hides the 30 audit and JSON columns nobody should be grouping by, and it gives you one place to tune. I wrote more about choosing and naming what to expose in Curating a reporting schema for self-service users.

For the heavy dashboard numbers — monthly revenue by region, daily active accounts — pre-aggregate. On SQL Server that can be an indexed view; on PostgreSQL a materialized view you refresh on a schedule:

-- PostgreSQL
CREATE MATERIALIZED VIEW rpt.monthly_revenue AS
SELECT tenant_id, date_trunc('month', order_date) AS month, region_id,
       SUM(total_amount) AS revenue, COUNT(*) AS orders
FROM   orders
GROUP  BY tenant_id, date_trunc('month', order_date), region_id;

CREATE UNIQUE INDEX ON rpt.monthly_revenue (tenant_id, month, region_id);

-- later, from a job:
REFRESH MATERIALIZED VIEW CONCURRENTLY rpt.monthly_revenue;

(CONCURRENTLY needs that unique index, and it lets readers keep querying during the refresh.) A chart that used to scan millions of order rows now reads a few hundred.

4. Index for the filters you know will be there

You can't predict every ad hoc query, but you can predict the filters that are on every query.

In a multi-tenant app the obvious one is the tenant column: the DataFilters you return from GetSettings() are appended to every report query for row-level security (details in Multi-tenant reporting in ASP.NET Core: row-level security your users can't bypass). The second is almost always a date range. So the composite index that pays for itself is usually tenant first, then date:

CREATE INDEX IX_Orders_Tenant_Date
    ON dbo.Orders (TenantId, OrderDate)
    INCLUDE (Status, TotalAmount, CustomerId);

Then look at what users actually run. On the replica, the reporting login's top queries by CPU and reads (Query Store on SQL Server, pg_stat_statements on PostgreSQL) will tell you within a week which two or three reports need a view or an index. Encourage report authors to add a default date filter and a default sort; both turn "scan everything" into "seek a range".

5. Put a ceiling on every query

Some query will still be awful. Decide in advance how long it's allowed to be awful.

In the SQL Server data path, SqlServerDatabaseConnection.ExecuteQuery sets the command timeout explicitly:

using (SqlCommand command = new SqlCommand(sql.Replace("{FROM}", "FROM"), conn))
{
    command.CommandTimeout = 60 * 5;   // 5 minutes
    ...
}

That code lives in your project (Models/DotNetReportModel.cs), so if five minutes is too generous for your primary — or too short for a nightly export from a warehouse — change it there. The other providers (Npgsql, MySQL, Oracle) don't set one in that method, so they use their driver defaults.

I like a second ceiling on the database side, tied to the reporting login, so it holds no matter which code path issued the query:

-- PostgreSQL: every statement from the reporting role is capped
ALTER ROLE reporting_ro SET statement_timeout = '120s';

On SQL Server, Resource Governor (where your edition supports it) can classify the reporting login into its own pool with capped CPU and memory grants.

Lookups are already bounded: the dropdown values for filters are fetched with SELECT DISTINCT TOP 500, converted per database.

6. Spread out the scheduled load

Everyone picks "Monday 8:00". If you have a few hundred scheduled reports, that's a few hundred unpaged exports in the same minute. Schedules use the same data connection as everything else, so once reporting points at the replica they land there too. Then nudge people off the top of the hour: suggest odd minutes, and stagger heavy dashboards across an early-morning window. Your on-call self will thank you.

The short version

• Know the workload: on-screen runs are a count plus a page; exports, schedules and stored procedures pull back everything the query matches.
• Run reports on a replica through a dedicated read-only login.
• Expose views, pre-join and pre-aggregate the heavy stuff.
• Index tenant + date, then let real usage tell you the rest.
• Cap query time in code and in the database.

None of this is exotic. It's the same discipline you'd apply to any analytical workload — the difference with self-service is that you have to put it in place before users start clicking, because you won't be reviewing their queries afterwards. If you haven't set up the report builder yet, start with Add self-service ad hoc reporting to an ASP.NET Core app.

---

Razi Syed builds Dotnet Report, an embedded self-service reporting platform for .NET whose report-builder front-end is source-available on GitHub. He writes about adding reporting and analytics to SaaS products without rebuilding them from scratch.