4 ms·

> You could fairly easily implement this logic in Ruby/Java/whatever but you'd have to pull a ton of rows out of the database and sort them in your app tier, wh

> You could fairly easily implement this logic in Ruby/Java/whatever but you'd have to pull a ton of rows out of the database and sort them in your app tier, which is kind of ugly performance-wise even if you're not paying the cost of converting each row into a model object w/ ActiveRecord/Hibernate/etc. This, a thousand times this. I have done a lot of performance work with a variety of web applications, and this is a common theme - pulling more data into the app tier than you need to. Putting even a small amount of effort into writing good queries will almost always have a massive performance impact on your application. If performance is at all a concern, this is one of the first places you should start looking - you should be trying to bring over the smallest amount of data from the database as possible. I've found, though, that a lot of developers have conceptual problems dealing with set-based operations. Not only does it look cleaner (depending on the framework and design) to front-load the application, I think that many developers simply find it much more intuitive to bring the whole dataset in and then filter with some loops and conditionals and then use the 25% of fields they pulled in that actually need to be displayed. If you forced them to push more work onto the database, they'd simply write imperative-looking SQL. I'm not real sure how to get people to think more in set terms. Once you get there though, well-written SQL is beautiful. Query optimizations for the most part become obvious - though there will always be further optimizations database experts can make because they have a better understanding of the database engine - just try to make sure you're always operating on the smallest possible sets of data to get the desired results. It's often not hard at all to write highly performant queries even if they have huge numbers of joins in large tables. And window functions are a big help when it comes to doing this! Sorry for the rant - I just see this so much. :)

JohnBooty 12y ago
I've found, though, that a lot of developers have conceptual problems dealing with set-based operations. Yes! I can't help but think that a lot of people are drawn to NoSQL (or find themselves opposed to SQL for other reasons) simply because they don't understand SQL. As somebody who avoided databases early in his career I definitely understand this. well-written SQL is beautiful Count me as a member of the club that believes this. Not every problem can or should be handled with a relational data store, but for those that do, I don't think the resultant set-based operations could be expressed much more cleanly or succinctly than SQL allows.
- buckbova 12y ago
  These NoSQL document stores seem to be a realization of that object database dream of years past. It's not exactly one-to-one (objects to documents) but it gets you there quicker than a relational database could. I enjoy writing well crafted SQL queries as a currently employed database architect. But I don't find it to be all that elegant. I do think it bridges the gap between lay person and expert by being somewhat readable declarative language. For those programmers out there it offers some procedural aspects, differing from vendor to vendor. Features like window functions and CTEs have helped to limit the temp tables, derived tables, subqueries, etc required to achieve the desired output. One can now make a much more terse query.
sergiosgc 12y ago
> well-written SQL is beautiful I say often that software engineers/computer scientists produced good solutions for three and only three problems: - Networking - Compilers - Databases And that's it. On the one hand, it's pretty poor for ~70 years of serious effort. On the other hand, the solutions for each of these are beautiful -- mathematically and engineering-wise.
- kbenson 12y ago
  Then again, I think many, many problems actually break down into these categories if you look at them long enough, which is why we've had so much effort put into these.

JohnBooty 12y ago

I've found, though, that a lot of developers have conceptual problems dealing with set-based operations. Yes! I can't help but think that a lot of people are drawn to NoSQL (or find themselves opposed to SQL for other reasons) simply because they don't understand SQL. As somebody who avoided databases early in his career I definitely understand this. well-written SQL is beautiful Count me as a member of the club that believes this. Not every problem can or should be handled with a relational data store, but for those that do, I don't think the resultant set-based operations could be expressed much more cleanly or succinctly than SQL allows.

buckbova 12y ago
These NoSQL document stores seem to be a realization of that object database dream of years past. It's not exactly one-to-one (objects to documents) but it gets you there quicker than a relational database could. I enjoy writing well crafted SQL queries as a currently employed database architect. But I don't find it to be all that elegant. I do think it bridges the gap between lay person and expert by being somewhat readable declarative language. For those programmers out there it offers some procedural aspects, differing from vendor to vendor. Features like window functions and CTEs have helped to limit the temp tables, derived tables, subqueries, etc required to achieve the desired output. One can now make a much more terse query.

sergiosgc 12y ago

> well-written SQL is beautiful I say often that software engineers/computer scientists produced good solutions for three and only three problems: - Networking - Compilers - Databases And that's it. On the one hand, it's pretty poor for ~70 years of serious effort. On the other hand, the solutions for each of these are beautiful -- mathematically and engineering-wise.

kbenson 12y ago
Then again, I think many, many problems actually break down into these categories if you look at them long enough, which is why we've had so much effort put into these.