3 ms·

I would argue that you probably haven't written any SQL of significant complexity in which to observe its composability if that is your assertion. Subqueries,

I would argue that you probably haven't written any SQL of significant complexity in which to observe its composability if that is your assertion. Subqueries, table-valued functions, SProcs that return tables, and more allow you to compose operations into your desired final query. That said, it is largely used in a giant procedural way most of the time, and is certainly not an ideal functional language, but the fundamentals of things like, map, filter, reduce, sort, etc. are there.

nightski 9y ago
I have written quite a bit of SQL including all of the items you mentioned including common table expressions, subqueries, views, etc.. This is a very limited form of composition. It's very difficult to break a query down into a bunch of subcomponents that can be reused among groups of queries. CTEs are close, but it's really hard to achieve re-usability of a CTE unless you are stitching together strings at runtime. Probably the closest you could get is a view, but even then if you have many views stitched together to form a query this can impact performance in ugly ways. Generally in order to achieve the best performance and to get the most of out of the DB engine it seems like producing unique SQL for each query which is optimized for that query is generally the optimal approach. In summary: Just because SQL is based on a few composable operations (map, filter), does not mean it is composable itself.
- geofft 9y ago
  This is a slightly snarky but mostly serious response: isn't hidden runtime complexity of elegant operations also a property of functional programming languages? :-) There are a few that are good at optimization (and in my limited experience, Haskell is one of them), but in general if you use lots of conceptually clean maps and filters, you will occasionally see cases where your performance is very bad because you don't have full control over exactly what is evaluated when, won't you? That seems like basically the same problem that you have with using views and not trusting your query planner to do things efficiently.
- rjbwork 9y ago
  I can largely agree with that from a practical perspective - I was speaking more in the theoretical. At any kind of practical scale, everything does seem to end up being fairly bespoke.

nightski 9y ago

I have written quite a bit of SQL including all of the items you mentioned including common table expressions, subqueries, views, etc.. This is a very limited form of composition. It's very difficult to break a query down into a bunch of subcomponents that can be reused among groups of queries. CTEs are close, but it's really hard to achieve re-usability of a CTE unless you are stitching together strings at runtime. Probably the closest you could get is a view, but even then if you have many views stitched together to form a query this can impact performance in ugly ways. Generally in order to achieve the best performance and to get the most of out of the DB engine it seems like producing unique SQL for each query which is optimized for that query is generally the optimal approach. In summary: Just because SQL is based on a few composable operations (map, filter), does not mean it is composable itself.

geofft 9y ago
This is a slightly snarky but mostly serious response: isn't hidden runtime complexity of elegant operations also a property of functional programming languages? :-) There are a few that are good at optimization (and in my limited experience, Haskell is one of them), but in general if you use lots of conceptually clean maps and filters, you will occasionally see cases where your performance is very bad because you don't have full control over exactly what is evaluated when, won't you? That seems like basically the same problem that you have with using views and not trusting your query planner to do things efficiently.
rjbwork 9y ago
I can largely agree with that from a practical perspective - I was speaking more in the theoretical. At any kind of practical scale, everything does seem to end up being fairly bespoke.