Why does deleteMany use 2 queries?

user_14600972 0
Viewed 344

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;
prisma2 prisma
1 Answers

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.

user_88558440
Related