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.
Hope this saves someone a few hours of frustration! If it did, consider subscribing — I write 3 practical .NET articles every week based on real things I've actually fixed in production.