Query optimization

This guide describes ways to optimize query performance, debug performance issues, and how to tackle common performance issues such as the n+1 problem.

Debugging performance issues

To help you debug and diagnose performance issues, you can log query events at client level, which allows you to see the generated queries, parameters, and durations.

Alternatively, if you are only interested in the time taken to run a query, you can implement logging middleware.

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.

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

Instead, 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.

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.

Do I have to use the fluent API to enable batching of queries?

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 .

When the dataloader batches findMany queries, you no longer need to use findUnique with the fluent API in this way.

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
})
Hide 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:

  • Use nested reads (include ) to return users and related posts
  • Use the in filter

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,
},
})
Hide 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,
},
},
})
Hide 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

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. Prisma Client supports the following bulk queries:

Using select to limit number of columns returned

Using select to limit the number of columns that are returned is unlikely to have an effect on performance unless you have identified this as a performance bottleneck through testing. For example, reading all fields may negatively affect performance if you have:

  • Tables with a large number of columns
  • Large columns that are stored in a separate location on disk rather than a row, which results in an additional disk read

Furthermore, if you have a mature product with well established query patterns and finely tuned indexes, selecting a specific subset of fields may be beneficial as it avoids reading data from disk. However, in most cases, this level of performance tuning is only necessary at a certain scale.