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.
To debug slow queries, you can use Prisma Optimize and follow the recommendations provided to improve query performance in your app.
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 if:
- All criteria of the
wherefilter are on scalar fields (unique or non-unique) of the same model you're querying. - All criteria use the
equalfilter, 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 },
})
},
})
},
})
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()
},
})
},
})
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 batch findMany() queries.
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
})
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,
},
})
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,
},
},
})
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.