What is the reason/tradeoffs of always using a single SQL query?

[webNeat]

Hello Drizzle team,

Thank you for this good ORM.

I was reading the docs and this sentence was surprising to me:

Regardless of how many nested relations you query - Drizzle will always make exactly one SQL query to the database, it makes it extremely explicit and easy to tune performance with indexes.

In my mind, using exactly one SQL query will not be performant in all cases. In fact, I have seen some cases where doing multiple joins hurt the performance a lot. To validate that, I made this benchmark https://github.com/webNeat/sql-single-vs-multiple-queries

The results show a big difference in performance between using a single db.query.... and using multiple ones and creating the final result manually (71seconds vs 150ms)

if I made a mistake in my small benchmark, please let me know.

I want to understand the reasoning behind this choice of always using a single SQL query.

Thanks,
Amine

[AndriiSherman]

Thanks a lot for your work!
Let me check your repo so I can come back with a full answer

[AndriiSherman]

First I want to say, it's a great repo. Thanks for such detailed explanations and steps to be done. It was super helpful
I do see almost the same numbers on my machine and can't find anything you did wrong, so I guess it's something we may look into and try to solve on our end. I'll keep you updated here

[webNeat]

Thanks for your response.

I enabled queries logging in Postgres, here is the one query that is executed to get one comment with the relations:

select 
  "id", 
  "user_id", 
  "post_id", 
  "content",
  "user"::json,
  "post"::json
from (
  select
    "comments".*,
    case when count("comments_post"."id") = 0 then '[]' else json_agg(json_build_array("comments_post"."title", "comments_post"."user"::json))::text end as "post" 
  from (
    select
      "comments".*,
      case when count("comments_user"."id") = 0 then '[]' else json_agg(json_build_array("comments_user"."name"))::text end as "user" 
    from "comments"
    left join (select "comments_user".* from "users" "comments_user") "comments_user" on "comments"."user_id" = "comments_user"."id"
    group by "comments"."id", "comments"."user_id", "comments"."post_id", "comments"."content"
  ) "comments"
  left join (
    select "comments_post".* from (
      select 
        "comments_post".*,
        case when count("comments_post_user"."id") = 0 then '[]' else json_agg(json_build_array("comments_post_user"."name")) end as "user"
      from "posts" "comments_post" 
      left join (select "comments_post_user".* from "users" "comments_post_user") "comments_post_user" on "comments_post"."user_id" = "comments_post_user"."id"
      group by "comments_post"."id"
    ) "comments_post"
  ) "comments_post" on "comments"."post_id" = "comments_post"."id"
  group by "comments"."id", "comments"."user_id", "comments"."post_id", "comments"."content", "comments"."user"
) "comments"
limit 1

I am no expert in databases, but this looks over-complicated to me. I think the performance issue comes from all the intermediate results of subqueries.

[AndriiSherman]

@webNeat hey!
We've managed to fix all performance problems for each database we support. After releasing an update, we're going to make a pull request to your repository so that you can check it as well

[webNeat]

That's great, thanks for the update. Can't wait to see the improvements :)

[AndriiSherman]

@webNeat webNeat/sql-single-vs-multiple-queries#2

[DivMode]

Seems like we still need to use separate queries and join them manually if we need to use any relational queries with a where or orderby clause.