prisma-engines icon indicating copy to clipboard operation
prisma-engines copied to clipboard
verified publisher icon prisma

fix(sqlite): allow 32766 query params

Open Weakky opened this issue 1 year ago • 3 comments

Overview

closes https://github.com/prisma/team-orm/issues/1048

Since SQLite 3.32.0, the max bind values were upgraded from 999 to 32766. As of commiting is, we're bundling SQLite 3.41.1.

This will improve performance for all queries that had more than 999 query params.

Weakky avatar Apr 04 '24 16:04 Weakky

WASM Query Engine file Size

Engine This PR Base branch Diff
Postgres 2.079MiB 2.079MiB 0.000B
Postgres (gzip) 821.404KiB 821.404KiB 0.000B
Mysql 2.050MiB 2.050MiB 0.000B
Mysql (gzip) 808.541KiB 808.542KiB -1.000B
Sqlite 1.945MiB 1.945MiB 22.000B
Sqlite (gzip) 769.440KiB 769.427KiB 13.000B

github-actions[bot] avatar Apr 04 '24 16:04 github-actions[bot]

CodSpeed Performance Report

Merging #4810 will not alter performance

Comparing fix/sqlite_max_bind_values (82cfce5) with main (fc73af6)

Summary

✅ 11 untouched benchmarks

codspeed-hq[bot] avatar Apr 04 '24 16:04 codspeed-hq[bot]

✅ WASM query-engine performance won't change substantially (0.998x)

Full benchmark report 
DATABASE_URL="postgresql://postgres:postgres@localhost:5432/bench?schema=imdb_bench&sslmode=disable" \
node --experimental-wasm-modules query-engine/driver-adapters/executor/dist/bench.mjs
"
"
"
"
"
"
"
"
"
"
"
"
"
"
"
"
"
"
"
"
"
"
"
"
"
"
"
"
"
"
"
"
"
"
"
"
cpu: AMD EPYC 7763 64-Core Processor
runtime: node v18.19.1 (x64-linux)

benchmark                   time (avg)             (min … max)       p75       p99      p999
-------------------------------------------------------------- -----------------------------
• movies.findMany() (all - ~50K)
-------------------------------------------------------------- -----------------------------
Web Assembly: Baseline     368 ms/iter       (364 ms … 374 ms)    372 ms    374 ms    374 ms
Web Assembly: Latest       453 ms/iter       (451 ms … 459 ms)    454 ms    459 ms    459 ms
Web Assembly: Current      452 ms/iter       (450 ms … 457 ms)    454 ms    457 ms    457 ms
Node API: Current          197 ms/iter       (193 ms … 210 ms)    200 ms    210 ms    210 ms

summary for movies.findMany() (all - ~50K)
  Web Assembly: Current
   2.3x slower than Node API: Current
   1.23x slower than Web Assembly: Baseline
   1x faster than Web Assembly: Latest

• movies.findMany({ take: 2000 })
-------------------------------------------------------------- -----------------------------
Web Assembly: Baseline  14'820 µs/iter (14'455 µs … 18'029 µs) 14'687 µs 18'029 µs 18'029 µs
Web Assembly: Latest    19'184 µs/iter (18'057 µs … 22'515 µs) 19'154 µs 22'515 µs 22'515 µs
Web Assembly: Current   18'460 µs/iter (18'266 µs … 18'701 µs) 18'555 µs 18'701 µs 18'701 µs
Node API: Current        8'121 µs/iter   (7'909 µs … 8'379 µs)  8'223 µs  8'379 µs  8'379 µs

summary for movies.findMany({ take: 2000 })
  Web Assembly: Current
   2.27x slower than Node API: Current
   1.25x slower than Web Assembly: Baseline
   1.04x faster than Web Assembly: Latest

• movies.findMany({ where: {...}, take: 2000 })
-------------------------------------------------------------- -----------------------------
Web Assembly: Baseline   2'336 µs/iter   (2'209 µs … 3'534 µs)  2'341 µs  3'341 µs  3'534 µs
Web Assembly: Latest     2'868 µs/iter   (2'753 µs … 4'308 µs)  2'850 µs  3'614 µs  4'308 µs
Web Assembly: Current    2'853 µs/iter   (2'750 µs … 3'597 µs)  2'848 µs  3'444 µs  3'597 µs
Node API: Current        1'421 µs/iter   (1'338 µs … 1'924 µs)  1'424 µs  1'760 µs  1'924 µs

summary for movies.findMany({ where: {...}, take: 2000 })
  Web Assembly: Current
   2.01x slower than Node API: Current
   1.22x slower than Web Assembly: Baseline
   1.01x faster than Web Assembly: Latest

• movies.findMany({ include: { cast: true } take: 2000 }) (m2m)
-------------------------------------------------------------- -----------------------------
Web Assembly: Baseline     575 ms/iter       (570 ms … 579 ms)    578 ms    579 ms    579 ms
Web Assembly: Latest       749 ms/iter       (747 ms … 754 ms)    752 ms    754 ms    754 ms
Web Assembly: Current      759 ms/iter       (750 ms … 772 ms)    769 ms    772 ms    772 ms
Node API: Current          467 ms/iter       (459 ms … 477 ms)    477 ms    477 ms    477 ms

summary for movies.findMany({ include: { cast: true } take: 2000 }) (m2m)
  Web Assembly: Current
   1.63x slower than Node API: Current
   1.32x slower than Web Assembly: Baseline
   1.01x slower than Web Assembly: Latest

• movies.findMany({ where: {...}, include: { cast: true } take: 2000 }) (m2m)
-------------------------------------------------------------- -----------------------------
Web Assembly: Baseline  79'980 µs/iter (79'437 µs … 80'432 µs) 80'229 µs 80'432 µs 80'432 µs
Web Assembly: Latest       106 ms/iter       (105 ms … 107 ms)    106 ms    107 ms    107 ms
Web Assembly: Current      107 ms/iter       (106 ms … 108 ms)    107 ms    108 ms    108 ms
Node API: Current       62'716 µs/iter (61'896 µs … 63'929 µs) 63'594 µs 63'929 µs 63'929 µs

summary for movies.findMany({ where: {...}, include: { cast: true } take: 2000 }) (m2m)
  Web Assembly: Current
   1.7x slower than Node API: Current
   1.33x slower than Web Assembly: Baseline
   1.01x slower than Web Assembly: Latest

• movies.findMany({ take: 2000, include: { cast: { include: { person: true } } } })
-------------------------------------------------------------- -----------------------------
Web Assembly: Baseline   1'020 ms/iter   (1'006 ms … 1'048 ms)  1'035 ms  1'048 ms  1'048 ms
Web Assembly: Latest     1'251 ms/iter   (1'245 ms … 1'272 ms)  1'249 ms  1'272 ms  1'272 ms
Web Assembly: Current    1'255 ms/iter   (1'241 ms … 1'275 ms)  1'265 ms  1'275 ms  1'275 ms
Node API: Current          899 ms/iter       (851 ms … 929 ms)    923 ms    929 ms    929 ms

summary for movies.findMany({ take: 2000, include: { cast: { include: { person: true } } } })
  Web Assembly: Current
   1.4x slower than Node API: Current
   1.23x slower than Web Assembly: Baseline
   1x faster than Web Assembly: Latest

• movie.findMany({ where: { ... }, take: 2000, include: { cast: { include: { person: true } } } })
-------------------------------------------------------------- -----------------------------
Web Assembly: Baseline     143 ms/iter       (142 ms … 146 ms)    144 ms    146 ms    146 ms
Web Assembly: Latest       177 ms/iter       (173 ms … 186 ms)    180 ms    186 ms    186 ms
Web Assembly: Current      180 ms/iter       (178 ms … 185 ms)    180 ms    185 ms    185 ms
Node API: Current          111 ms/iter       (107 ms … 115 ms)    114 ms    115 ms    115 ms

summary for movie.findMany({ where: { ... }, take: 2000, include: { cast: { include: { person: true } } } })
  Web Assembly: Current
   1.62x slower than Node API: Current
   1.26x slower than Web Assembly: Baseline
   1.01x slower than Web Assembly: Latest

• movie.findMany({ where: { reviews: { author: { ... } }, take: 100 }) (to-many -> to-one)
-------------------------------------------------------------- -----------------------------
Web Assembly: Baseline   1'039 µs/iter     (977 µs … 1'717 µs)  1'035 µs  1'489 µs  1'717 µs
Web Assembly: Latest     1'372 µs/iter   (1'290 µs … 2'185 µs)  1'361 µs  2'126 µs  2'185 µs
Web Assembly: Current    1'378 µs/iter   (1'296 µs … 2'156 µs)  1'375 µs  2'007 µs  2'156 µs
Node API: Current          797 µs/iter     (758 µs … 1'145 µs)    812 µs    872 µs  1'145 µs

summary for movie.findMany({ where: { reviews: { author: { ... } }, take: 100 }) (to-many -> to-one)
  Web Assembly: Current
   1.73x slower than Node API: Current
   1.33x slower than Web Assembly: Baseline
   1x faster than Web Assembly: Latest

• movie.findMany({ where: { cast: { person: { ... } }, take: 100 }) (m2m -> to-one)
-------------------------------------------------------------- -----------------------------
Web Assembly: Baseline   1'030 µs/iter     (980 µs … 1'745 µs)  1'027 µs  1'683 µs  1'745 µs
Web Assembly: Latest     1'358 µs/iter   (1'297 µs … 2'195 µs)  1'358 µs  1'833 µs  2'195 µs
Web Assembly: Current    1'374 µs/iter   (1'299 µs … 2'374 µs)  1'369 µs  2'100 µs  2'374 µs
Node API: Current          801 µs/iter     (763 µs … 1'160 µs)    814 µs    857 µs  1'160 µs

summary for movie.findMany({ where: { cast: { person: { ... } }, take: 100 }) (m2m -> to-one)
  Web Assembly: Current
   1.72x slower than Node API: Current
   1.33x slower than Web Assembly: Baseline
   1.01x slower than Web Assembly: Latest

After changes in 82cfce5396f99c9048a38263454ceea20dfbfb70

github-actions[bot] avatar Apr 04 '24 16:04 github-actions[bot]