Sangwoo Lee

Posted on Feb 16

Solving MSSQL's 2100 Parameter Limit: From IN Clause to Cursor-Based Pagination

#backend #database #sql #typescript

How we fixed 'The incoming request has too many parameters' error in a NestJS push notification service by switching from IN clause batch queries to cursor-based pagination

The Problem: A Mysterious MSSQL Error

While working on a Bible reading reminder feature in our NestJS application, we encountered a cryptic error:

Error: The incoming request has too many parameters. 
The server supports a maximum of 2100 parameters.
Error Code: EREQUEST

This error occurred when trying to fetch push tokens for inactive users. Interestingly, the exact same pagination constants worked perfectly in another service (firebase.service.ts) but failed in our new service (bible-read.service.ts).

// These constants were identical in both services
private readonly DB_FETCH_PAGE_SIZE = 10000;
private readonly PAGE_FETCH_DELAY_MS = 100;

So why did one service work while the other crashed? 🤔

Root Cause Analysis

The culprit was different pagination strategies:

❌ bible-read.service.ts (IN Clause Batch Query)

// Step 1: Get inactive user IDs from mobile DB
const inactiveMembers = await this.mobileDataSource.query(query);
const memberSeqs = inactiveMembers.map(m => m.member_seq);

// Step 2: Fetch push tokens in batches
let page = 0;
while (page * pageSize < memberSeqs.length) {
  // Slice array in JavaScript memory
  const chunk = memberSeqs.slice(page * pageSize, (page + 1) * pageSize);

  // ❌ IN clause with 10,000 parameters!
  const members = await MemberRepository
    .where('member.seq IN (:...seqs)', { seqs: chunk })
    .getMany();

  page++;
}

Problem: The IN clause creates one parameter per value. With a chunk size of 10,000, we're sending 10,000 parameters to MSSQL, far exceeding its 2,100 parameter limit.

✅ firebase.service.ts (Cursor-Based Pagination)

let lastSeq = 0;

while (true) {
  // ✅ Only 3 parameters: lastSeq, pushOnoff, limit
  const members = await Repository
    .where('member.seq > :lastSeq', { lastSeq })
    .andWhere('member.push_onoff = :pushOnoff', { pushOnoff: 'Y' })
    .orderBy('member.seq', 'ASC')
    .take(10000)
    .getMany();

  if (members.length === 0) break;

  lastSeq = members[members.length - 1].seq; // Next cursor
}

Why it works: Cursor-based pagination uses a single cursor parameter (lastSeq) instead of listing all IDs. This keeps the parameter count minimal regardless of page size.

Pagination Strategies Compared

Strategy	Parameters	MSSQL Safe?	Performance
IN Clause Batch	10,000	❌ Exceeds limit	Heavy IN parsing
Cursor-Based	3	✅ Safe	Efficient index scan
OFFSET/LIMIT	3	✅ Safe	Skips earlier rows

The Solution: Migrating to Cursor-Based Pagination

Here's the refactored code for bible-read.service.ts:

private async getInactiveUsersWithTokens(
  inactiveDays: number,
): Promise<Array> {

  // Step 1: Get inactive users from mobile DB
  const inactiveMembers = await this.mobileDataSource.query(query, [inactiveDays]);

  if (inactiveMembers.length === 0) return [];

  // Step 2: Build a lookup map for fast access
  const inactiveMemberMap = new Map();
  let minSeq = Infinity;
  let maxSeq = -Infinity;

  for (const member of inactiveMembers) {
    inactiveMemberMap.set(member.member_seq, member);
    if (member.member_seq < minSeq) minSeq = member.member_seq;
    if (member.member_seq > maxSeq) maxSeq = member.member_seq;
  }

  this.logger.log(`Member seq range: ${minSeq} ~ ${maxSeq}`);

  // Step 3: Cursor-based pagination to fetch push tokens
  const results = new Map();
  let lastSeq = minSeq - 1;
  let pageCount = 0;

  while (true) {
    pageCount++;

    // ✅ Cursor-based query with only 3 parameters
    const members = await this.memberRepository
      .createQueryBuilder('member')
      .select(['member.seq', 'member.push_token'])
      .where('member.seq > :lastSeq', { lastSeq })
      .andWhere('member.seq <= :maxSeq', { maxSeq })
      .andWhere('member.push_onoff = :pushOnoff', { pushOnoff: 'Y' })
      .andWhere('member.push_token IS NOT NULL')
      .andWhere("member.push_token != ''")
      .orderBy('member.seq', 'ASC')
      .take(this.DB_FETCH_PAGE_SIZE)
      .getMany();

    if (members.length === 0) break;

    this.logger.log(
      `Page ${pageCount}: ${members.length} members fetched ` +
      `(cursor: ${lastSeq} → ${members[members.length - 1].seq})`
    );

    // Filter and add only inactive users
    let addedCount = 0;
    for (const member of members) {
      const inactiveMember = inactiveMemberMap.get(member.seq);

      if (inactiveMember && member.push_token?.trim()) {
        results.set(member.push_token, {
          member_seq: member.seq,
          last_read_date: inactiveMember.last_read_date,
          // ... other fields
        });
        addedCount++;
      }
    }

    this.logger.log(
      `Page ${pageCount}: ${addedCount} inactive users added ` +
      `(total: ${results.size})`
    );

    // Update cursor
    lastSeq = members[members.length - 1].seq;

    // Stop if we've reached the end of the range
    if (lastSeq >= maxSeq) break;

    // Small delay to reduce DB load
    await delay(this.PAGE_FETCH_DELAY_MS);
  }

  return Array.from(results.entries()).map(([push_token, data]) => ({
    push_token,
    ...data,
  }));
}

Key Improvements

1. Parameter Count: 10,000 → 3

// Before: 10,000 parameters
WHERE member.seq IN (1, 2, 3, ..., 10000)

// After: 3 parameters
WHERE member.seq > :lastSeq 
  AND member.seq <= :maxSeq 
  AND member.push_onoff = :pushOnoff

2. Consistent Pattern Across Services

Both firebase.service.ts and bible-read.service.ts now use the same cursor-based approach, improving maintainability.

3. Index-Friendly Queries

Cursor-based pagination leverages indexes more efficiently than IN clauses with thousands of values.

Performance Considerations

When Cursor-Based Shines ✅

Users are densely distributed (e.g., 4,000 inactive users in seq range 12,000-15,000)
Indexes on seq column exist
Consistent query pattern across the codebase

Potential Drawback ⚠️

If inactive users are sparsely distributed (e.g., 100 users scattered across seq 1-1,000,000), the cursor might scan many unnecessary rows.

Optimization:

-- Add a compound index
CREATE INDEX IX_member_seq_push 
ON member(seq, push_onoff, push_token);

Results

[getInactiveUsersWithTokens] Inactive members: 5,234 found
[getInactiveUsersWithTokens] Member seq range: 10001 ~ 950123
[getInactiveUsersWithTokens] Page 1: 10000 members fetched (cursor: 10000 → 20500)
[getInactiveUsersWithTokens] Page 1: 423 inactive users added (total: 423)
[getInactiveUsersWithTokens] Page 2: 10000 members fetched (cursor: 20500 → 31200)
[getInactiveUsersWithTokens] Page 2: 389 inactive users added (total: 812)
...
[getInactiveUsersWithTokens] ✅ Final targets: 4,892 users

No more parameter limit errors! 🎉

Lessons Learned

Different pagination strategies have different parameter requirements - What works for one query pattern might not work for another
Cursor-based pagination is more scalable when dealing with large datasets
Understanding database limitations (like MSSQL's 2,100 parameter cap) is crucial for production systems
Code consistency matters - Using the same pattern across services reduces debugging time

Alternative Solutions

If cursor-based pagination isn't suitable for your use case, consider:

1. Chunked IN Clauses

const CHUNK_SIZE = 1500; // Safe margin below 2,100

for (let i = 0; i < memberSeqs.length; i += CHUNK_SIZE) {
  const chunk = memberSeqs.slice(i, i + CHUNK_SIZE);
  // Process chunk...
}

2. Temporary Table + JOIN

CREATE TABLE #TempSeqs (member_seq INT PRIMARY KEY);

INSERT INTO #TempSeqs VALUES (1), (2), (3), ...;

SELECT m.* 
FROM member m
INNER JOIN #TempSeqs t ON m.seq = t.member_seq
WHERE m.push_onoff = 'Y';

DROP TABLE #TempSeqs;

Conclusion

By switching from IN clause batch queries to cursor-based pagination, we:

✅ Eliminated the MSSQL 2,100 parameter limit error
✅ Improved code consistency across services
✅ Reduced database query parsing overhead
✅ Maintained the same 10,000 page size

DEV Community