31 ms·

In the same way SQL encourages you to normalize your data for DRY representation, NoSQL encourages you to denormalize for fast retreival. I wrote about this mor

• SJC_Hacker 2y ago
  I guess materialized views are the middle ground here. I had a DB with like 30 joins and it was dog slow. Granted it was SQLite. While SQLite didn't have MATERIALIZE VIEW, what I did was CREATE TABLE AS, in effect denormalizing my heavily normalized DB.
  • zepolen 2y ago
    30 joins, immediately I'm thinking your schema was garbage. But assuming it wasn't, and you actually needed to join across thirty(!) tables (did I mention that's insane btw), it still shouldn't be a problem assuming you had the correct indexes and are querying in a sane manner.
    • maxbond 2y ago
      Maybe instead of jumping to the conclusion they were incompetent, you could ask questions about the things you find surprising. "Why did you need so many tables? 30 joins should still be doable, did you have some kind of resource constraints?" There's probably an interesting story there. I for one am curious.
      • SJC_Hacker 2y ago
        Actual count was 54. This was a baseball database. View is a "game situation" view. https://github.com/praphael/baseball/blob/main/retrosheet_init_db.py#L1274 https://github.com/praphael/baseball/blob/main/retrosheet_in... The real killer wasn't actually with this view. It was the fact it was joining with game_info_view, which itself had about 15 joins. game_info_view is what I ended up materializing. Speed was then reasonable (under 1 s) with just this step. I was trying to do everything in totally in memory (which was possible when it was just the gamelogs) so was really trying to minimize space, which required denormalization. Altogether the DB prior to the " denormalization" was about 3 GB for the entire history of Major League Baseball :-). The Retrosheet files however were < 1 GB uncompressed (about 250 MB compressed) and that. "Materializing" the game_info_view added another 1 GB, so 4 GB total. So the DB even with its supposedly "efficient" storage was expanding the plain-text storage by a factor of 3-4.
    • beachy 2y ago
      It's easy to get up to quite a high number of joins if you have a well normalised database that uses a lot of lookup/reference tables. Common in a situation where you're writing for a presentation layer and you need human readable data. 30 sounds a lot, but I'm pretty sure I've written SQL with almost 20 while building an ERP application so I don't see why 30 implies garbage schema, just a complex one. And as you say, it shouldn't be a problem anyway, there no magic number of joined tables where things stay breaking down.
      • SJC_Hacker 2y ago
        > Common in a situation where you're writing for a presentation layer and you need human readable data. Yup, this was it. For compactness (theoretical) alot of the fields were stored as small integers, then a lookup. Here is the relevant offender if interested. https://github.com/praphael/baseball/blob/main/retrosheet_init_db.py#L1274 https://github.com/praphael/baseball/blob/main/retrosheet_in... Alot of lookup for player names and more descriptive fields (field conditions, play descriptions, etc.) In retrospect, this all could have been done as part of a post-process step on the backend. I already was doing that for certain fields such as the month. Which does fail the "anything that can be reasonably be done on the DB end should be done by the DB" test. But it might be a better idea.
• andoando 2y ago
  Nothing stops you from denormalizing in SQL though, you have the power to do both. The NoSQL paradigm has its merits, it just seems like it can be applied in SQL with far less limitations.
• daelon 2y ago
  Just a heads-up, your website title/headers are way too big on mobile and its kinda breaking the layout.
  • jchanimal 2y ago
    Thanks, we recently started working with a designer, I can't way to share the new homepage soon.