[MongoDB] Performance issue in findMany() query execution with in

[ayush-cbh]

Bug description

I have a nextJS project and I am using Prisma as an ORM for mongoDB.
I need to execute findMany for a collection having 1 million documents.

Performance change:
When I try to execute the same aggregation in mongoDB compass it takes less than 0.5 sec
while with prisma it takes around 20 sec to get the result.

How to reproduce

Create a collection with 1 million documents.
Search 5 document through id with the help of findMany()
It will take around 20 sec to execute the aggregation.

Expected behavior

It should return asked documents in (0.5 - 1 ) sec.

Prisma information

model collection_name {
   id                       String                  @id @default(auto()) @map("_id") @db.ObjectId
  name                  String?
  age                     Float?
  url                       String?
}

this.prismaClient.collection_name.findMany({
      where: { id: { in: [id1, id2, id3] } }
    })

Environment & setup

• OS: macOS
• Database: MongoDB
• Node.js version: v16.17.1

Prisma Version

"prisma": "^3.12.0"

[Jolg42]

@ayush-cbh Do you see the same problem with a more recent version of Prisma? If you could try >4 that would be interesting, the latest is 4.5.0.

[paulrostorp]

this is a duplicate of #14871 and #12793

[paulrostorp]

@ayush-cbh see #15156 (comment)

[janpio]

@ayush-cbh It's been a while, but can you maybe provide both the aggregation you run manually in MongoDB compass and the command that Prisma generates? If those are identical, then this would mean this is not a case of the same issues that Paul linked to above and mean that we have to treat this problem separately. Thanks.

[laplab]

This performance problem is fixed in Prisma 5.14.0