Fixing a slow attendance report with production data, not guesses

1. The Complaint

“The attendance download is slow.” Easy to agree with, hard to act on. Slow for whom? Which report? How slow, and how often?

The report is the one HR teams download to check attendance before payroll: every employee, every day in the range, w/ clock-in and clock-out times, shifts, leave, overtime and a long list of flags. It lived inside a PHP monolith, and for some teams a monthly download took long enough that people would start it, go do something else, and hope it was done when they came back. Sometimes it wasn’t done at all.

The temptation with a complaint like this is to open the code, find something that looks inefficient and rewrite it. I’ve learned not to trust that instinct much. Code that looks slow often isn’t the problem, and the real problem is often sitting in code that looks fine. So before touching anything I went to the data.

2. Who Is Actually Waiting

Every request to the report goes through the web application firewall, and the firewall’s logs land in ClickHouse via our OpenTelemetry pipeline (I wrote about that setup in From logs to spans). Each row has the URL, its query parameters and the response time, which is already a usage dataset if you squint. One month of it looked like this:

That table changed the problem. It wasn’t “the report is slow” anymore. It was: one business unit, about 12,000 employees, waits almost five minutes on average, and some of its downloads hit the firewall’s 60-minute timeout and never finish. Over a month that added up to roughly 16 hours of people waiting on one report.

Multiply each unit’s downloads by its average wait and the shape gets very clear:

That’s the Pareto principle almost exactly: 28% of the downloads caused 84% of the waiting. The most common case, PM’s quick daily download, wasn’t the problem at all. Making it twice as fast would’ve saved under an hour a month. Making the slowest fifth fast would save nearly all of it.

So I went after the worst cases first: the largest unit, the longest date ranges, and the downloads that ran into the timeout. Averages hide exactly these. A report with a 13-second median looks healthy until you sort by duration and read from teh bottom.

The same logs also showed how people used it. 85.5% of downloads asked for all rows at once rather than paging. 63.6% filtered by a specific sub-area and the rest asked for everything. 98.4% never touched the division filter, so that one barely mattered. Weekly and monthly ranges were common, and that’s where the waits got long. None of this needed a new tool btw, just a few GROUP BY queries over logs we were already keeping.

Then benchmarks. Usually you’d pick a few requests that seem representative and call it a day. With the usage data I didn’t have to guess. Each scenario mapped to something people actually did, weighted by how often:

• PM, one day, all rows (67% of traffic)
• KS, one day, all rows (28% of traffic, 84% of the waiting)
• KS, filtered by the most-used sub-area
• KS, filtered by employee type (the second most common filter)
• All business units, one day
• KS and PM, 7-day and 30-day ranges (the timeout risk)

Every scenario ran against the old PHP version first, then the new one w/ the same filters, one at a time so they weren’t competing. This matters more than it looks. A benchmark built from guesses optimizes for the guess. One built from prod usage optimizes for the people who actually complained.

3. Where the Time Went

Once I could reproduce the slow cases reliably, the cause wasn’t hard to find. The PHP report basically did this:

for each employee:
    for each day in the range:
        load this employee's shift for this day
        load this employee's clock-ins for this day
        load this employee's leave for this day
        ...

Classic N+1 query pattern. Each query is fast on its own, which is exactly why the code looked fine. But for the largest business unit over 30 days that’s about 12,000 employees × 30 days × 3 to 5 queries each, so around 1.5 million small queries for one download. Each one pays a network round trip plus a bit of DB work, and together they took 25 minutes.

It also explained something in the usage data. The waits didn’t grow with the number of rows the user wanted, they grew with employees × days. A one-day report for a large unit was tolerable; a monthly one was not.

The rewrite was a Go service with a different shape: ask the DB a few big questions instead of millions of small ones. Load the employees that match the filters first, since that decides which IDs everything else needs. Then load everything else (shifts, clock-ins, leave, overtime requests, holidays) for all of those employees and the whole date range at once, in parallel. Put the results into in-memory maps keyed by employee and date, and compute each employee’s day from the maps with no further queries.

The parallel part is a set of goroutines under an errgroup, so the loads run at the same time and one failure cancels the rest:

g, ctx := errgroup.WithContext(ctx)

g.Go(func() error {
    shifts, err = repo.GetShiftsByNIKs(ctx, niks, start.AddDate(0, 0, -6), end.AddDate(0, 0, 1))
    return err
})
g.Go(func() error {
    punches, err = repo.GetPunchRecords(ctx, niks, start, end)
    return err
})
g.Go(func() error {
    absences, err = repo.GetAbsencesByNIKs(ctx, niks, start, end.AddDate(0, 0, 1))
    return err
})
// ... more loads

if err := g.Wait(); err != nil {
    return err
}

The ranges are deliberately wider than the report. Night shifts that start before midnight and end after it need the day before and the day after, and some rules look back a full week. Getting these edges right is honestly most of the work in an attendance system, and it’s where the old code had quietly encoded years of business rules.

The first results were… mixed:

Monthly reports were 3.4× faster. Single-day reports were slower though. The data explained why: the new service returned results 500 employees per page, so a 12,000-person report took 25 HTTP round trips, while the usage logs had already shown that 85.5% of real downloads wanted everything in one response. So the benchmark caught a design mismatch before any user did, which is kind of the whole point of building it from real usage.

4. Following the Spans

Every step of report generation runs inside an OpenTelemetry span: GenerateAttendanceReport, then FetchAllData, then one span per load like db.GetPunchRecords, with the table name and the number of employees as attributes. Looking at the trace for a slow run, one span stood out. The clock-in query was taking way longer than the others even though it wasn’t returning more data.

It filtered clock-ins by a transaction_date column:

SELECT id, nik, transaction_date, transaction_time, ...
FROM absen
WHERE nik IN (?)
  AND transaction_date BETWEEN ? AND ?
ORDER BY nik, transaction_date, transaction_time

The table had an index on employee ID plus a dateTime timestamp column, not on transaction_date. So the DB could find each employee quickly and then had to scan every clock-in they’d ever recorded just to filter by date. The fix was small: filter on the indexed column instead, converting the date range to timestamps in the right time zone.

SELECT id, nik, dateTime, ...
FROM absen
WHERE nik IN (?)
  AND dateTime BETWEEN ? AND ?
ORDER BY nik, dateTime

Two related changes came out of the same trace. Batches of employee IDs went from 2,000 to 5,000 per query, because the indexed query could handle it. And the connection pool went from 25 to 50, since report generation runs about 20 queries at once and the trace showed loads waiting for a free connection rather than for the the database itself.

None of this would’ve been obvious from reading the code. The query looked correct, and it was correct. it was just asking the database to do far more work than it needed to, and the span made that visible in one look.

Final numbers, same scenarios, same data:

The monthly report that used to risk the 60-minute timeout now finishes in about two minutes. The old version got slower with every extra day. The new one grows almost flat, because the number of queries doesn’t depend on the number of days anymore.

5. Fast Is Worthless If It’s Wrong

An attendance report feeds payroll. A fast report that’s wrong is worse than a slow one, because people trust it. So every benchmark run also compared the two versions row by row and column by column. Every row was matched by employee and date and every column compared, w/ a numeric tolerance of ±0.01 for durations. Duplicate keys and missing rows on either side got reported, not ignored. The KS 30-day run matched 403,600 of 403,620 rows (99.995%), and across all suites more than 769,000 rows were compared.

The remaining differences didn’t get waved away either. Each one went into a category with an explanation. Some were real edge cases in the new code, e.g. night-shift end times, and got fixed one by one. Some went the other way: the old code had a bug in one employee type’s branch and the new code was the correct one. When 331 employees once differed in a single overtime column, the analysis split them into a group that was expected (employees on contract pause) and a group that was a genuine bug, and the bug was fixed.

Parity testing is also what made the migration safe. The new service went live behind the same URLs and replaced the old module piece by piece (the strangler fig pattern), with no downtime and no big-bang switch.

Looking back, none of it was clever. The firewall logs told me who was waiting before I’d read a line of code. A quarter of the downloads caused almost all the waiting. The p95 for one unit was twelve minutes while the overall average looked fine, and the last chunk of time was hiding in one unindexed filter that a trace found in minutes. Lots of small boring steps, each one decided by data instead of gut feel. Which, imo, is most of what performance work is.