Use joins for populating multiple levels about joist-orm HOT 6 CLOSED

Question: Is the main issue to be solved the fact that we currently do three round trips to get the results? Or that we logically want/need 1 sql query?

For instance, if we could issue these 3 queries simultaneously, wouldn't that be just as good? (and maybe better as duplication will be eliminated from the result)

SELECT * FROM author WHERE id = '1'
SELECT * FROM books WHERE author_id IN (1);  // No join needed, author_id is on books
SELECT * FROM book_reviews JOIN books ON books.id = book_reviews.book_id WHERE book.author_id in (1);

from joist-orm.

@chr1sjf0x ah yeah, in this instance it is to avoid 3 round trips; we can't issue the queries simultaneously b/c the IN in the 2nd query depends on the result of the 1st, and then IN in the 3rd query depends on the results of the 2nd.

...speaking of simultaneous queries, I have heard murmurs that PG itself has great support for "1 connection making multiple / simultaneous in-flight queries", but that the client driver libpq only just gained this functionality, and so the pg package we use may be artificially serializing queries.

I.e. for Joist, we could send UPDATE books ..., INSERT authors ..., UPDATE book_reviews ..., basically all of our mutations, essentially all simultaneously, but I believe/assume the driver is currently serializing them.

from joist-orm.

Filed #240

from joist-orm.

we can't issue the queries simultaneously b/c the IN in the 2nd query depends on the result of the 1st, and then IN in the 3rd query depends on the results of the 2nd.

Right, but what I'm getting at is it seems like it might be simpler to still have 3 queries, where each one joins what they need to get back to the value in the IN clause.

As opposed to combining the three into 1 large query?

from joist-orm.

Ah yes! It is definitely simpler, which is why it's been implemented that way so far. :-D

That said, if you meant "simpler" as in simplicity that means "the database can execute 3 simple-but-sequential queries faster than it can do 1 complex-with-joins query", unfortunately my suspicion is that that is not true, i.e. we are missing out on perf gains by doing the 1-query-with-joins approach (b/c even if the 1-complex-with-joins query takes 10ms instead of 5ms, it's still faster than 5ms+5ms+5ms if our cost is driven mostly by inherent per-call network latency).

With some disclaimers:

1. there are probably pathological cases where the 3-join query returns significantly more data than separate queries (b/c it has to repeat the grandparent entity N times, repeat the parent entities 1 time per child, etc) such that 3 separate calls really is better (I assume this is rare...not sure...), and

2. The "must do 1-query-with-joins" has been defacto ORM best practice for quite awhile, so maybe its out-of-date, b/c the response times of modern databases is just so amazing (e.g. the ORMs of the past didn't have sub-5ms Aurora response times, so it was much more important to avoid sequential network calls).

So I'd love for that to be the case 🤞 :-) but until really digging in/having numbers, I think it's a fair/almost sure assumption that we'd get net/net better perf by using the prototypical 1-query-with-joins approach to preloading.

from joist-orm.

This is implemented but not rolled out, until I can get a chance to have metrics in place that show improvements (better/worse/no change).

from joist-orm.