4 ms·

Huh. Like other commenters, I’m surprised to see a multi part JOIN be the answer for “I want the results of Query A as well as the results of Query B”. If anyt

by boulos 6y ago

Huh. Like other commenters, I’m surprised to see a multi part JOIN be the answer for “I want the results of Query A as well as the results of Query B”. If anything, I think many folks would say “whatever, make the two requests in parallel and zip them together in the application”. If not, WITH expressions to make the two independent queries and then a final SELECT to merge them however you prefer would be reasonable (whether that’s a join flavor or UNION/INTERSECT/etc.), but the super explosion JOIN just doesn’t cross my mind. I think there must be a different example that they tried to anonymize for this post, otherwise they don’t need the IDs (and probably want a sum of quantity). If the author is around, what was the more realistic example behind: “In order to audit inventory, the logistics team at the corporate headquarters requests a tool that can generate a report containing all meal_items that left a given store’s inventory on a particular day. This requires a query that includes items that were both sold to customers as well as recorded as employee markouts for the specified store on the specified day.”?

IgorPartola 6y ago
Where this fail (and I don’t see the article addressing this concern) is if you want to do pagination. Say you are retrieving page 17 of 8000. Will the UNION still be performant then? After all, it will require a stable sort order that may depend on one of the branches. You certainly can’t pagination without doing a whole lot of logic in your application if you use two separate queries. My typical solution to this is to do a query not employee IDs, another one of customer IDs and then do an IN clause. Or you could do an intermediate ID of some kind that will collapse your diamond branch into a single IN.
- catmanjan 6y ago
  In my experience this is the number one driver for infinite scroll, dropping paging simplifies a lot
  - sahila 6y ago
    Having infinite scroll doesn't avoid paging results from a query. Like the OP mentioned, if you have 8000 page * results/page records, you may only want to retrieve 50 records at a time to pass back to the client. In that case, you will still need a way to query for the next set / page. Infinite scroll is a UX implementation.
    - catmanjan 6y ago
      Yes - very true, however when we used paging we found users were going "I know my results are on page 19, skip to there" but when they were faced with the task of scrolling to page 19, their behaviour changed and they used filtering
      - ajmurmann 6y ago
        Did that make the user experience worse or better?
        catmanjan 6y ago
        Haha no comment
      - OJFord 6y ago
        > "I know my results are on page 19, skip to there" What on Earth is your application where that's the case? ..A forum maybe? (Revisiting a thread looking for a particular reply I mean.)
        catmanjan 6y ago
        Document registry, but I've heard users doing this in just about every application that allows it - just doing a broad search and memorising the page that the thing they actually want is on
        rini17 6y ago
        If users often search for known things then why not optimize the engine/UI to the use case instead of removing the use case? Like, remembering the association <search term - selected results> and prominently display these?
gwbas1c 6y ago
> If anything, I think many folks would say “whatever, make the two requests in parallel and zip them together in the application” That was my first reaction. Keep in mind that high load applications have limited database connections. Running queries in parallel will be an anti-pattern in such applications.

by boulos 6y ago

IgorPartola 6y ago

Where this fail (and I don’t see the article addressing this concern) is if you want to do pagination. Say you are retrieving page 17 of 8000. Will the UNION still be performant then? After all, it will require a stable sort order that may depend on one of the branches. You certainly can’t pagination without doing a whole lot of logic in your application if you use two separate queries. My typical solution to this is to do a query not employee IDs, another one of customer IDs and then do an IN clause. Or you could do an intermediate ID of some kind that will collapse your diamond branch into a single IN.

catmanjan 6y ago
In my experience this is the number one driver for infinite scroll, dropping paging simplifies a lot
- sahila 6y ago
  Having infinite scroll doesn't avoid paging results from a query. Like the OP mentioned, if you have 8000 page * results/page records, you may only want to retrieve 50 records at a time to pass back to the client. In that case, you will still need a way to query for the next set / page. Infinite scroll is a UX implementation.
  - catmanjan 6y ago
    Yes - very true, however when we used paging we found users were going "I know my results are on page 19, skip to there" but when they were faced with the task of scrolling to page 19, their behaviour changed and they used filtering
    - ajmurmann 6y ago
      Did that make the user experience worse or better?
      - catmanjan 6y ago
        Haha no comment
    - OJFord 6y ago
      > "I know my results are on page 19, skip to there" What on Earth is your application where that's the case? ..A forum maybe? (Revisiting a thread looking for a particular reply I mean.)
      - catmanjan 6y ago
        Document registry, but I've heard users doing this in just about every application that allows it - just doing a broad search and memorising the page that the thing they actually want is on
        rini17 6y ago
        If users often search for known things then why not optimize the engine/UI to the use case instead of removing the use case? Like, remembering the association <search term - selected results> and prominently display these?

gwbas1c 6y ago

> If anything, I think many folks would say “whatever, make the two requests in parallel and zip them together in the application” That was my first reaction. Keep in mind that high load applications have limited database connections. Running queries in parallel will be an anti-pattern in such applications.