Skip to main content

Query optimization

This guide shows how to identify and optimize query performance, debug performance issues, and address common challenges.

Debugging performance issues​

Several common practices can lead to slow queries and performance problems, such as:

  • Over-fetching data
  • Missing indexes
  • Not caching repeated queries
  • Performing full table scans
info

For more potential causes of performance issues, visit this page.

Prisma Optimize offers recommendations to identify and address the inefficiencies listed above and more, helping to improve query performance.

To get started, follow the integration guide and add Prisma Optimize to your project to begin diagnosing slow queries.

tip

You can also log query events at the client level to view the generated queries, their parameters, and execution times.

If you are particularly focused on monitoring query duration, consider using logging middleware.

Using bulk queries​

It is generally more performant to read and write large amounts of data in bulk - for example, inserting 50,000 records in batches of 1000 rather than as 50,000 separate inserts. PrismaClient supports the following bulk queries:

Reuse PrismaClient or use connection pooling to avoid database connection pool exhaustion​

Creating multiple instances of PrismaClient can exhaust your database connection pool, especially in serverless or edge environments, potentially slowing down other queries. Learn more in the serverless challenge.

For applications with a traditional server, instantiate PrismaClient once and reuse it throughout your app instead of creating multiple instances. For example, instead of:

query.ts
async function getPosts() {
  const prisma = new PrismaClient()
  await prisma.post.findMany()
}

async function getUsers() {
  const prisma = new PrismaClient()
  await prisma.user.findMany()
}

Define a single PrismaClient instance in a dedicated file and re-export it for reuse:

db.ts
export const prisma = new PrismaClient()

Then import the shared instance:

query.ts
import { prisma } from "db.ts"

async function getPosts() {
  await prisma.post.findMany()
}

async function getUsers() {
  await prisma.user.findMany()
}

For serverless development environments with frameworks that use HMR (Hot Module Replacement), ensure you properly handle a single instance of Prisma in development.

Solving the n+1 problem​

The n+1 problem occurs when you loop through the results of a query and perform one additional query per result, resulting in n number of queries plus the original (n+1). This is a common problem with ORMs, particularly in combination with GraphQL, because it is not always immediately obvious that your code is generating inefficient queries.

Solving n+1 in GraphQL with findUnique() and Prisma Client's dataloader​

The Prisma Client dataloader automatically batches findUnique() queries that occur in the same tick and have the same where and include parameters if:

  • All criteria of the where filter are on scalar fields (unique or non-unique) of the same model you're querying.
  • All criteria use the equal filter, whether that's via the shorthand or explicit syntax (where: { field: <val>, field1: { equals: <val> } }).
  • No boolean operators or relation filters are present.

Automatic batching of findUnique() is particularly useful in a GraphQL context. GraphQL runs a separate resolver function for every field, which can make it difficult to optimize a nested query.

For example - the following GraphQL runs the allUsers resolver to get all users, and the posts resolver once per user to get each user's posts (n+1):

query {
  allUsers {
    id,
    posts {
      id
    }
  }
}

The allUsers query uses user.findMany(..) to return all users:

const Query = objectType({
  name: 'Query',
  definition(t) {
    t.nonNull.list.nonNull.field('allUsers', {
      type: 'User',
      resolve: (_parent, _args, context) => {
        return context.prisma.user.findMany()
      },
    })
  },
})

This results in a single SQL query:

{
  timestamp: 2021-02-19T09:43:06.332Z,
  query: 'SELECT `dev`.`User`.`id`, `dev`.`User`.`email`, `dev`.`User`.`name` FROM `dev`.`User` WHERE 1=1 LIMIT ? OFFSET ?',
  params: '[-1,0]',
  duration: 0,
  target: 'quaint::connector::metrics'
}

However, the resolver function for posts is then invoked once per user. This results in a findMany() query ✘ per user rather than a single findMany() to return all posts by all users (expand CLI output to see queries).

const User = objectType({
  name: 'User',
  definition(t) {
    t.nonNull.int('id')
    t.string('name')
    t.nonNull.string('email')
    t.nonNull.list.nonNull.field('posts', {
      type: 'Post',
      resolve: (parent, _, context) => {
        return context.prisma.post.findMany({
          where: { authorId: parent.id || undefined },
        })
      },
    })
  },
})
Show CLI results

Solution 1: Batching queries with the fluent API​

Use findUnique() in combination with the fluent API (.posts()) as shown to return a user's posts. Even though the resolver is called once per user, the Prisma dataloader in Prisma Client ✔ batches the findUnique() queries.

info

It may seem counterintitive to use a prisma.user.findUnique(...).posts() query to return posts instead of prisma.posts.findMany() - particularly as the former results in two queries rather than one.

The only reason you need to use the fluent API (user.findUnique(...).posts()) to return posts is that the dataloader in Prisma Client batches findUnique() queries and does not currently batch findMany() queries.

When the dataloader batches findMany() queries or your query has the relationStrategy set to join, you no longer need to use findUnique() with the fluent API in this way.

const User = objectType({
  name: 'User',
  definition(t) {
    t.nonNull.int('id')
    t.string('name')
    t.nonNull.string('email')
    t.nonNull.list.nonNull.field('posts', {
      type: 'Post',
      resolve: (parent, _, context) => {
        return context.prisma.post.findMany({
          where: { authorId: parent.id || undefined },
        })
        return context.prisma.user
          .findUnique({
            where: { id: parent.id || undefined },
          })
          .posts()
      },
    })
  },
})
Show CLI results

If the posts resolver is invoked once per user, the dataloader in Prisma Client groups findUnique() queries with the same parameters and selection set. Each group is optimized into a single findMany().

Solution 2: Using JOINs to perform queries​

You can perform the query with a database join by setting relationLoadStrategy to "join", ensuring that only one query is executed against the database.

const User = objectType({
  name: 'User',
  definition(t) {
    t.nonNull.int('id')
    t.string('name')
    t.nonNull.string('email')
    t.nonNull.list.nonNull.field('posts', {
      type: 'Post',
      resolve: (parent, _, context) => {
        return context.prisma.post.findMany({
          relationLoadStrategy: "join",
          where: { authorId: parent.id || undefined },
        })
      },
    })
  },
})

n+1 in other contexts​

The n+1 problem is most commonly seen in a GraphQL context because you have to find a way to optimize a single query across multiple resolvers. However, you can just as easily introduce the n+1 problem by looping through results with forEach in your own code.

The following code results in n+1 queries - one findMany() to get all users, and one findMany() per user to get each user's posts:

// One query to get all users
const users = await prisma.user.findMany({})

// One query PER USER to get all posts
users.forEach(async (usr) => {
  const posts = await prisma.post.findMany({
    where: {
      authorId: usr.id,
    },
  })

  // Do something with each users' posts
})
Show CLI results
SELECT "public"."User"."id", "public"."User"."email", "public"."User"."name" FROM "public"."User" WHERE 1=1 OFFSET $1
SELECT "public"."Post"."id", "public"."Post"."title" FROM "public"."Post" WHERE "public"."Post"."authorId" = $1 OFFSET $2
SELECT "public"."Post"."id", "public"."Post"."title" FROM "public"."Post" WHERE "public"."Post"."authorId" = $1 OFFSET $2
SELECT "public"."Post"."id", "public"."Post"."title" FROM "public"."Post" WHERE "public"."Post"."authorId" = $1 OFFSET $2
SELECT "public"."Post"."id", "public"."Post"."title" FROM "public"."Post" WHERE "public"."Post"."authorId" = $1 OFFSET $2
/* ..and so on .. */

This is not an efficient way to query. Instead, you can:

Solving n+1 with include​

You can use include to return each user's posts. This only results in two SQL queries - one to get users, and one to get posts. This is known as a nested read.

const usersWithPosts = await prisma.user.findMany({
  include: {
    posts: true,
  },
})
Show CLI results
SELECT "public"."User"."id", "public"."User"."email", "public"."User"."name" FROM "public"."User" WHERE 1=1 OFFSET $1
SELECT "public"."Post"."id", "public"."Post"."title", "public"."Post"."authorId" FROM "public"."Post" WHERE "public"."Post"."authorId" IN ($1,$2,$3,$4) OFFSET $5

Solving n+1 with in​

If you have a list of user IDs, you can use the in filter to return all posts where the authorId is in that list of IDs:

const users = await prisma.user.findMany({})

const userIds = users.map((x) => x.id)

const posts = await prisma.post.findMany({
  where: {
    authorId: {
      in: userIds,
    },
  },
})
Show CLI results
SELECT "public"."User"."id", "public"."User"."email", "public"."User"."name" FROM "public"."User" WHERE 1=1 OFFSET $1
SELECT "public"."Post"."id", "public"."Post"."createdAt", "public"."Post"."updatedAt", "public"."Post"."title", "public"."Post"."content", "public"."Post"."published", "public"."Post"."authorId" FROM "public"."Post" WHERE "public"."Post"."authorId" IN ($1,$2,$3,$4) OFFSET $5

Solving n+1 with relationLoadStrategy: "join"​

You can perform the query with a database join by setting relationLoadStrategy to "join", ensuring that only one query is executed against the database.

const users = await prisma.user.findMany({})

const userIds = users.map((x) => x.id)

const posts = await prisma.post.findMany({
  relationLoadStrategy: "join",
  where: {
    authorId: {
      in: userIds,
    },
  },
})