3

I noticed that deleteMany uses two queries when I specify a where. It first selects the primary keys of the rows to delete, and then removes them with a DELETE FROM WHERE id IN (...) query.

What is the use of this? Instead of the WHERE id IN (...) query, it would make more sense to me to just select only the rows to delete in the DELETE query itself.

As an example:

await this.prismaService.cardSet.deleteMany({ where: { steamAccountId: steamAccount.id } });

This runs:

SELECT "public"."CardSet"."id" FROM "public"."CardSet" WHERE "public"."CardSet"."steamAccountId" = $1;
DELETE FROM "public"."CardSet" WHERE "public"."CardSet"."id" IN ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,...);

The following seems more efficient to me:

DELETE FROM "public"."CardSet" WHERE "public"."CardSet"."steamAccountId" = $1;
Tim
  • 231
  • 1
  • 3
  • 6

1 Answers1

1

I can't comment on what technical decisions led the Prisma library authors to translate a deleteMany operation into two separate SQL statements.

However, ORMs (Prisma and others) in general don't always generate perfectly efficient SQL queries. In many cases, it is definitely possible to write more optimal queries direclty in SQL. This is a tradeoff you have to be mindful about when using ORMs.

If your usecase really needs this deleteMany operationg to be as efficient as possible, you could cosider using Prisma's $queryRaw feature to directly write a more efficient SQL query. You can find more information in the Raw database access article of Prisma docs.

In my opinion, unless you're sure that this that a manual SQL query would really improve your performance in some meaningful way, I would not bother for this particular case.

Tasin Ishmam
  • 5,670
  • 1
  • 23
  • 28
  • 1
    I am well aware of ORMs not always being ideal, but this case struck out to me since it seemed so easy to optimize. I indeed ended up using `$queryRaw`. I was using `deleteMany` to truncate a table, so a manual `TRUNCATE` query was much faster. I ended up also asking this question on Prisma's GitHub discussion, and received the following answer as to why `deleteMany` uses two queries: https://github.com/prisma/prisma/discussions/8211#discussioncomment-993163 – Tim Jul 12 '21 at 15:23
  • 1
    In retrospect, my response was possibly overly simplistic. Apologies about that and thanks for the link describing the very specific reason behind the issue! – Tasin Ishmam Jul 12 '21 at 16:34