पाठ 28 / 35

Making ORM Queries Fast

यह पाठ अभी हिंदी में अनुवादित नहीं है, इसलिए अंग्रेज़ी में दिखाया जा रहा है।

Find slow queries from logs, read a query plan, add the right index, fetch less data and switch to cursor pagination for deep pages.

लेखक: · 15 साल से वेब डेवलपर

Measure, then fix the SQL

ORM performance work is SQL performance work. Start by measuring: enable query logging or your database's slow-query log, and find the queries that run most often or take longest. Then ask the database how it executes one with EXPLAIN (PostgreSQL's EXPLAIN ANALYZE also runs it and reports real timings). A sequential scan over a large table for a selective filter usually means a missing index on the filtered or joined column. Next, fetch less: select only needed columns, avoid loading relations you do not render, and replace counting in JavaScript with a COUNT query. Finally, fix deep pagination: OFFSET 100000 makes the database walk past and discard a hundred thousand rows. Cursor (keyset) pagination remembers the last seen key and asks for WHERE id > :lastId ORDER BY id LIMIT n, which can use an index and stays fast on any page. The trade-off is that you cannot jump straight to page 57.

A performance and safety loop

Log the queries, find the slow or unsafe ones, fix them with an index, a narrower select or parameters, then verify the plan and timings again.

Four connected stages: log queries, identify slow or unsafe queries, apply a fix, verify the result.
Figure 9.1: log queries -> find slow or unsafe ones -> fix (index, select, parameters) -> verify

An index changes the plan

SQLite's EXPLAIN QUERY PLAN shows the same idea PostgreSQL's EXPLAIN does: a full scan before the index, an index search after it.

import sqlite3
db = sqlite3.connect(':memory:')
db.execute("CREATE TABLE posts (id INTEGER PRIMARY KEY, author_id INTEGER, title TEXT)")
db.executemany("INSERT INTO posts (author_id, title) VALUES (?, ?)",
               [(i % 1000, f'post {i}') for i in range(50000)])
q = "EXPLAIN QUERY PLAN SELECT id, title FROM posts WHERE author_id = 42"
print('before:', db.execute(q).fetchall()[0][3])
db.execute("CREATE INDEX idx_posts_author ON posts(author_id)")
print('after: ', db.execute(q).fetchall()[0][3])

Output:

before: SCAN posts
after:  SEARCH posts USING INDEX idx_posts_author (author_id=?)

Cursor pagination in Prisma

cursor starts at the last row of the previous page and skip: 1 excludes that row itself.

const firstPage = await prisma.post.findMany({ take: 20, orderBy: { id: 'asc' } });
// assumes the first page has rows; check firstPage.length in real code
const lastId = firstPage[firstPage.length - 1].id;

const nextPage = await prisma.post.findMany({
  take: 20,
  skip: 1,
  cursor: { id: lastId },
  orderBy: { id: 'asc' },
});

त्वरित जाँच: An infinite-scroll feed with millions of rows gets slower the further a user scrolls. It uses skip/take with large offsets. What is the best fix?

  • Switch to cursor pagination on an indexed, ordered key
  • Increase take so fewer pages are requested
  • Remove ORDER BY to save sorting time
  • Wrap each page query in a transaction
Answer

Switch to cursor pagination on an indexed, ordered key — Large offsets force the database to skip rows one by one. Keyset or cursor pagination seeks directly via the index, so every page costs about the same.