Prisma•2mo ago

Prisma Query Performance Question

I have a nextjs application built with trpc endpoints and Prisma (also using Prisma Postgres - here is my schema). Most of my queries are pretty quick and dont give me any concern about performance, but one of my queries is noticeably slow (get all). This request is made from a trpc endpoint. I think it is fair to assume that this query could be slower than others given it is loading more data, but it is incredibly slow (initial load sometimes taking 3+ seconds and I only have 24 recipes in the db) relative to the rest of my queries. What is even more interesting is this query is shared between pages, and it seems to only be slow on one page. I know there are a handful of variables at play here, but looking at my schema and query, are there any obvious issues I have/improvements to be made (schema index’s, etc). To test this out live here are the 2 deployed pages on my site: - Recipe list - this page provides the optional categories prop in the query input - Recipe build - this page provides the optional ingredients prop in the query input Both pages provide empty arrays for their respective inputs on initial page load, so I’d expect them to have similar initial load times, but /build seems to be consistently faster than /recipes. Please let me know if anyone has questions to help clarify the problem.

6 Replies

Prisma AI Help•2mo ago

Salutations, traveler! I'm the Prisma AI Help Bot. You've reached a fork in the road: one path leads to the wisdom of the human sages (ETA: sometime today), and the other to my instantaneous insights. Which way shall we go?

Max•2mo ago

Seem like you're loading a lot of relationships in a select clause, unless you're using relationLoadStrategy: 'join' (which is a preview feature i believe) you should use an include clause. as-is basically what will happen is it will load the recipes, then for each recipe load all of the categories and count the favorites. it can balloon pretty quickly. I'm not sure how prisma handles where clauses with relations, when not using relation joins, but they may be contributing even more sub queries. i would also check and see if removing some of the bits in the where clauses speeds things up

tyler4949OP•2mo ago

Thanks for the feedback, @Max! Would it be possible for you to provide a small example of using the include clause instead? Doesn't necessarily need to be in the context of my app, but want to make sure I understand what you're saying. BE/DB are definitely not my strong suit, so learning as I go on this project.

Max•2mo ago

Of course! Instead of doing:

select: {
  id: true,
  name: true,
  categories: true,
  description: true,
  prepTime: true,
  cookTime: true,
  servings: true,
  _count: { select: { favorites: true } },
},

select: {
  id: true,
  name: true,
  categories: true,
  description: true,
  prepTime: true,
  cookTime: true,
  servings: true,
  _count: { select: { favorites: true } },
},

You can use:

include: {
  categories: true,
  _count: { select: { favorites: true } },
},

include: {
  categories: true,
  _count: { select: { favorites: true } },
},

Just note that include can't take actual table columns (called "scalar fields"), instead it takes in relationships of that table. INSTEAD all of the columns on recipes are returned (from what I remember) Cool to see you're just beginning to look into backend! Another example: Say you have a table called Movies and another table called Reviews There are plenty of movies, and plenty of reviews for those movies. Using something like this:

await prisma.movies.findMany({
  select: {
    name: true,
    reviews: true
  }
})

await prisma.movies.findMany({
  select: {
    name: true,
    reviews: true
  }
})

Would do something like this: Find movies (1 query): 1. Fast & Furious 2. Harry Potter 3. Lord of the Rings 4. etc. THEN for each movie it would do (1 query per movie): 1. Lookup reviews for Fast & Furious 2. Lookup reviews for Harry Potter 3. Lookup review for Lord of the Rings 4. And so on Becomes a lot of queries pretty quickly. Using include makes this perform better:

await prisma.movies.findMany({
  include: {
    reviews: true
  }
})

await prisma.movies.findMany({
  include: {
    reviews: true
  }
})

Now, we have the same first step (reading movies), but the next step is much faster. With includes, you get one query to find ALL reviews related to the movies you read. Something like Find all reviews related to F&F HP & LotR. Then Prisma will match things up under the hood. It's pretty neat for sure. You can read more about this problem on Prisma's docs: https://www.prisma.io/docs/orm/prisma-client/queries/query-optimization-performance Hope this explains it well enough 🙂

Query optimization using Prisma Optimize | Prisma Documentation

How Prisma optimizes queries under the hood

Nurul•2mo ago

Thanks for chiming in Max 🙌 I agree on using include to load relation fields. In addition to what Max said, I would also recommend you to try using Prisma Optimize and see if you get recommendations on improving your queries. https://www.prisma.io/optimize

Prisma

Prisma Optimize: AI-driven query analysis

Gain deep insights and get actionable recommendations to improve your database queries, making your app run faster.

tyler4949OP•2mo ago

Thank you to both of you for your responses! Really appreciate it. Will give include a shot and will definitely check out the optimize tool

Gaming

Programming

Prisma Query Performance Question

Did you find this page helpful?