Eliminating N+1 Queries Based on Performance Test Results

Eliminating N+1 Queries Based on Performance Test Results

- Improving Read Performance in the Banjang Note Recruiting Domain -

1. Introduction

The recruiting domain in Banjang Note calls several APIs together to render a single screen. As features are added, the number of requests and queries can grow. This quarter, a test engineer ran a read-heavy load test in the lab environment (approximately 2.7 million seed records, pods with 2 vCPU/2 Gi, 2 replicas). Based on the test results, I improved read performance in the recruiting domain.

I did not run the load test myself. So rather than covering the test methodology, this post explains what issues I identified in the report and what I changed in the actual code. The information is based on figures from the test report and the related commits.

2. What the Test Results Revealed

Overall, the test results were good. At around 110 req/s, the average HTTP response time was 80ms, p95 was 331ms, and the failure rate was 0%. CPU usage also had plenty of headroom. However, when I broke the results down by screen and API, there were some noticeable differences in response times.

Screen

Count

Average (ms)

p95 (ms)

Foreman home page

1,461

514

632

Team assignment

524

352

413

Application status

522

347

408

Recruiting list/details

1,001

195

452

Recruiting management list (team lead)

482

85

164

Table 1. Sample response times by screen (source: First Performance Test Report)

Looking at the results by API, the query for my applications had the longest average response time at 345ms. The average response time for the second-slowest API was 134ms, a difference of about 2.6 times. This API was shared by the foreman home, recruiting list/details, application status, and my team screens. Its total call count was also 2,901, matching the sum across screens. Since several screens used this API, I thought improving it could benefit multiple screens. The test report also identified it as a priority for improvement.

However, before the test, I had already started looking at the recruiting management list. I'll start by explaining what I changed there.

3. What I Fixed Before the Test: The Recruiting Management List

In the scenario shared by the test engineer before the test, it said that “10 recruiting posts generate 12 requests, and the server reads every application for each post” on the recruiting management list. After reading this, I recalled the relevant code and made a fix before waiting for the test results. There were two issues: the screen called a count API for each recruiting post, and the server queried applications for each post.

Server — replace fetching all applications for each recruiting post with a single group by based on the current page

// Before
jobPostings.map(p -> new JobPostingLdrRdo(p, jobApplicationLogic.findByJobPostingId(p.getId())));

// After
Map<String, Map<JobApplicationStatus, Long>> countsByPosting =
        countApplicationsByPostingIds(jobPostings.getContent().stream().map(JobPosting::getId).toList());
jobPostings.map(p -> new JobPostingLdrRdo(p, countsByPosting.getOrDefault(p.getId(), Map.of())));

Screen — remove useQueries, which called the count API once for each recruiting post

// Before
const applicantCountQueries = useQueries({
  queries: jobPosts.map((job) =>
    findJobApplicationCountsByJobPostingLdrOptions({ jobPostingId: job.jobPostId })),
});

// After — 목록 응답이 철회 제외 지원자 수를 함께 내려준다
applicantCount: job.activeApplicationCount ?? job.totalApplicationCount ?? 0,

Along with this, I added four indexes for columns used in sorting with Flyway, and changed the team name (team list) lookup attached to recruiting cards so that results were reused for 30 seconds instead of being fetched repeatedly on every scroll. The number of requests on the initial screen dropped from 12 to 2. In the test results, the recruiting management list had an average response time of 85ms, so it did not appear to be a major bottleneck. However, with no measurements from before the changes, I couldn't calculate the improvement rate. Around the same time, I also replaced the full my applications list lookup on the recruiting details and application submission screens—which was only used to check whether the user had applied—with the applied value in the details response.

4. Improvements After the Test: My Applications and the Foreman Home

4-1. Why Was It Slow?

When fetching my applications, the system read all application entities for every Worker associated with a foreman. Applications also include large JSON columns, such as my information at the time of application. But the main values needed on this screen were the application status and recruiting post ID. In other words, we were reading far more data than we needed. Other screens that used this list also repeatedly fetched the entire list and filtered it on the screen just to get a single value.

  • Foreman home: fetch the entire my applications list to exclude recruiting posts the user had already applied to

  • Application status, application history, recruiting list: call the recruiting post details API separately for each card

4-2. Foreman Home: Removing an Entire API Call

The foreman home needed to know whether the user had applied to each recruiting post, not fetch the entire applications list. I added an applied field to the recruiting post list response and changed the server to calculate its value by querying only recruiting post IDs. Since users can apply again after withdrawing an application, I kept the existing rule that a withdrawn application does not count as an applied-to recruiting post.

Server — calculate applied by reading only recruiting post IDs

Set<String> appliedIds = findMyActiveAppliedPostingIds();
Page<JobPostingRdo> jobPostingRdos = jobPostings.map(jobPosting -> {
    JobPostingRdo rdo = new JobPostingRdo(jobPosting, bookmarkedIds.contains(jobPosting.getId()));
    rdo.setApplied(appliedIds.contains(jobPosting.getId()));
    return rdo;
});

Screen — remove the call to my applications from the home page

// Before
const { myJobApplicationRdoList: applications } = useFindMyJobApplicationsWkr({});
const appliedJobPostingIds = new Set(applications.map((a) => a.jobPostingId));
... .filter((job) => job.jobPostingStatus === Open && !appliedJobPostingIds.has(job.jobPostingId))

// After — 지원 여부는 목록 응답의 applied로 본다
... .filter((job) => job.jobPostingStatus === JobPostingStatus.Open && !job.applied)

By changing the home page so it no longer fetched my applications separately, I also removed the need for the home page to wait for that API response.

4-3. Include Job Posting Summaries in the Application List Response

The application status and application history screens were calling the job posting details API for each application. To reduce these calls, we updated the server to include the job posting summary information needed for the cards in the application list response, and removed the repeated calls from the screens. Since calling the job posting details API records a view, we implemented a separate path for providing summary information in the list that does not record a view. We also changed the job posting list to use information included in the response in the same way.

Screen — Removed useQueries calls that fetched job posting details for every application

// Before
const detailQueries = useQueries({
  queries: applications.map((a) => findJobPostingDetailWkrOptions({ jobPostingId: a.jobPostingId })),
});

// After — 구인 글 요약은 지원 목록 응답에 함께 온다
const detail = application.jobPosting;

4-4. Reduce Data Read and Query Count

On the server, we added summary queries that fetch only the required columns instead of loading entire JPA entities. Because this approach does not need to read the application body JSON, we applied it to the logic that checks restrictions when submitting an application, the unreviewed applicant cards on the team lead home screen, and the application count chip. In particular, we reduced the unreviewed applicant queries—which previously ran once for each open job posting—to a single query. We also changed per-Worker queries to use an IN condition and process them all at once.

Application summary query — only columns excluding the body JSON

queryFactory
    .select(jobApplicationJpo.id, jobApplicationJpo.jobPostingId, jobApplicationJpo.workerId,
            jobApplicationJpo.status, jobApplicationJpo.registeredOn)
    .from(jobApplicationJpo)
    .where(jobApplicationJpo.jobPostingId.in(jobPostingIds))
    .fetch();

The team lead job posting list uses infinite scrolling. Previously, however, each page request also ran a count query to check the total number of items. Since it was only necessary to determine whether another page existed, we changed the query to fetch limit + 1 items and determine whether there is another page based on whether the extra item is present.

5. Verification After Improvements

After the improvements, the tester ran another load test locally using a similar approach. However, the first test was conducted in lab, while the retest was run locally with a different scenario setup. Therefore, the response times from the two results cannot be directly compared. Instead, within each test run, we checked how the position of the My Applications list changed relative to other APIs.

API

Average (ms), initial test in lab

Average (ms), local retest

My Applications list

345

6.6

Home card query (comparison baseline)

100

13.6

Job posting list (comparison baseline)

45

7.1

Table 2. Average response times for APIs compared within the same test run

In the first test, the average response time for the My Applications list was about 3.5 times that of the home card query. In the retest, it was about 0.5 times as long. The API that had taken relatively long in the first test no longer appeared to be a major bottleneck in the retest. The average response time for the team lead home screen was also 35.5 ms, similar to that of other screens. In the retest, the API with the longest average response time was the matching recommendations job posting query, at 32 ms on average. We also heard from users that the team lead home screen felt faster, but did not claim that this alone proved the effectiveness of the improvements.

However, these results alone cannot precisely demonstrate the performance difference before and after the improvements. We had no measurements of the pre-change code under the same environment and conditions, and the retest did not include resource metrics such as Hikari. Therefore, rather than claiming that something became faster by a specific multiple, this post focuses on how the relative positions of bottlenecks changed within each test.

6. Conclusion

  • Measurement results can reveal that the areas initially suspected may not be the actual bottlenecks. The response time of the count API that stood out in the test scenario was 27 ms, while the My Applications query was the higher-priority area for improvement. The test results helped us set priorities. Also, simply checking how many requests a screen makes can help identify N+1 problems.

  • If multiple screens use the same API, improving it in one place can affect several screens. In this case, the My Applications query was shared by four screens, so we chose it as a target for improvement.

  • N+1 problems are not just about the number of queries; they are also related to response design. If a screen has to make additional API calls because the response does not include the values it needs, it may be worth considering providing that information in the response. However, when calling an API itself has side effects, as with the job posting details API that records views, those side effects must be considered. Likewise, when providing an approximate value instead of a total count, the difference should be made clear.

  • It is also important to query only the required columns. In particular, for tables containing large JSON columns, summary queries or projections can help avoid reading data that the screen does not need.

Going forward, we need to measure again before and after the improvements under the same conditions in the lab environment to verify the difference. We also plan to investigate the matching recommendations job posting query, which emerged as the slowest API in the retest.

zero

Site footer