4 ms·

> It's true that Prisma currently doesn't do JOINs for relational queries. Instead, it sends individual queries and joins the data on the application level. ..

by etblg 1y ago

> It's true that Prisma currently doesn't do JOINs for relational queries. Instead, it sends individual queries and joins the data on the application level. ..........I'm sorry, what? That seems........absurd. edit: Might as well throw in: I can't stand ORMs, I don't get why people use it, please just write the SQL.

compton93 1y ago
It is. But wait... it doesn't join the data on the application level of your application. You have to deploy their proxy service which joins the data on the application level.
- Tadpole9181 1y ago
  It's pretty obvious when somebody has only heard of Prisma, but never used it. - Using `JOIN`s (with correlated subqueries and JSON) has been around for a while now via a `relationLoadStrategy` setting. - Prisma has a Rust service that does query execution & result aggregation, but this is automatically managed behind the scenes. All you do is run `npx prisma generate` and then run your application. - They are in the process of removing the Rust layer. The JOIN setting and the removing of the middleware service are going to be defaults soon, they're just in preview.
  - compton93 1y ago
    They've been saying that for 3 years. We actually had a discount for being an early adopter. But hey its obvious Ive never used it and only heard of it.
    - Tadpole9181 1y ago
      The JOIN mode has been in preview for over a year and is slated for GA release within a few months. Which has been on their roadmap. The removal of the rust service is available in preview for Postgres as of 6.7.[1] Rewriting significant parts of a complex codebase used by millions is hard, and pushing it to defaults requires prolonged testing periods when the worst case is "major data corruption". [1]: https://www.prisma.io/blog/try-the-new-rust-free-version-of-prisma-orm-early-access https://www.prisma.io/blog/try-the-new-rust-free-version-of-...
      - paulddraper 1y ago
        It is hard. Harder than just doing joins.
      - compton93 1y ago
        They've had flags and work arounds for ages. Not sure what point you are trying to make? But like you said I've never used it, only heard of it lol.
      - williamdclt 1y ago
        Honestly, everything you say makes me want to stay far from prisma _more_. All this complexity, additional abstractions and indirections, with all the bugs gootguns and gotchas that come with it... when I could just type "JOIN" instead.
        Tadpole9181 1y ago
        Okay? It's one setting that will be the default in 2 months. And you could always write type-safe SQL manually instead. i greatly envy y'all having projects where the biggest complexity is... A single setting once, that's clearly documented. We live in very different worlds, apparently.
jjice 1y ago
I believe it’s either released now or at least a feature flag (maybe only some systems). It’s absolutely absurd it took so long. I can’t believe it wasn’t the initial implementation. Funny relevant story: we got an OOM from a query that we used Prisma for. I looked into it - it’s was a simple select distinct. Turns out (I believe it was changed like a year ago, but I’m not positive), event distincts were done in memory! I can’t fathom the decision making there…
- etblg 1y ago
  > event distincts were done in memory! I can’t fathom the decision making there… This is one of those situations where I can't tell if they're operating on some kind of deep insight that is way above my experience and I just don't understand it, or if they just made really bad decisions. I just don't get it, it feels so wrong.
  - Tadpole9181 1y ago
    > I can't tell if they're operating on some kind of deep insight that is way above my experience and I just don't understand it This is answered at the very top of the link on the post you replied to. In no unclear language, no less. Direct link here: https://github.com/prisma/prisma/discussions/19748#discussioncomment-6171725 https://github.com/prisma/prisma/discussions/19748#discussio... > I want to elaborate a bit on the tradeoffs of this decision. The reason Prisma uses this strategy is because in a lot of real-world applications with large datasets, DB-level JOINs can become quite expensive... > The total cost of executing a complex join is often higher than executing multiple simpler queries. This is why the Prisma query engine currently defaults to multiple simple queries in order to optimise overall throughput of the system. > But Prisma is designed towards generalized best practices, and in the "real world" with huge tables and hundreds of fields, single queries are not the best approach... > All that being said, there are of course scenarios where JOINs are a lot more performance than sending individual queries. We know this and that's why we are currently working on enabling JOINs in Prisma Client queries as well You can follow the development on the roadmap. Though this isn't a complete answer still. Part of it is that Prisma was, at its start, a GraphQL-centric ORM. This comes with its own performance pitfalls, and decomposing joins into separate subqueries with aggregation helped avoid them.
pier25 1y ago
> I can't stand ORMs, I don't get why people use it, please just write the SQL. I used to agree until I started using a good ORM. Entity Framework on .NET is amazing.
- tilne 1y ago
  Doesn’t entity framework have a huge memory footprint too?
  - neonsunset 1y ago
    Do you have any links that note memory usage issues with any of the semi-recent EF Core versions?
    - tilne 1y ago
      No. To be clear: I wasn’t trying to say it was bad. Just repeating what I had read in a (fairly old) .net book. Should have chosen my words more carefully.
      - neonsunset 1y ago
        To be fair, old Entity Framework was on the heavier side. Still much faster than e.g. ActiveRecord but enough for Dapper to be made back then. The gap between them is mostly gone nowadays plus .NET itself has become massively faster and alternate more efficient alternatives got introduced since (Dapper AOT, its main goal is NAOT compatibility but it also uses the opportunity to further streamline the implementation).
        tilne 1y ago
        Thanks for the context. I’m new to the ecosystem, so it’s valuable to hear thoughts like these from people with more experience with it.
  - homebrewer 1y ago
    If you don't do stupid things like requesting everything from the database and then filtering data client side, then no. We have one application built on .NET 8 (and contemporary EF) with about 2000 tables, and its memory usage is okay. The one problem it has is startup time: EF takes about a minute of 100% CPU load to initialize on every application restart, before it passes execution to the rest of your program. Maybe it is solvable, maybe not, I haven't yet had the time to look into it.
ketzo 1y ago
Not 100% parallel, but I was debugging a slow endpoint earlier today in our app which uses Mongo/mongoose. I removed a $lookup (the mongodb JOIN equivalent) and replaced it with, as Prisma does, two table lookups and an in-memory join p90 response times dropped from 35 seconds to 1.2 seconds
- nop_slide 1y ago
  Maybe because mongo isn’t ideal for relational data?
  - wredcoll 1y ago
    Does mongodb optimize joins at all? Do they even happen server side?
  - merek 1y ago
    I believe a lot of Mongo's criticisms come from people modelling highly relational data on a non-relational DB.
    - rwyinuse 1y ago
      I'm not sure what is the point of using MongoDB these days, when you can as easily store and query jsonb in postgres.
- lelanthran 1y ago
  > I removed a $lookup (the mongodb JOIN equivalent) There is no "MongoDB JOIN equivalent" because MongoDB is not a relationalal database. It's like calling "retrieve table results sequentially using previous table's result-set" a JOIN; it's not.
lesuorac 1y ago
Can't speak about Prisma (or Postgres much). But I've found with that you can get better performance in _few_ situations with application level joins than SQL joins when the SQL join is causing a table lock and therefore rather than slower parallel application joins you have sequential MySQL joins. (The lock also prevents other parallel DB queries which is generally the bigger deal than if this endpoint is faster or not). Although I do reach for the SQL join first but if something is slow then metrics and optimization is necessary.
- hermanradtke 1y ago
  In what cases is your join causing a table lock?

by etblg 1y ago

compton93 1y ago

It is. But wait... it doesn't join the data on the application level of your application. You have to deploy their proxy service which joins the data on the application level.

Tadpole9181 1y ago
It's pretty obvious when somebody has only heard of Prisma, but never used it. - Using `JOIN`s (with correlated subqueries and JSON) has been around for a while now via a `relationLoadStrategy` setting. - Prisma has a Rust service that does query execution & result aggregation, but this is automatically managed behind the scenes. All you do is run `npx prisma generate` and then run your application. - They are in the process of removing the Rust layer. The JOIN setting and the removing of the middleware service are going to be defaults soon, they're just in preview.
- compton93 1y ago
  They've been saying that for 3 years. We actually had a discount for being an early adopter. But hey its obvious Ive never used it and only heard of it.
  - Tadpole9181 1y ago
    The JOIN mode has been in preview for over a year and is slated for GA release within a few months. Which has been on their roadmap. The removal of the rust service is available in preview for Postgres as of 6.7.[1] Rewriting significant parts of a complex codebase used by millions is hard, and pushing it to defaults requires prolonged testing periods when the worst case is "major data corruption". [1]: https://www.prisma.io/blog/try-the-new-rust-free-version-of-prisma-orm-early-access https://www.prisma.io/blog/try-the-new-rust-free-version-of-...
    - paulddraper 1y ago
      It is hard. Harder than just doing joins.
    - compton93 1y ago
      They've had flags and work arounds for ages. Not sure what point you are trying to make? But like you said I've never used it, only heard of it lol.
    - williamdclt 1y ago
      Honestly, everything you say makes me want to stay far from prisma _more_. All this complexity, additional abstractions and indirections, with all the bugs gootguns and gotchas that come with it... when I could just type "JOIN" instead.
      - Tadpole9181 1y ago
        Okay? It's one setting that will be the default in 2 months. And you could always write type-safe SQL manually instead. i greatly envy y'all having projects where the biggest complexity is... A single setting once, that's clearly documented. We live in very different worlds, apparently.

jjice 1y ago

I believe it’s either released now or at least a feature flag (maybe only some systems). It’s absolutely absurd it took so long. I can’t believe it wasn’t the initial implementation. Funny relevant story: we got an OOM from a query that we used Prisma for. I looked into it - it’s was a simple select distinct. Turns out (I believe it was changed like a year ago, but I’m not positive), event distincts were done in memory! I can’t fathom the decision making there…

etblg 1y ago
> event distincts were done in memory! I can’t fathom the decision making there… This is one of those situations where I can't tell if they're operating on some kind of deep insight that is way above my experience and I just don't understand it, or if they just made really bad decisions. I just don't get it, it feels so wrong.
- Tadpole9181 1y ago
  > I can't tell if they're operating on some kind of deep insight that is way above my experience and I just don't understand it This is answered at the very top of the link on the post you replied to. In no unclear language, no less. Direct link here: https://github.com/prisma/prisma/discussions/19748#discussioncomment-6171725 https://github.com/prisma/prisma/discussions/19748#discussio... > I want to elaborate a bit on the tradeoffs of this decision. The reason Prisma uses this strategy is because in a lot of real-world applications with large datasets, DB-level JOINs can become quite expensive... > The total cost of executing a complex join is often higher than executing multiple simpler queries. This is why the Prisma query engine currently defaults to multiple simple queries in order to optimise overall throughput of the system. > But Prisma is designed towards generalized best practices, and in the "real world" with huge tables and hundreds of fields, single queries are not the best approach... > All that being said, there are of course scenarios where JOINs are a lot more performance than sending individual queries. We know this and that's why we are currently working on enabling JOINs in Prisma Client queries as well You can follow the development on the roadmap. Though this isn't a complete answer still. Part of it is that Prisma was, at its start, a GraphQL-centric ORM. This comes with its own performance pitfalls, and decomposing joins into separate subqueries with aggregation helped avoid them.

pier25 1y ago

> I can't stand ORMs, I don't get why people use it, please just write the SQL. I used to agree until I started using a good ORM. Entity Framework on .NET is amazing.

tilne 1y ago
Doesn’t entity framework have a huge memory footprint too?
- neonsunset 1y ago
  Do you have any links that note memory usage issues with any of the semi-recent EF Core versions?
  - tilne 1y ago
    No. To be clear: I wasn’t trying to say it was bad. Just repeating what I had read in a (fairly old) .net book. Should have chosen my words more carefully.
    - neonsunset 1y ago
      To be fair, old Entity Framework was on the heavier side. Still much faster than e.g. ActiveRecord but enough for Dapper to be made back then. The gap between them is mostly gone nowadays plus .NET itself has become massively faster and alternate more efficient alternatives got introduced since (Dapper AOT, its main goal is NAOT compatibility but it also uses the opportunity to further streamline the implementation).
      - tilne 1y ago
        Thanks for the context. I’m new to the ecosystem, so it’s valuable to hear thoughts like these from people with more experience with it.
- homebrewer 1y ago
  If you don't do stupid things like requesting everything from the database and then filtering data client side, then no. We have one application built on .NET 8 (and contemporary EF) with about 2000 tables, and its memory usage is okay. The one problem it has is startup time: EF takes about a minute of 100% CPU load to initialize on every application restart, before it passes execution to the rest of your program. Maybe it is solvable, maybe not, I haven't yet had the time to look into it.

ketzo 1y ago

Not 100% parallel, but I was debugging a slow endpoint earlier today in our app which uses Mongo/mongoose. I removed a $lookup (the mongodb JOIN equivalent) and replaced it with, as Prisma does, two table lookups and an in-memory join p90 response times dropped from 35 seconds to 1.2 seconds

nop_slide 1y ago
Maybe because mongo isn’t ideal for relational data?
- wredcoll 1y ago
  Does mongodb optimize joins at all? Do they even happen server side?
- merek 1y ago
  I believe a lot of Mongo's criticisms come from people modelling highly relational data on a non-relational DB.
  - rwyinuse 1y ago
    I'm not sure what is the point of using MongoDB these days, when you can as easily store and query jsonb in postgres.
lelanthran 1y ago
> I removed a $lookup (the mongodb JOIN equivalent) There is no "MongoDB JOIN equivalent" because MongoDB is not a relationalal database. It's like calling "retrieve table results sequentially using previous table's result-set" a JOIN; it's not.

lesuorac 1y ago

Can't speak about Prisma (or Postgres much). But I've found with that you can get better performance in _few_ situations with application level joins than SQL joins when the SQL join is causing a table lock and therefore rather than slower parallel application joins you have sequential MySQL joins. (The lock also prevents other parallel DB queries which is generally the bigger deal than if this endpoint is faster or not). Although I do reach for the SQL join first but if something is slow then metrics and optimization is necessary.

hermanradtke 1y ago
In what cases is your join causing a table lock?