I Made Our Attendance API 75% Faster By Fixing One Stupid Mistake

Abdul Imran Faridh — Tue, 23 Jun 2026 13:21:29 +0000

Originally published at imrantech.hashnode.dev

It Started With Complaints

I work on an HRMS platform and our HR team kept telling me the attendance page was too slow. I ignored it the first couple of times — I thought maybe it was their internet connection or a slow PC. Then my manager asked me about it.

Okay fine. Time to actually look at this.

The attendance API was taking over 1 minute to return data for 6500 employees. One full minute. You could make a coffee and come back and it still wouldn't be done.

I Looked at the Wrong Place First

My first instinct was to look at the main SQL query. I had JOINs, GROUP BY, filters — surely something there was wrong.
But honestly? The query looked fine. I checked it three times.

I was confused. The SQL was clean. The database had indexes. So why was it so slow?

I almost opened a Stack Overflow tab. But then I added a simple Stopwatch log

`var sw = System.Diagnostics.Stopwatch.StartNew();

foreach (var item in data) {

item.EmpCategoryName = AppConfig.GetAppConfigDescription(item.EmpCategoryID);

}

sw.Stop(); LogError.WriteErrorLog("foreach time: " + sw.ElapsedMilliseconds + "ms");`

The log showed 45+ seconds just for the foreach loop.

Wait. What.

The Real Culprit

Here's the original code I had written:

This looked innocent. It was not.

foreach (var item in data) {

item.EmpCategoryName = AppConfig.GetAppConfigDescription(item.EmpCategoryID);

}

See the problem?

GetAppConfigDescription() was hitting the database on every single loop iteration.

If grouped data returned 150 rows — that's 150 separate database calls. Just to get category names. I was genuinely embarrassed when I realized this.

This is the N+1 problem. And it was hiding in a tiny helper method that looked completely harmless.

Here's what my full original code looked like:

public IList GetEmpAttenDanceDetails(GetNewAttenListDto obj)

{ try

{

using (var ctx = new hrms2Context())

{ var locationIds = obj.LocIds; var departmentIds = obj.DepartIds;

        var selectMonths = AppconfigtableList
            .Where(a => a.Source == "AttendanceDisplayMonth")
            .Select(o => o.Value)
            .FirstOrDefault();

        var today = DateTime.Now.Date;
        var fromMonth = today.AddMonths(-(Convert.ToInt32(selectMonths) - 1));
        var startDate = new DateTime(fromMonth.Year, fromMonth.Month, 1);
        var endDate = today;

        var data = ctx.tEmpAttendance
            .Join(ctx.tEmployees,
                a => a.EmpIoyeeID,
                b => b.ID,
                (a, b) => new { a, b })
            .Join(ctx.tLocations,
                ab => ab.b.EmpLocationID,
                d => d.ID,
                (ab, d) => new { ab, d })
            .Where(w =>
                w.d.IsActive &&
                w.ab.b.IsActive &&
                locationIds.Contains((int)w.ab.b.EmpLocationID) &&
                departmentIds.Contains((int)w.ab.b.DepartmentID) &&
                w.ab.a.AttendDate >= startDate &&
                w.ab.a.AttendDate < endDate.AddDays(1))
            .GroupBy(g => new
            {
                Year = g.ab.a.AttendDate.Year,
                Month = g.ab.a.AttendDate.Month,
                Category = g.ab.b.EmpCategoryID,
                Location = g.ab.b.EmpLocationID,
                LocationName = g.d.LocationName
            })
            .Select(s => new GetAllAttendanceDetailsDTO
            {
                AttendanceYear = s.Key.Year.ToString(),
                AttendanceMonth = new DateTime(s.Key.Year, s.Key.Month, 1)
                                    .ToString("MMMM"),
                AttendanceMonthInt = s.Key.Month,
                EmpCategoryID = s.Key.Category,
                LocationID = s.Key.Location,
                LocationName = s.Key.LocationName
            })
            .OrderByDescending(o => o.AttendanceYear)
            .ThenByDescending(o => o.AttendanceMonthInt)
            .ToList();

        // THE PROBLEM WAS HERE ALL ALONG
        foreach (var item in data)
        {
            item.EmpCategoryName = 
                AppConfig.GetAppConfigDescription(item.EmpCategoryID);
        }

        return data;
    }
}
catch (Exception ex)
{
    LogError.WriteErrorLog(
        "AttendanceNewService- GetEmpAttenDanceDetails : " + ex.Message);
    throw ex;
}

}

The main query? Perfect. The foreach at the bottom? Disaster.

The Fix Was Actually Simple

Once I understood the problem, the fix was straightforward.

Instead of calling the database on every loop iteration, I pre-fetch all category names once into a dictionary. Then inside the loop I just do a simple dictionary lookup — no database involved at all.

// Pre-fetch all category names in ONE call

var categoryIds = data .Select(d => d.EmpCategoryID) .Distinct() .ToList();

var categoryDict = AppconfigtableList .Where(a => categoryIds.Contains(a.ID)) .ToDictionary(a => a.ID, a => a.Description);

// Dictionary lookup — zero database calls

foreach (var item in data) { item.EmpCategoryName = categoryDict .GetValueOrDefault(item.EmpCategoryID ?? 0); }

That's it. Two small changes. No fancy libraries. No major refactoring.

Here's the full fixed version:

public IList GetEmpAttenDanceDetails(GetNewAttenListDto obj)

{

try

{

using (var ctx = new hrms2Context())

{

var locationIds = obj.LocIds; var departmentIds = obj.DepartIds;

        var selectMonths = AppconfigtableList
            .Where(a => a.Source == "AttendanceDisplayMonth")
            .Select(o => o.Value)
            .FirstOrDefault();

        var today = DateTime.Now.Date;
        var fromMonth = today.AddMonths(-(Convert.ToInt32(selectMonths) - 1));
        var startDate = new DateTime(fromMonth.Year, fromMonth.Month, 1);
        var endDate = today;

        var data = ctx.tEmpAttendance
            .Join(ctx.tEmployees,
                a => a.EmpIoyeeID,
                b => b.ID,
                (a, b) => new { a, b })
            .Join(ctx.tLocations,
                ab => ab.b.EmpLocationID,
                d => d.ID,
                (ab, d) => new { ab, d })
            .Where(w =>
                w.d.IsActive &&
                w.ab.b.IsActive &&
                locationIds.Contains((int)w.ab.b.EmpLocationID) &&
                departmentIds.Contains((int)w.ab.b.DepartmentID) &&
                w.ab.a.AttendDate >= startDate &&
                w.ab.a.AttendDate < endDate.AddDays(1))
            .GroupBy(g => new
            {
                Year = g.ab.a.AttendDate.Year,
                Month = g.ab.a.AttendDate.Month,
                Category = g.ab.b.EmpCategoryID,
                Location = g.ab.b.EmpLocationID,
                LocationName = g.d.LocationName
            })
            .Select(s => new GetAllAttendanceDetailsDTO
            {
                AttendanceYear = s.Key.Year.ToString(),
                AttendanceMonth = new DateTime(s.Key.Year, s.Key.Month, 1)
                                    .ToString("MMMM"),
                AttendanceMonthInt = s.Key.Month,
                EmpCategoryID = s.Key.Category,
                LocationID = s.Key.Location,
                LocationName = s.Key.LocationName
            })
            .OrderByDescending(o => o.AttendanceYear)
            .ThenByDescending(o => o.AttendanceMonthInt)
            .ToList();

        // Pre-fetch once
        var categoryIds = data
            .Select(d => d.EmpCategoryID)
            .Distinct()
            .ToList();

        var categoryDict = AppconfigtableList
            .Where(a => categoryIds.Contains(a.ID))
            .ToDictionary(a => a.ID, a => a.Description);

        // In-memory lookup
        foreach (var item in data)
        {
            item.EmpCategoryName = categoryDict
                .GetValueOrDefault(item.EmpCategoryID ?? 0);
        }

        return data;
    }
}
catch (Exception ex)
{
    LogError.WriteErrorLog(
        "AttendanceNewService- GetEmpAttenDanceDetails : " + ex.Message);
    throw ex;
}

}

Before vs After

	Before	After
Employees	6500	6500
Response Time	~1 minute	~15 seconds
DB calls in loop	150+	0
Improvement	—	75% faster

The HR team didn't even say thank you. They just stopped complaining. That felt like a win. 😄

What I Learned From This A few things I'll never forget after this:

The main query is not always the problem. I wasted time staring at perfectly fine SQL while the real issue was a tiny method call at the bottom.

Never trust what looks innocent. That one-liner GetAppConfigDescription() looked completely harmless. It wasn't.

Stopwatch logs are your best friend. Adding timing around different sections of code immediately told me where the problem was. I should have done that first.

Pre-fetch, then lookup. Any time you need external data inside a loop — fetch it all before the loop, store in a dictionary, and use .GetValueOrDefault() inside.

Wrapping Up

The N+1 problem doesn't always look like a classic SELECT inside a loop. Sometimes it hides inside a helper method that calls the database quietly in the background.

If your API is slow and you can't figure out why — add Stopwatch logs around each section. The numbers will tell you exactly where to look.