# Query optimization (/docs/orm/v6/prisma-client/queries/query-optimization-performance)

Location: ORM > v6 > Prisma Client > Queries > Query optimization

This guide covers identifying and optimizing query performance with Prisma.

Query Insights [#query-insights]

[Query Insights](/query-insights) is built into Prisma Postgres and shows you which queries are slow, how expensive they are, and what to fix. It works out of the box for raw SQL, but to see Prisma ORM operations (model name, action, query shape) you need one extra step.

Enabling Prisma ORM attribution [#enabling-prisma-orm-attribution]

Install `@prisma/sqlcommenter-query-insights`:

```bash
npm install @prisma/sqlcommenter-query-insights
```

Then pass it to the `comments` option in your `PrismaClient` constructor:

```ts
import { prismaQueryInsights } from "@prisma/sqlcommenter-query-insights";
import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient({
  adapter: myAdapter, // driver adapter or Accelerate URL required
  comments: [prismaQueryInsights()],
});
```

This adds a SQL comment to every query containing the model, action, and parameterized query shape. Query Insights uses these annotations to trace SQL back to the exact Prisma call that generated it — even when a single Prisma call produces multiple SQL statements.

Let your AI agent handle setup [#let-your-ai-agent-handle-setup]

Copy this prompt into your AI coding assistant:

```
Install and configure @prisma/sqlcommenter-query-insights in my project so I can
see Prisma ORM queries in Query Insights. Docs: https://www.prisma.io/docs/query-insights
```

Debugging performance issues [#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

Use [Query Insights](/query-insights) to identify which queries are affected and what to change.

> [!NOTE]
> You can also [log query events at the client level](/orm/v6/prisma-client/observability-and-logging/logging#event-based-logging) to view the generated queries, their parameters, and execution times.

Using bulk queries [#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:

* [`createMany()`](/orm/v6/reference/prisma-client-reference#createmany)
* [`createManyAndReturn()`](/orm/v6/reference/prisma-client-reference#createmanyandreturn)
* [`deleteMany()`](/orm/v6/reference/prisma-client-reference#deletemany)
* [`updateMany()`](/orm/v6/reference/prisma-client-reference#updatemany)
* [`updateManyAndReturn()`](/orm/v6/reference/prisma-client-reference#updatemanyandreturn)
* [`findMany()`](/orm/v6/reference/prisma-client-reference#findmany)

Reuse PrismaClient or use connection pooling to avoid database connection pool exhaustion [#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](/orm/v6/prisma-client/setup-and-configuration/databases-connections#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:

```ts title="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:

```ts title="db.ts"
export const prisma = new PrismaClient();
```

Then import the shared instance:

```ts title="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](/orm/v6/more/troubleshooting/nextjs#best-practices-for-using-prisma-client-in-development).

Solving the n+1 problem [#solving-the-n1-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 [#solving-n1-in-graphql-with-findunique-and-prisma-clients-dataloader]

[Watch video](https://www.youtube.com/watch?v=7oMfBGEdwsc)

The Prisma Client dataloader automatically *batches* `findUnique()` queries that occur in the same [tick](https://nodejs.org/en/learn/asynchronous-work/event-loop-timers-and-nexttick#processnexttick) 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):

```js
query {
  allUsers {
    id,
    posts {
      id
    }
  }
}
```

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

```ts highlight=7;normal
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:

```js
{
  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).

```ts highlight=10-13;normal;
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 },
        });
      },
    });
  },
});
```

```js no-copy
{
  timestamp: 2021-02-19T09:43:06.343Z,
  query: 'SELECT `dev`.`Post`.`id`, `dev`.`Post`.`createdAt`, `dev`.`Post`.`updatedAt`, `dev`.`Post`.`title`, `dev`.`Post`.`content`, `dev`.`Post`.`published`, `dev`.`Post`.`viewCount`, `dev`.`Post`.`authorId` FROM `dev`.`Post` WHERE `dev`.`Post`.`authorId` = ? LIMIT ? OFFSET ?',
  params: '[1,-1,0]',
  duration: 0,
  target: 'quaint::connector::metrics'
}
{
  timestamp: 2021-02-19T09:43:06.347Z,
  query: 'SELECT `dev`.`Post`.`id`, `dev`.`Post`.`createdAt`, `dev`.`Post`.`updatedAt`, `dev`.`Post`.`title`, `dev`.`Post`.`content`, `dev`.`Post`.`published`, `dev`.`Post`.`viewCount`, `dev`.`Post`.`authorId` FROM `dev`.`Post` WHERE `dev`.`Post`.`authorId` = ? LIMIT ? OFFSET ?',
  params: '[3,-1,0]',
  duration: 0,
  target: 'quaint::connector::metrics'
}
{
  timestamp: 2021-02-19T09:43:06.348Z,
  query: 'SELECT `dev`.`Post`.`id`, `dev`.`Post`.`createdAt`, `dev`.`Post`.`updatedAt`, `dev`.`Post`.`title`, `dev`.`Post`.`content`, `dev`.`Post`.`published`, `dev`.`Post`.`viewCount`, `dev`.`Post`.`authorId` FROM `dev`.`Post` WHERE `dev`.`Post`.`authorId` = ? LIMIT ? OFFSET ?',
  params: '[2,-1,0]',
  duration: 0,
  target: 'quaint::connector::metrics'
}
{
  timestamp: 2021-02-19T09:43:06.348Z,
  query: 'SELECT `dev`.`Post`.`id`, `dev`.`Post`.`createdAt`, `dev`.`Post`.`updatedAt`, `dev`.`Post`.`title`, `dev`.`Post`.`content`, `dev`.`Post`.`published`, `dev`.`Post`.`viewCount`, `dev`.`Post`.`authorId` FROM `dev`.`Post` WHERE `dev`.`Post`.`authorId` = ? LIMIT ? OFFSET ?',
  params: '[4,-1,0]',
  duration: 0,
  target: 'quaint::connector::metrics'
}
{
  timestamp: 2021-02-19T09:43:06.348Z,
  query: 'SELECT `dev`.`Post`.`id`, `dev`.`Post`.`createdAt`, `dev`.`Post`.`updatedAt`, `dev`.`Post`.`title`, `dev`.`Post`.`content`, `dev`.`Post`.`published`, `dev`.`Post`.`viewCount`, `dev`.`Post`.`authorId` FROM `dev`.`Post` WHERE `dev`.`Post`.`authorId` = ? LIMIT ? OFFSET ?',
  params: '[5,-1,0]',
  duration: 0,
  target: 'quaint::connector::metrics'
}
// And so on
```

Solution 1: Batching queries with the fluent API [#solution-1-batching-queries-with-the-fluent-api]

Use `findUnique()` in combination with [the fluent API](/orm/v6/prisma-client/queries/relation-queries#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**.

> [!NOTE]
> It may seem counterintuitive 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](https://github.com/prisma/prisma/issues/1477).
> 
> 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.

```ts highlight=13-18;add|10-12;delete
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({
          // [!code --]
          where: { authorId: parent.id || undefined }, // [!code --]
        }); // [!code --]
        return context.prisma.user // [!code ++]
          .findUnique({
            // [!code ++]
            where: { id: parent.id || undefined }, // [!code ++]
          }) // [!code ++]
          .posts(); // [!code ++]
      }, // [!code ++]
    });
  },
});
```

```js no-copy
{
  timestamp: 2021-02-19T09:59:46.340Z,
  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'
}
{
  timestamp: 2021-02-19T09:59:46.350Z,
  query: 'SELECT `dev`.`User`.`id` FROM `dev`.`User` WHERE `dev`.`User`.`id` IN (?,?,?) LIMIT ? OFFSET ?',
  params: '[1,2,3,-1,0]',
  duration: 0,
  target: 'quaint::connector::metrics'
}
{
  timestamp: 2021-02-19T09:59:46.350Z,
  query: 'SELECT `dev`.`Post`.`id`, `dev`.`Post`.`createdAt`, `dev`.`Post`.`updatedAt`, `dev`.`Post`.`title`, `dev`.`Post`.`content`, `dev`.`Post`.`published`, `dev`.`Post`.`viewCount`, `dev`.`Post`.`authorId` FROM `dev`.`Post` WHERE `dev`.`Post`.`authorId` IN (?,?,?) LIMIT ? OFFSET ?',
  params: '[1,2,3,-1,0]',
  duration: 0,
  target: 'quaint::connector::metrics'
}
```

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 [#solution-2-using-joins-to-perform-queries]

You can perform the query with a [database join](/orm/v6/prisma-client/queries/relation-queries#relation-load-strategies-preview) by setting `relationLoadStrategy` to `"join"`, ensuring that only **one** query is executed against the database.

```ts
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 [#n1-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:

```ts
// 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
});
```

```sql no-copy
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`](/orm/v6/reference/prisma-client-reference#include) ) to return users and related posts
* Use the [`in`](/orm/v6/reference/prisma-client-reference#in) filter
* Set the [`relationLoadStrategy`](/orm/v6/prisma-client/queries/relation-queries#relation-load-strategies-preview) to `"join"`

Solving n+1 with include [#solving-n1-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](/orm/v6/prisma-client/queries/relation-queries#nested-reads).

```ts
const usersWithPosts = await prisma.user.findMany({
  include: {
    posts: true,
  },
});
```

```sql no-copy
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 [#solving-n1-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:

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

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

const posts = await prisma.post.findMany({
  where: {
    authorId: {
      in: userIds,
    },
  },
});
```

```sql no-copy
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" [#solving-n1-with-relationloadstrategy-join]

You can perform the query with a [database join](/orm/v6/prisma-client/queries/relation-queries#relation-load-strategies-preview) by setting `relationLoadStrategy` to `"join"`, ensuring that only **one** query is executed against the database.

```ts
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,
    },
  },
});
```

## Related pages

- [`Aggregation, grouping, and summarizing`](https://www.prisma.io/docs/orm/v6/prisma-client/queries/aggregation-grouping-summarizing): Use Prisma Client to aggregate, group by, count, and select distinct.
- [`Case sensitivity`](https://www.prisma.io/docs/orm/v6/prisma-client/queries/case-sensitivity): How Prisma Client handles case sensitivity when filtering and sorting.
- [`Computed fields`](https://www.prisma.io/docs/orm/v6/prisma-client/queries/computed-fields): This page explains how to use client extensions to add computed fields to Prisma models.
- [`CRUD`](https://www.prisma.io/docs/orm/v6/prisma-client/queries/crud): How to perform CRUD with Prisma Client.
- [`Custom models`](https://www.prisma.io/docs/orm/v6/prisma-client/queries/custom-models): This page explains how to wrap Prisma Client in custom models