6 ms·

Window functions are often a good answer to the "why SQL?" question. Here's a real-world case where I used them lately. Suppose a user has an inbox of messages

Window functions are often a good answer to the "why SQL?" question. Here's a real-world case where I used them lately. Suppose a user has an inbox of messages from other users. I'd like to show the user her latest conversations (aka "message threads"). Ten conversations per page, and I'd like to show the three latest messages per conversation. That's fairly easy with window functions like dense_rank() and row_number() - and it's flippin' fast... just a few milliseconds on a table with thousands of rows and I could probably get it lower than that with some index optimization. // "conversations" is a CTE (common table expression) // "messages" is the actual table // we don't need a CTE here but it's probably the most readable way // query is a little hairy because of all the greatest() and least()'s WITH conversations as ( select row_number() over (partition by greatest(from_id,to_id), least(from_id,to_id) order by created_at desc) as conversation_message_number, max(id) over (partition by greatest(from_id,to_id), least(from_id,to_id)) as conversation_latest_message_id, mes.* from messages mes where 1234 in (mes.to_id, from_id) // 1234 is the member's id ) // get conversations 1 through 10, and get the 3 latest messages select dense_rank() over (order by conversation_latest_message_id) as conversation_number, * from conversations where (conversation_number between 1 and 10) and (conversation_message_number <= 3) The alternatives are not a lot of fun. 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. I'm not familiar with NoSQL implementations but I'm guessing you'd have to pull all those records into your app tier and do it there as well?

Amezarak 12y ago
> 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.
angersock 12y ago
The thing that makes me nervous about having that sort of business logic in the database is two-fold: first, finding people that can write good, clean SQL and document it so others can use it can be tricky; second, changing that logic means, effectively, more migrations. The model of a SQL DB as dumb storage, while hardly making good use of all the (awesome!) functionality of query languages, seems to make it easier in production and in hiring. That said, it is downright comical just how much mileage you can get from using functions or optimized queries instead of hoisting all that data up into the application layer and doing the work there.
superuser2 12y ago
Of course, you could always use ActiveRecord's facility to drop down to SQL, and continue to use ActiveRecord where it is actually more convenient.
- JohnBooty 12y ago
  Absolutely! I have the above SQL code in a Postgres stored function, and call it via ActiveRecord's find_by_sql() and use it in one of my Message model's methods to populate a bunch of Message instances.

Amezarak 12y ago

> 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.

angersock 12y ago

The thing that makes me nervous about having that sort of business logic in the database is two-fold: first, finding people that can write good, clean SQL and document it so others can use it can be tricky; second, changing that logic means, effectively, more migrations. The model of a SQL DB as dumb storage, while hardly making good use of all the (awesome!) functionality of query languages, seems to make it easier in production and in hiring. That said, it is downright comical just how much mileage you can get from using functions or optimized queries instead of hoisting all that data up into the application layer and doing the work there.

superuser2 12y ago

Of course, you could always use ActiveRecord's facility to drop down to SQL, and continue to use ActiveRecord where it is actually more convenient.

JohnBooty 12y ago
Absolutely! I have the above SQL code in a Postgres stored function, and call it via ActiveRecord's find_by_sql() and use it in one of my Message model's methods to populate a bunch of Message instances.