
# That Time I Fixed a Sneaky N+1 Query in Grade Calculations

## What I Was Working On

So today I was looking into a performance issue in our grade calculation system. We have this feature where students get weighted grades based on different assignment categories (like homework worth 20%, tests worth 50%, etc.). The code loops through each category, calculates the score, and applies the weight.

Sounds simple enough, right?

## The Problem

Turns out, this thing was making way too many database calls. Like, 40+ queries just to calculate one student's grade. Every time we calculated grades for a section with a bunch of assignments, it was painfully slow.

Looking at our monitoring tools, I could see the same queries repeating over and over:

```
"Hey database, give me assignments for category 1"
"Hey database, give me assignments for category 2"
"Hey database, give me assignments for category 3"
...
```

And it got worse. For each assignment, we were also asking:

```
"Is this assignment exempt?"
"Is this one exempt?"
"What about this one?"
...
```

Classic N+1. But actually, it was more like N+1 times three because there were multiple loops doing this.

## How I Found It

I started by checking Datadog/Sentry traces to see the query breakdown. The trace showed dozens of nearly identical queries - dead giveaway for N+1.

Then I dug into the code and found the culprit: a loop that was querying the database on every iteration. The tricky part was that there were actually THREE separate N+1 patterns hiding in there:

1. Fetching assignments per category (instead of all at once)
2. Counting exempt assignments per category with a join query
3. Checking if each individual assignment was exempt with `.exists?`

That last one was the sneakiest. It looked innocent - just a simple check - but it was running once for every single assignment.

## The Fix

The solution was basically: **load everything upfront, then work with it in memory**.

Instead of asking the database questions inside the loop, I:

1. Grabbed ALL the visible assignments in one query, then grouped them by category in Ruby
2. Got ALL the exempt assignment IDs in one query and threw them into a Set (for fast lookups)
3. Got ALL the assignment results in one query

Now the loop just works with data that's already in memory. No more "hey database" calls inside the loop.

### The Key Trick

The `.to_set` trick was clutch. Before, we were checking "is this assignment exempt?" by hitting the database every time. After, we just check if the ID is in a Set - which is basically instant.

```ruby
# Before (slow - database call every time)
next if assignment.results.exists?(status: 'exempt')

# After (fast - just checking a Set in memory)
next if exempt_ids.include?(assignment.id)
```

## Results

| Before | After |
|--------|-------|
| 40+ queries | 5 queries |
| Slow as heck | Actually reasonable |

## What I Learned

1. **N+1 can hide in plain sight.** That innocent-looking `.exists?` check was actually a database query running hundreds of times.

2. **Look for loops inside loops.** Each nested loop that touches the database multiplies your problem.

3. **Sets are your friend.** When you need to check "is X in this list?" a bunch of times, convert the list to a Set first. O(1) vs O(n) lookup.

4. **Load once, use many.** If you're going to use the same data multiple times, grab it all at the start. Don't keep asking the database the same questions.

5. **Check your traces.** Monitoring tools make N+1 issues obvious. If you see the same query pattern repeating, that's your sign.

## TL;DR

Grade calculations were slow because we were asking the database too many questions inside loops. Fixed it by asking all the questions upfront and then working with the answers in memory. Went from 40+ queries to 5.

---

*Sometimes the fix isn't complicated - it's just about being smarter about when you talk to the database.*


Sam Lee.

I build scalable systems.

Latest Posts

That Time I Fixed a Sneaky N+1 Query in Grade Calculations