Schema size affects runtime speed

[DarioSiroki]

Bug description

After migration from 3.15.2 to 4.0.0, my production server has started running out of memory during syncs between 2 databases which occur every 45 seconds. The provided client code is a simplified version of the production code.

Test code is provided below. It builds 500 upsert queries and executes them via $transaction

Prisma 4.1.1

With 201 tables in schema:

Runtime: 48.124s
Runtime: 1.610s
Runtime: 1.578s

With 1 table in schema:

Runtime: 2.181s
Runtime: 1.509s
Runtime: 1.539s

Prisma 3.15.2

With 201 tables in schema:

Runtime: 1.691s
Runtime: 1.508s
Runtime: 1.482s

With 1 table in schema:

Runtime: 1.776s
Runtime: 1.599s
Runtime: 1.479s

By the way, this also happens with prisma.table.create. I have not tried other cases.

How to reproduce

1. Use the provided client code with provided prisma schema.
2. Try with prisma 4.1.1.
3. Check runtimes and RAM/CPU usages with 1 table in schema and with 200 tables which are commented out.

There is a difference in runtime with schema size.

Expected behavior

1. The first run shouldn't be so slow and use that much RAM. It wasn't this slow in 3.15.2.
2. Number of tables inside schema shouldn't affect the query speed.

Prisma information

import { PrismaClient } from '@prisma/client'
import { v4 } from 'uuid'

const prisma = new PrismaClient()

export async function go() {
    const queries = []

    for (let i = 0; i < 500; i++) {
        const element = prisma.client.upsert({
            where: {
                id: i,
            },
            create: {
                username: v4(),
            },
            update: {
                username: v4(),
            },
        })
        queries.push(element)
    }

    console.time('Runtime')
    await prisma.$transaction(queries)
    console.timeEnd('Runtime')
}

async function main() {
    await go()
    await go()
    await go()
}

main()

Find the prisma schema at pastebin

Environment & setup

• OS: Mac OS, Amazon Linux AMI
• Database: PostgreSQL
• Node.js version: v16.15.0

Prisma Version

prisma                  : 4.1.1
@prisma/client          : 4.1.1
Current platform        : darwin-arm64
Query Engine (Node-API) : libquery-engine 8d8414deb360336e4698a65aa45a1fbaf1ce13d8 (at node_modules/@prisma/engines/libquery_engine-darwin-arm64.dylib.node)
Migration Engine        : migration-engine-cli 8d8414deb360336e4698a65aa45a1fbaf1ce13d8 (at node_modules/@prisma/engines/migration-engine-darwin-arm64)
Introspection Engine    : introspection-core 8d8414deb360336e4698a65aa45a1fbaf1ce13d8 (at node_modules/@prisma/engines/introspection-engine-darwin-arm64)
Format Binary           : prisma-fmt 8d8414deb360336e4698a65aa45a1fbaf1ce13d8 (at node_modules/@prisma/engines/prisma-fmt-darwin-arm64)
Default Engines Hash    : 8d8414deb360336e4698a65aa45a1fbaf1ce13d8
Studio                  : 0.469.0

[SevInf]

I was able to reproduce and fix it. Here is what I am getting after the fix:

Runtime: 4.127s
Runtime: 1.912s
Runtime: 1.825s

First call is still around 2 times slower than subsequent ones, but not to the same degree as reported here. Reason why it's not quite back to the times it was before is that in 3.x we've bundled DMMF with generated client, where in 4.x we get it at runtime.

[SevInf]

Update:
DMMF turned out to be red herring, after shifting couple things around, got it to roughly 3.x levels:

Runtime: 1.863s
Runtime: 1.756s
Runtime: 1.756s

The fix won't make it into 4.2.0 release, but will be published in 4.2.1 shortly afterwards. @DarioSiroki thanks for the report and great reproduction!

[DarioSiroki]

No problem, thanks for the quick fix!

[SevInf]

Released in 4.2.1