Morphing this into "the number of joins is smaller so the performance is better" is just incorrect. The point of our post was to illustrate that a UNION query can simplify how your join your tables and allow you to write constituent queries that have better performance characteristics. This is not true and this belief that joins are slow leads people down the path that ends at "SQL just doesn't scale" and "let's build a complicated Redis-backed caching layer". I think someone new to SQL is likely to read your statement and think "okay joins are slow so I guess I should avoid joins". I can expand this schema to have a UNION query with two 10-table joins and it would still perform better than the 7 table query. The reason the UNION query is fast is because the query on either side effectively utilizes the indexes so that the database engine can limit the number of rows immediately, rather than filter them out after multiplying them all together. What's more important for performance of queries on larger data sets than the number of joins is that there are indexes and that the query is written in a way that can utilize indexes to avoid multiplicative slowdowns. The number of joins alone doesn't have much to do with the performance characteristics of any query. Between this and your other response, where you expound on the same point, I think you're being far too hand wavy about what causes performance issues. Haven't done many non-Dewey libraries, and hate the ones set up like bookstores where you don't have interleaved levels of sorting.Īuthor here. Seriously though, even if you throw me in an unfamiliar layout, I'll generally quickly refresh on general identifiers then do a quick jog through the Library to nail down the layout. I'll preferentially choose a library whose physical layout I'm familiar with over one I don't, but if you take that physical layout and switch around all the numbers, I'll find you, and beat you with the heaviest reference volume in the Library once I find it. Though I'll admit, I'm not sure what gives it the "stickyness" for me, the numbers, or the physical location. I've not encountered any other way of organizing information that seems intuitively mappable quite like it. Swear to God, might one day make a Dewey Decimal System-like search Engine. Given a relatively ordered cart of books to put back on shelves, and an arbitrary starting point in the library to begin restacking (imagine going to the bathroom and some student comes and moves your cart on you), once you've done it a few times, you can usually unconsciously navigate the entire data store.Ĭompare this to most Search Engines, Reddit, or most Internet forums, where you have people maliciously attacking your overall index structure in a constant conflict for top page rank, orconstantly shifting page numbers based on time elapsed since last reply/engagement sorts. I first grokked it's impact when I'd volunter to reshelve books in the library. Once a "good enough" structure is achieved, genearally someone can quickly find their way around. That's the neat thing about consistent search indexing that isn't being maliciously subverted or tweaked. Was about to say, either doc registry, or library catalog. 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.”? “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. If the author is around, what was the more realistic example behind: 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 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. If anything, I think many folks would say “whatever, make the two requests in parallel and zip them together in the application”. 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”.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |