EDIT 2024-07-18

I've observed significant variations in execution times in the same schema/model, which don't seem to correlate directly with any changes in the schema or known factors. The command is intermittently running significantly faster or slower without a clear cause.

Bug description

Hello, I got a fairly large schema with ~ 201 models, and 69 views, and I was used to waiting ~ 75s to prisma generate to run, but today, I added a new model, and it suddenly went faster again, now it's generating in about 12s (17s real time)

I've added the gist with the before/after schema in a gist

There's basically jsut a few new fields and a new table:

How to reproduce

• Add the before.prisma schema
• Run prisma generate
• check time
• Add the after.schema schema
• Run prisma generate
• compare time

Expected behavior

To be slower?! Just kidding, but there's something srange somewhere.

Prisma information

schemas: https://gist.github.com/renatocron/444ded8be1efebd54d8c088eb7a07260

Environment & setup

• OS: Linux
• Database: Postgres
• Node.js version: 18
• CPU: AMD x86, cpu fixed at cpu MHz 4049.734 Ryzen 7 3700X

Prisma Version

Prisma v5.15.0

This is the perfect Friday evening issue - doesn't make any sense at all, so let's dive in 🤡

On first look I can not confirm what you are seeing:

PS C:\Users\Jan\Documents\throwaway\24529> npx prisma generate --schema before.prisma
Prisma schema loaded from before.prisma

✔ Generated Prisma Client (v5.15.0) to .\node_modules\@prisma\client in 196.35s

Start using Prisma Client in Node.js (See: https://pris.ly/d/client)

...

vs.

PS C:\Users\Jan\Documents\throwaway\24529> npx prisma generate --schema after.prisma      
Prisma schema loaded from after.prisma

✔ Generated Prisma Client (v5.15.0) to .\node_modules\@prisma\client in 201.70s

Start using Prisma Client in Node.js (See: https://pris.ly/d/client)

...

So both are pretty slow, but same amount of slow.

This was on my completely overloaded local Windows, so confirming again in a cloud Ubuntu machine:

npx prisma generate --schema before.prisma
Prisma schema loaded from before.prisma

✔ Generated Prisma Client (v5.15.0) to ./node_modules/@prisma/client in 12.05s

Start using Prisma Client in Node.js (See: https://pris.ly/d/client)
...

vs

npx prisma generate --schema after.prisma
Prisma schema loaded from after.prisma

✔ Generated Prisma Client (v5.15.0) to ./node_modules/@prisma/client in 71.71s

Start using Prisma Client in Node.js (See: https://pris.ly/d/client)

That is not like on my Windows machine, but also not what you reported before.

I ran both locally and in the cloud multiple times, and while the actual numbers slightly vary of course, the ratio is roughly the same.

I double checked that I did not misname the files:

gitpod /workspace/prisma-repro-24529 (main) $ ls -l
total 416
-rw-r--r-- 1 gitpod gitpod 207533 Jun 14 16:14 after.prisma
-rw-r--r-- 1 gitpod gitpod 205187 Jun 14 16:14 before.prisma

🙈

Great, as soon as I add some more eyes, they suddenly behave differently:

> time npx prisma generate --schema before.prisma
Prisma schema loaded from before.prisma

✔ Generated Prisma Client (v5.15.0) to ./node_modules/@prisma/client in 27.85s

Start using Prisma Client in Node.js (See: https://pris.ly/d/client)
...

real    0m35.474s
user    0m41.355s
sys     0m2.864s

> time npx prisma generate --schema after.prisma
Prisma schema loaded from after.prisma

✔ Generated Prisma Client (v5.15.0) to ./node_modules/@prisma/client in 31.33s

Start using Prisma Client in Node.js (See: https://pris.ly/d/client)
...

real    0m37.711s
user    0m46.096s
sys     0m2.755s

This only happens sometimes though, usually we have the "before is faster than after" situation from above.

Can you confirm again @renatocron that you are seeing after.prisma being generated faster than before.prisma? Thanks.

(Quick repository for simpler future reproduction: https://github.com/janpio/prisma-repro-24529)

Yep, @janpio still happening, I'm not crazy!

before md5sum: a58ac883ff2b75435a9b77b27a0cc298 prisma/schema.prisma after md5sum: 4dc76b8cafbb66fc4c84614151b8a1d7 prisma/schema.prisma

I even copied from your repo, instead of my gist (same hash, not diff in there)

I've added one more table, and ran again:

real 0m16.365s user 0m20.227s sys 0m2.258s

even faster :boom: hahaha

There original repo is https://github.com/AppCivico/smae/tree/homol but the 'after.schema' branch is not there yet, db:generate is just a script for "db:generate": "prisma generate"

Hello,

Just an update, as I saw the unconfimred flag. I got an AMD Ryzen 7 3800X 8-Core Processor server, and tested again:

• asdf install nodejs 18.19.0
• asdf global nodejs 18.19.0
• git clone https://github.com/janpio/prisma-repro-24529.git
• npm i

before.prisma

time npx prisma generate --schema before.prisma
Prisma schema loaded from before.prisma

✔ Generated Prisma Client (v5.15.0) to ./node_modules/@prisma/client in 74.97s

Start using Prisma Client in Node.js (See: https://pris.ly/d/client)
```
import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()
```
or start using Prisma Client at the edge (See: https://pris.ly/d/accelerate)
```
import { PrismaClient } from '@prisma/client/edge'
const prisma = new PrismaClient()
```

See other ways of importing Prisma Client: http://pris.ly/d/importing-client

┌─────────────────────────────────────────────────────────────┐
│  Deploying your app to serverless or edge functions?        │
│  Try Prisma Accelerate for connection pooling and caching.  │
│  https://pris.ly/cli/--accelerate                           │
└─────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────┐
│  Update available 5.15.0 -> 5.15.1                      │
│  Run the following to update                            │
│    npm i --save-dev prisma@latest                       │
│    npm i @prisma/client@latest                          │
└─────────────────────────────────────────────────────────┘

real	1m22.290s
user	1m28.576s
sys	0m2.765s

after.prisma

time npx prisma generate --schema after.prisma
Prisma schema loaded from after.prisma

✔ Generated Prisma Client (v5.15.0) to ./node_modules/@prisma/client in 19.34s

Start using Prisma Client in Node.js (See: https://pris.ly/d/client)
```
import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()
```
or start using Prisma Client at the edge (See: https://pris.ly/d/accelerate)
```
import { PrismaClient } from '@prisma/client/edge'
const prisma = new PrismaClient()
```

See other ways of importing Prisma Client: http://pris.ly/d/importing-client

┌─────────────────────────────────────────────────────────────┐
│  Deploying your app to serverless or edge functions?        │
│  Try Prisma Accelerate for connection pooling and caching.  │
│  https://pris.ly/cli/--accelerate                           │
└─────────────────────────────────────────────────────────────┘


real	0m27.968s
user	0m33.640s
sys	0m2.757s

Architecture:            x86_64
  CPU op-mode(s):        32-bit, 64-bit
  Address sizes:         43 bits physical, 48 bits virtual
  Byte Order:            Little Endian
CPU(s):                  16
  On-line CPU(s) list:   0-15
Vendor ID:               AuthenticAMD
  Model name:            AMD Ryzen 7 3800X 8-Core Processor
    CPU family:          23
    Model:               113
    Thread(s) per core:  2
    Core(s) per socket:  8
    Socket(s):           1
    Stepping:            0
    Frequency boost:     enabled
    CPU max MHz:         3900.0000
    CPU min MHz:         2200.0000
    BogoMIPS:            7799.63
    Flags:               fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx mmxext fxsr_opt pdpe1gb rdtscp lm constant_tsc rep_good nopl nonstop_tsc cpuid extd_apicid aperfmperf
                          rapl pni pclmulqdq monitor ssse3 fma cx16 sse4_1 sse4_2 x2apic movbe popcnt aes xsave avx f16c rdrand lahf_lm cmp_legacy svm extapic cr8_legacy abm sse4a misalignsse 3dnowprefetch osvw ibs skinit wdt tce topoext 
                         perfctr_core perfctr_nb bpext perfctr_llc mwaitx cpb cat_l3 cdp_l3 hw_pstate ssbd mba ibpb stibp vmmcall fsgsbase bmi1 avx2 smep bmi2 cqm rdt_a rdseed adx smap clflushopt clwb sha_ni xsaveopt xsavec xgetbv1 xsaves
                          cqm_llc cqm_occup_llc cqm_mbm_total cqm_mbm_local clzero irperf xsaveerptr rdpru wbnoinvd arat npt lbrv svm_lock nrip_save tsc_scale vmcb_clean flushbyasid decodeassists pausefilter pfthreshold avic v_vmsave_vmlo
                         ad vgif v_spec_ctrl umip rdpid overflow_recov succor smca sme sev sev_es
Virtualization features: 
  Virtualization:        AMD-V
Caches (sum of all):     
  L1d:                   256 KiB (8 instances)
  L1i:                   256 KiB (8 instances)
  L2:                    4 MiB (8 instances)
  L3:                    32 MiB (2 instances)
NUMA:                    
  NUMA node(s):          1
  NUMA node0 CPU(s):     0-15
Vulnerabilities:         
  Itlb multihit:         Not affected
  L1tf:                  Not affected
  Mds:                   Not affected
  Meltdown:              Not affected
  Mmio stale data:       Not affected
  Retbleed:              Mitigation; untrained return thunk; SMT enabled with STIBP protection
  Spec store bypass:     Mitigation; Speculative Store Bypass disabled via prctl and seccomp
  Spectre v1:            Mitigation; usercopy/swapgs barriers and __user pointer sanitization
  Spectre v2:            Mitigation; Retpolines, IBPB conditional, STIBP always-on, RSB filling, PBRSB-eIBRS Not affected
  Srbds:                 Not affected
  Tsx async abort:       Not affected

Now again, on an Intel(R) Xeon(R) E-2288G CPU @ 3.70GHz

before.prisma

time npx prisma generate --schema before.prisma 
Prisma schema loaded from before.prisma

✔ Generated Prisma Client (v5.15.0) to ./node_modules/@prisma/client in 73.22s

Start using Prisma Client in Node.js (See: https://pris.ly/d/client)
```
import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()
```
or start using Prisma Client at the edge (See: https://pris.ly/d/accelerate)
```
import { PrismaClient } from '@prisma/client/edge'
const prisma = new PrismaClient()
```

See other ways of importing Prisma Client: http://pris.ly/d/importing-client

┌─────────────────────────────────────────────────────────────┐
│  Deploying your app to serverless or edge functions?        │
│  Try Prisma Accelerate for connection pooling and caching.  │
│  https://pris.ly/cli/--accelerate                           │
└─────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────┐
│  Update available 5.15.0 -> 5.15.1                      │
│  Run the following to update                            │
│    npm i --save-dev prisma@latest                       │
│    npm i @prisma/client@latest                          │
└─────────────────────────────────────────────────────────┘

real	1m17.353s
user	1m21.976s
sys	0m1.123s

after.prisma

time npx prisma generate --schema after.prisma
Prisma schema loaded from after.prisma

✔ Generated Prisma Client (v5.15.0) to ./node_modules/@prisma/client in 12.80s

Start using Prisma Client in Node.js (See: https://pris.ly/d/client)
```
import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()
```
or start using Prisma Client at the edge (See: https://pris.ly/d/accelerate)
```
import { PrismaClient } from '@prisma/client/edge'
const prisma = new PrismaClient()
```

See other ways of importing Prisma Client: http://pris.ly/d/importing-client

┌─────────────────────────────────────────────────────────────┐
│  Deploying your app to serverless or edge functions?        │
│  Try Prisma Accelerate for connection pooling and caching.  │
│  https://pris.ly/cli/--accelerate                           │
└─────────────────────────────────────────────────────────────┘


real	0m16.985s
user	0m20.775s
sys	0m1.080s

 Architecture:            x86_64
  CPU op-mode(s):        32-bit, 64-bit
  Address sizes:         39 bits physical, 48 bits virtual
  Byte Order:            Little Endian
CPU(s):                  16
  On-line CPU(s) list:   0-15
Vendor ID:               GenuineIntel
  Model name:            Intel(R) Xeon(R) E-2288G CPU @ 3.70GHz
    CPU family:          6
    Model:               158
    Thread(s) per core:  2
    Core(s) per socket:  8
    Socket(s):           1
    Stepping:            13
    CPU max MHz:         5000.0000
    CPU min MHz:         800.0000
    BogoMIPS:            7399.70
    Flags:               fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx pdpe1gb rdtscp lm constant_tsc art arch_perfmon pebs bts rep_good nopl xtopology n
                         onstop_tsc cpuid aperfmperf pni pclmulqdq dtes64 monitor ds_cpl vmx smx est tm2 ssse3 sdbg fma cx16 xtpr pdcm pcid sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand lahf_lm abm 3dnowpr
                         efetch cpuid_fault epb invpcid_single ssbd ibrs ibpb stibp ibrs_enhanced tpr_shadow vnmi flexpriority ept vpid ept_ad fsgsbase tsc_adjust bmi1 avx2 smep bmi2 erms invpcid mpx rdseed adx smap clflushopt intel_pt xs
                         aveopt xsavec xgetbv1 xsaves dtherm ida arat pln pts hwp hwp_notify hwp_act_window hwp_epp md_clear flush_l1d arch_capabilities
Virtualization features: 
  Virtualization:        VT-x
Caches (sum of all):     
  L1d:                   256 KiB (8 instances)
  L1i:                   256 KiB (8 instances)
  L2:                    2 MiB (8 instances)
  L3:                    16 MiB (1 instance)
NUMA:                    
  NUMA node(s):          1
  NUMA node0 CPU(s):     0-15
Vulnerabilities:         
  Itlb multihit:         KVM: Mitigation: VMX disabled
  L1tf:                  Not affected
  Mds:                   Not affected
  Meltdown:              Not affected
  Mmio stale data:       Mitigation; Clear CPU buffers; SMT vulnerable
  Retbleed:              Mitigation; Enhanced IBRS
  Spec store bypass:     Mitigation; Speculative Store Bypass disabled via prctl and seccomp
  Spectre v1:            Mitigation; usercopy/swapgs barriers and __user pointer sanitization
  Spectre v2:            Mitigation; Enhanced IBRS, IBPB conditional, RSB filling, PBRSB-eIBRS SW sequence
  Srbds:                 Mitigation; Microcode
  Tsx async abort:       Mitigation; TSX disabled

From your results, the only strange thing is that your cloud Ubuntu machine got the results the inverted (after is the ~ 73.22s like mine before is on AMD or Intel), which make things even stranger.

What is the node version and hardware specs? Is this even remotely possible to affect this (as I understand, the JS part of prisma is mostly a bind to the rust lib)

I got some news. I've added 5 schemas to https://github.com/renatocron/prisma-repro-24529

Each one of them, has few tables, in just one table. The time really increases when I added a new FK to my "user" table (that already has many incoming FKs) but for each table I usually include 3 fks (created by, updated by, removed by), the slow down really increase when I added the second FK

If i add a empty field, from the user table ("pessoa") back to the new table, the generate time goes back again to ~20s.

before.prisma 11.72s (this has just the new table, no FK's) after-1.prisma 11.64s (this has the new table, with new fks, but no FK to Pessoa) after-2.prisma 20.83s (new FK to pessoa, "criador") after-3.prisma 63.40s (new FK to pessoa, "atualizador") after-4.prisma 56.17s after-5.prisma 13.48s

diff from after-1 to after-2 (9 secs increase)

diff from after-2 to after-3 (42 secs increase)

Hope this helps to understand what's is the issue

Just another update, I've keep working and adding new tables with more refences to ours users table, it keep going up/down randomly (but consistently over the same schema, no matter what I change in the file order, naming, etc)

Files after-6-just-new-tables.prisma and after-7-one-more-table.prisma added to the repo.

Hello, me again.

Updated and running tests again with v5.17.0

after-7-one-more-table.prisma @ v5.15.1 13.62s, real 0m18.500s @ v5.16.1 58.80s, real 1m3.986s in first ran, then I ran again, 13.55s in the second time ¹ @ v5.17.0 13.42s, real 0m18.602s

No significantly changes here, alright.

Now in a slow version:

after-6-just-new-tables.prisma @ v5.15.1 72.93s, real 1m18.106s @ v5.16.1 77.36s, 1m22.557s @ v5.17.0 76.08s, real 1m21.036s

No still no significantly changes here.

In my current version of the schema, the history is a little different (testing from v5.16.1 => v5.17.0):

[email protected] @ v5.16.1, 14.71s real 0m20.027s @ v5.17.0 27.10s, real 0m32.845s

¹: I'm running the tests with CPU Frequency locked @ 4050.000

The processes only uses 1 core, not particularly memory intensive

Edit 2024-07-18 - Variations in the same schema

As I noticed this variation in the same during migration from 5.16 to 5.17, I wrote a script to keep runing the prisma generate continually in the background and left it running while I was not using the computer.

With prisma 5.17.0, top graph is after-7-one-more-table.prisma, lower is after-6-just-new-tables.prisma

Prisma 5.16.1:

Link: https://docs.google.com/spreadsheets/d/1shbMC-o2W4Fx9NkZfk822L6IRDlA1aqhpQ0-PJNrLf4/edit?usp=sharing

Hi @janpio I know you've been busy, but can you reevaluate the tags? Is there anything I can do to confirm this bug?

Doc updated on tab "Prisma 5.15.1 node v18.19.0" with execution time's for a entire night compareing 2 verions of schema.

Doc updated on tab "Prisma 5.18.0 node v22.5.1" - same schema as previous, just changed the node+prisma version. Schema 7 is slower (baseline went from 20 to 50) but still bounce arround a bit. Schema 8 has the most variance, sometimes it ran a lot faster (150 to 25 seconds) and decreased the peak to 125s, but mostly stays at ~75s~ 23s, 20% of time at 75s)

• Left running for a full weeking on Prisma 5.18.0 node v18.19.0 Had 4 executions where the it came from 148 seconds to 36s ~ 37s for the same file, same version etc.

Comparing results for same prisma version (5.18.0) to different versions of node seens to matter, as node v.22 performed a lot different from this, where 70% time of execution was less than 23 seconds for schema 8

Performance Analysis Update: Foreign Key Impact on prisma generate

Key Finding: User Table Foreign Key Bottleneck

I've identified a significant correlation between foreign key relationships to the user table and prisma generate performance. The crucial discovery is that removing user-related FKs (created_by, updated_by, deleted_by) dramatically improves generation time.

Test Setup

• Created strip-fk.pl to systematically remove relations
• Database structure:
  • 233 models
  • 241 foreign keys referencing the user table
• Test schemas:
  • Original schema
  • Reduced schema (user FKs removed)

Benchmark Results

Prisma Version	Node Version	Original Schema	Reduced Schema	Improvement
v5.20.0	v18.19.0	149.49s	4.17s	35.85x faster
v5.20.0	v20.17.0	72.04s	3.09s	23.31x faster
v5.21.1	v20.17.0	18.07s ~ 101.24s	3.18s	5.68x ~ 31.84x faster
v5.21.1	v18.19.0	140.61s	4.03s	34.89x faster

Key Observations

1. Node.js Version Impact

   • Node v20.17.0 significantly outperforms v18.19.0
   • Same Prisma version (v5.20.0) shows ~50% improvement with newer Node

2. Prisma Version Improvements

   • v5.21.1 shows inconsistent performance with Node v20.17.0
   • Times vary significantly (18.07s ~ 101.24s) for identical schema
   • Previous version (v5.20.0) shows more consistent but generally slower times

3. Foreign Key Impact

   • Removing user table FKs consistently improves performance by 23x to 35x
   • Performance gain is dramatic regardless of Node.js or Prisma version
   • Reduced schema maintains consistent performance (~3-4s) across all configurations

Questions for the Prisma Team

1. Is the dramatic performance impact of multiple FKs to the same table an expected behavior? This is crucial to know, as if this is a known issue, I could validate the FK's on insert/update by triggers, and maybe use prisma extensions to load the data back without modifying too much the fetch data.

2. Are there plans to optimize the handling of frequently referenced tables (like user tables)?

3. Could this be related to how Prisma resolves circular dependencies during code generation?

4. What's the recommended approach for schemas with many audit/tracking fields (created_by, updated_by, etc.)?

Reproducibility

All test files and scripts are available in the reproduction repository. The results are consistently reproducible across multiple runs, tests done on 3700X 8-Core locked t 4049.871 MHz

^ image of inconsistencies even in consequent runs

So any updates on this? @renatocron gave a thorough reproduction and the final answer was the dev removing itself from the issue.