Albert_Camus

5 lat temudiscuss

> If you're a software engineer, it won't make a difference, just send two queries to the DB and combine the results before sending them off.

This isn't true if you have certain use cases in your application, pagination being one of them. There's no simple way to implement pagination when you have two or more queries that return and unknown number of results and you're tasked with maintaining a consistent order across page changes.

If you make a single query do all the work, it's easy to implement paging in any number of ways, the most performant being to filter by the last id the client saw. If your users aren't likely to paginate too far into the data, LIMIT and OFFSET will work fine as well.

Albert_Camus

5 lat temudiscuss

In the past we worked on a system that used MySQL 8. We used UNION (not UNION ALL, but I assume it doesn't matter) in several places, applying it to improve performance as we described in the article. There were definitely cases in the system where one side of the UNION would return zero rows, but we never ran into any of the types of issues you're describing.

Albert_Camus

5 lat temudiscuss

Author of the original article here. Temporary tables are different than using WITH (which are common table expressions, or CTEs). In many database engines, can make a temporary table that will persist for a single session. The syntax is the same as table creation, it just starts with CREATE TEMPORARY TABLE ....

More info in the PostgreSQL docs [1]:

> If specified, the table is created as a temporary table. Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction (see ON COMMIT below). Existing permanent tables with the same name are not visible to the current session while the temporary table exists, unless they are referenced with schema-qualified names. Any indexes created on a temporary table are automatically temporary as well.

[1]: https://www.postgresql.org/docs/13/sql-createtable.html

Albert_Camus

5 lat temudiscuss

Author here. This doesn't give the correct results. It produces meal_items that have both customer_id and employee_id. Here's an excerpt (the full result set is thousands of rows, as opposed to the expected 45):

    id |label       |price|employee_id|customer_id|
    ---|------------|-----|-----------|-----------|
    ...
    344|Poke        | 4.18|       3772|      13204|
    344|Poke        | 4.18|       3313|      13204|
    344|Poke        | 4.18|       2320|      13204|
    344|Poke        | 4.18|        632|      13204|
    344|Poke        | 4.18|       4264|      13204|
    344|Poke        | 4.18|        699|      13204|
    344|Poke        | 4.18|       1070|      13204|
    344|Poke        | 4.18|       3022|      13204|
    344|Poke        | 4.18|       1501|      13204|
    344|Poke        | 4.18|        808|      13204|
    344|Poke        | 4.18|       2793|      13204|
    344|Poke        | 4.18|       1660|      13204|
    344|Poke        | 4.18|        932|      13204|
    ...

To be clear, there are ways to write this query without UNION that have both good performance and give the correct results, but they're very fiddly and harder to reason about that just writing the two comparatively simple queries and then mashing the results together.

Albert_Camus

5 lat temudiscuss

Author here, you are indeed correct that Query #2's final join can be an INNER join. However, I just tested it against our test data set and it makes no impact on the performance.

Albert_Camus

6 lat temudiscuss

I don't think it's holding SPAs to a higher standard. It's holding SPAs to a reasonable expectation for SPAs. An SPA is significantly more complicated on the client side in exchange for some purported benefits, especially to the user (such as only downloading what you need). If everything is discarded on each "page" transition and re-downloaded again upon a return, and has the page-popping-into-place effect, that eliminates one of the foremost benefits of an SPA and diminishes the UX.

I encourage anyone developing an SPA to go talk to actual end users about this. Most will report that they don't like the experience, often citing a variety of issues, but one of the biggest ones is related to the immediate page transitions followed by blank boxes or loading spinners for a perceptible amount of time. You'll hear comments like "it feels too fast" or "it feels like nothing is happening". These are not good things, and developers shouldn't be clutching their React/Angular/Vue/whatever framework just because it's what they know how to develop in, at the cost of producing products that users don't like as much as normal server side rendered applications.

Albert_Camus

9 lat temudiscuss

>And it's a breeze to figure out and read the code too: even when it's 10s of thousands of lines of code.

Not at all. The issue with an application written in jQuery is a lack of sane state management. Forgetting to initialize (or re-initialize) values, not expecting things to be executed in a different order, and just poor organization in general led to mountains of runtime errors.

Nowadays we use Elm. The difference is night and day. I wrote a post on this topic a few months ago: https://charukiewi.cz/posts/elm/

Albert_Camus

9 lat temudiscuss

> I care only about properly working, easy to develop and maintain and wildly used so I can hire for. Elm is none of these at the moment, but React is.

As a CTO and the author of the OP, I can say with confidence this statement is false. Elm works correctly, is easy to develop in, and is easy to train developers that have experience in other languages to write Elm.

Albert_Camus

9 lat temudiscuss

Author here. As I posted in a different reply:

JSON decoding is hard relative to what it is like in JavaScript. In your JS code you can just call JSON.parse() and get the corresponding JavaScript object.

In Elm, decoding is not nearly as easy as it is in JS because every field must be explicitly converted to an Elm value. Depending on the complexity of your conversion from JSON to Elm value (e.g. whether you are just decoding to primitive values or to custom types defined in your program), there may also be a bit of a learning curve.

As I stated in the post, there is a benefit in doing all of this: your Elm application will effectively type-check your JSON and reject it if it is malformed.

Albert_Camus

9 lat temudiscuss

Author here. JSON decoding is hard relative to what it is like in JavaScript. In your JS code you can just call JSON.parse() and get the corresponding JavaScript object.

In Elm, decoding is not nearly as easy as it is in JS because every field must be explicitly converted to an Elm value. Depending on the complexity of your conversion from JSON to Elm value (e.g. whether you are just decoding to primitive values or to custom types defined in your program), there may also be a bit of a learning curve.

As I stated in the post, there is a benefit in doing all of this: your Elm application will effectively type-check your JSON and reject it if it is malformed.

Albert_Camus

11 lat temudiscuss

Did you read the post at all? That's exactly the conclusion the post comes to.

Albert_Camus

Submissions

Speeding up SQL queries by orders of magnitude using UNION

comments