Prisma•4d ago

Raw SQL returns ~5x slower than CLI / `pg` package

I have a query that returns 170k rows of two 4-byte integer (so ~1.5MB total). When I run this on the CLI (pgsql), I get a ~500ms runtime. Using queryRawUnsafe or queryRaw. the query is 2-3 secs. I even hooked up the pg package and confirmed it is around ~500ms. Whu could Prisma be adding this overhead? Is there anything I could do to minimize it? Some other info: - I am running this in a serialized transaction; I see no perf difference when without a transaction - the query basically looks like this:

SELECT 
  image_id,
  label_id
FROM labels_images li
INNER JOIN images i 
ON i.id = li.image_id
WHERE i.customer_id = <number>

SELECT 
  image_id,
  label_id
FROM labels_images li
INNER JOIN images i 
ON i.id = li.image_id
WHERE i.customer_id = <number>

(where image_id and label_id are 4 byte ints) - but, again, running this same command via pg or psql is ~5x faster than the Prisma options...

5 Replies

Julian IncrEdelmanOP•4d ago

could this be related? https://github.com/prisma/prisma/issues/23573

GitHub

2x worse performance when running raw queries in Prisma vs pg · I...

Bug description We've been having trouble with bad performance with our database queries. We have spent multiple weeks on this issue on implementing tracing, checking Postgres performance, rewr...

Julian IncrEdelmanOP•4d ago

I am on Prisma 5.22

Nurul•2d ago

Hello @Julian IncrEdelman The GitHub Issue you have linked is related. There is indeed some overhead involved even while using Raw Queries with Prisma. We are working on a new prototype which should make queries alot faster. I don't have a timeline yet which I can share with you.

Julian IncrEdelmanOP•2d ago

Ok. As a work around I am using the pg library. Is there some way to use the same transaction / connection for queries across the two libraries?

Nurul•2d ago

I don't think it's possible. I believe each library manages their own connection pool so sharing a same database connection between both libraries might be tricky

Gaming

Programming

Raw SQL returns ~5x slower than CLI / `pg` package