P
Prisma•2w ago
MinatoTW

Query Performance

Hello, I wonder if there's any way to disable the extra SELECT queries made during updates. For example: This is the query I'm using to update a player. 
await tx.player.update({
  where: { user_id: state.player1_id },
  select: { id: true },
  data: {
    gold: { increment: player_gold },
    platinum: { decrement: attemptCost },
    potions: {
      updateMany: usedPotions.map((potion) => ({
        where: {
          potion_id: potion.potion.id,
          player_id: state.player1_id,
        },
        data: {
          quantity: potion.quantity,
        },
      })),
    },
  },
});
await tx.player.update({
  where: { user_id: state.player1_id },
  select: { id: true },
  data: {
    gold: { increment: player_gold },
    platinum: { decrement: attemptCost },
    potions: {
      updateMany: usedPotions.map((potion) => ({
        where: {
          potion_id: potion.potion.id,
          player_id: state.player1_id,
        },
        data: {
          quantity: potion.quantity,
        },
      })),
    },
  },
});
I see the following queries in the debug log: 
api-1     | prisma:query UPDATE "public"."player_potions" SET "quantity" = $1 WHERE ("public"."player_potions"."id" IN ($2) AND 1=1)
api-1     | prisma:query SELECT "public"."player_potions"."id", "public"."player_potions"."player_id" FROM "public"."player_potions" WHERE (("public"."player_potions"."potion_id" = $1 AND "public"."player_potions"."player_id" = $2) AND "public"."player_potions"."player_id" IN ($3)) OFFSET $4
api-1     | prisma:query UPDATE "public"."player_potions" SET "quantity" = $1 WHERE ("public"."player_potions"."id" IN ($2) AND 1=1)
api-1     | prisma:query SELECT "public"."player_potions"."id", "public"."player_potions"."player_id" FROM "public"."player_potions" WHERE (("public"."player_potions"."potion_id" = $1 AND "public"."player_potions"."player_id" = $2) AND "public"."player_potions"."player_id" IN ($3)) OFFSET $4
api-1     | prisma:query UPDATE "public"."player_potions" SET "quantity" = $1 WHERE ("public"."player_potions"."id" IN ($2) AND 1=1)
api-1     | prisma:query UPDATE "public"."player_potions" SET "quantity" = $1 WHERE ("public"."player_potions"."id" IN ($2) AND 1=1)
api-1     | prisma:query SELECT "public"."player_potions"."id", "public"."player_potions"."player_id" FROM "public"."player_potions" WHERE (("public"."player_potions"."potion_id" = $1 AND "public"."player_potions"."player_id" = $2) AND "public"."player_potions"."player_id" IN ($3)) OFFSET $4
api-1     | prisma:query UPDATE "public"."player_potions" SET "quantity" = $1 WHERE ("public"."player_potions"."id" IN ($2) AND 1=1)
api-1     | prisma:query SELECT "public"."player_potions"."id", "public"."player_potions"."player_id" FROM "public"."player_potions" WHERE (("public"."player_potions"."potion_id" = $1 AND "public"."player_potions"."player_id" = $2) AND "public"."player_potions"."player_id" IN ($3)) OFFSET $4
api-1     | prisma:query UPDATE "public"."player_potions" SET "quantity" = $1 WHERE ("public"."player_potions"."id" IN ($2) AND 1=1)
Why is it making all those extra SELECT queries rather than just updating? I'm worried it might impact performance as we scale.
3 Replies
Nurul
Nurul•7d ago
Hello 👋 There isn't a direct way to disable the extra SELECT queries for the nested query entirely unless you use a Raw Query or TypedSQL. For simple updates where you don't need the returned data, you could use updateMany even for single record updates, as it doesn't perform the extra SELECT. We have a feature request for this use case here I would recommend adding a 👍 to the request, so we can prioritise it
MinatoTW
MinatoTWOP•7d ago
you mean I can do player.updateMany() to avoid the select? oh I see, all good then seems like updateMany doesn't work for relational fields right? am I missing something
Nurul
Nurul•7d ago
Yes, relation fields aren't supported in updateMany. https://github.com/prisma/prisma/issues/3143
GitHub
Add ability to update relation fields inside updateMany() · Issue...
Problem Given this schema: model Booking { worker Worker } If we want to disconnect multiple bookings at once, we currently have to run a separate update call for each individual booking: await Pro...

Did you find this page helpful?