I do whatever I'm supposed to do. 3 months later, a bugfix required, I find myself at this piece of SQL that I know works but I cannot digest it for another 30 minutes.
And my case is really simple.
But yeah they are amazing.
We have a few ~200 line long queries in one of our applications. With comments one of them is 460 lines.
When things get hairy, don't be afraid to explain why you needed this CTE, what it's doing from a high level, point out that little gotcha that you ran into a few times while developing the query, why you needed to do X instead of the simpler Y, etc...
While there are downsides to excessive commenting (the big one is that the comments can get "out of sync" with the code and just cause confusion), when it comes to big/complicated SQL, I've found that more is better for the most part.
[1]. http://www.craigkerstiens.com/2013/07/29/documenting-your-po...
Initially a few folks rolled their eyes at that, but now everyone loves it. When you have thousands of tables, comments help, likewise when you have massive amount of queries. I'm also in the camp of let the DB do the work if it can do it faster, and likewise have 100-400 line queries that will turn into 5x LOC programs which take 20x time to execute. Writing tons of comments is important. Usually the query is right and when it works, no one remembers it for a year or two till business rules change.
My approach to commenting such queries after modification is to delete every single comment and start afresh. From memory, when I'm done. If I can't then I have no idea what the query was doing which is very bad. Sometimes folks modify query and get a happy result. If I can document it without reference to old one, great! After that, I look at the old one to make sure that I'm not missing anything.
After i'm done (I tend not to comment these behemoths until at or near the end), I go and explain what the query is doing, sometimes almost line-by-line, to a fake "rubber duck" as if it were a junior developer. Even sometimes commenting what I didn't do, and why I didn't do it. Because as it turns out, future me is a bit of a cocky asshole that always thinks past-me was some kind of idiot that must have never thought to try that before...
You can decompose large queries into smaller views/temp tables, and such queries will be much more readable..
It's a trade off.
Instead, for a CTE, I would rather add a sample of initial data, and how they are transformed by the first two or so iterations of the loop.
I generally find it a safe assumption that were the comments don't agree with the code both are wrong, or will be next time someone makes a related change (as sod's law says they'll chose to trust the one that is least correct). Even when all the actors involved are me at different times.
In the case of Haskell and Scala the aforementioned query DSLs serve as CTE generators without the performance hit (albeit non-recursive).
Being able to compose complex statements based on statically typed query snippets is really, really nice -- strips out the reams of repeated boilerplate that one inevitably is forced to write with string-y SQL.
Sure, in the end they generate (prepared) sql statements; CTEs are just another statement with an expected result type.
As for supporting parameters in a recursive CTE, I don't think providing runtime values would be an issue.
WITH RECURSIVE $name (n) AS (
SELECT $init
UNION ALL
SELECT n + $incr FROM $name WHERE n + $incr <= $limit
)
SELECT n FROM $name;
Also, for the record, SQL is a strongly typed, declarative language, even if it doesn't seem that way.
Key assumptions I'm working from here are:
1. Actual programmers (in contrast with SQL's original target audience) generally know exactly what data access patterns are appropriate for a particular task; they just don't want to write them from scratch and have to worry about stuff like locking and transactions at a fine grained level.
2. Being able to switch plans on the fly based on query input and data statistics is not a huge win in most real world scenarios, and isn't worth the unpredictability that it entails.
3. Most of the shallow pain of working with SQL comes from the fact that it was a strange paradigm to start with, and then had a bunch of features hacked on top of it.
4. Most of the deep pain of working with SQL comes from the fact that it abstracts too much, forcing you to reverse engineer the desired query plan through those abstraction layers. And like a lot of reverse engineering, the result is fragile and might change to something far less efficient in the future for inscrutable reasons like database upgrades or subtle changes in how the data is shaped.
Pretty much any replacement can address 3. Ideas that really excite me address 1 and 4, and it seems to me that those are more likely to look procedural than purely functional. But like I said, the main thing is that having a low level target to compile to would allow us to try out different ideas and see what works.
1. "Actual programmers" tend to have net negative clue about which data access patterns are appropriate. It is, for example, staggeringly common for me to have to explain that a "table scan" is often more efficient than random IO ("index scan") — even on NAND media — if you're reading more than some threshold of the data in a table.
If you (the general "you") understand data access patterns that poorly, no, you absolutely should not be dictating query plans. If you think "hav[ing] to worry about stuff like locking and transactions" is an imposition, you don't want me to sit in your interview. I will hard pass.
2. See above.
3. The relational algebra is not a "strange paradigm". It's very, very simple. "Strange features" like what? Ordering? Aggregation? Set intersection and exclusion?
4. I don't even understand this complaint.
> If you think "hav[ing] to worry about stuff like locking and transactions" is an imposition, you don't want me to sit in your interview.
Why did you remove "at a fine grained level" in quoting me? I'm saying that people want the facilities that a database painlessly provides with respect to these things. What I'm saying is that programmers don't want to implement MVCC themselves, for example, or invent their own system for managing locks. These are things that are great about RDBMSes, but my point is that they could be done without SQL.
For another example:
>"Strange features"
That's not a thing I said at all. I said "a bunch of features". "with recursive" is an example of this. It's a great feature, but as the commenter at the top of this thread pointed out, using it is clunky and hard to read. I believe that this is in large part because how it had to be worked into an existing, weirdly designed language in a backward compatible way.
Edit to add: I don't understand why discussions about software engineering so often quickly turn into these attacks on people's competence. I might be wrong, and if so, you can convince me of that without raising your hackles with these aggressive statements about what you would or wouldn't do if this were a job interview. That kind of rhetoric is toxic to productive discussions.
Engineer: "SQL is dumb and hard!"
DBA: "What part?"
Eng: describes problem
DBA: describes misunderstanding
Eng: "Oh! Oh, that's actually really simple! Thanks!"
> I believe that this is in large part because how it had to be worked into an existing, weirdly designed language in a backward compatible way.
Is sloppy shoe-horning the fault of the shoe, or the fault of the horn (assuming, for sake of discussion, that it's even sloppily done)? Recursively traversing parent-child (among other) relationships is not a wild, unforeseeable extension of set theory — and that's really all SQL is: a practical expression of set theory with a syntax that (admittedly) sometimes obscures that fact.
EDIT: Re: your edit. As one of my employer's DBAs, it's part of my job to reduce risk, including by passing on candidates I feel inadequately understand databases, or whose attitude evinces a lack of interest in improving that understanding. It's not about "attacking" a lack of competence, so much as avoiding the kind of incompetence that refuses to recognize itself.
If you've ever sat on the interviewer side of that table, you can't even pretend not to have seen entirely too much of that, and you'd pass on someone who didn't think they needed to understand the costs of various forms of, e.g., list traversal, just as quickly.
You get the point.
---
Imagine you're troubleshooting your query, mapping. It's got some modest joins. So you capture the emitted SQL. Then you fuss with that SQL to make it something reasonable. Once that SQL is working, you then wrestle with the ORM to try to coerce it to re-emit your desired SQL.
This is also called reverse engineering, pushing rope, fighting the 800lb angry gorilla sitting between you and your work.
Eventually you'll figure out you should just use SQL, whatever dialect your backend supports.
My take-home on the whole thing is, you need something that makes working with relations in your code easy but you don't need something attempting a ridiculous abstraction like "relations are objects."
You're saying you've never captured the generated SQL, debugged and tweaked it, and then backported that SQL to your ORM obfuscation layer?
I will agree that HQL has flaws but not the same ones that you admit. It is string based which means it is cumbersome to use, dynamic column filtering is not possible, entering parameters requires 3x dupliation of the. variable name and it isn't type checked at compile time.
But frameworks like grails (which is built on top of hibernate and criteria) or LINQ have solved these problems.
My biggest performance bottlenecks are in batch insert/update performance for which most ORMs already generate optimal queries that don't involve database specific features like "unnest(col1, col2)". But even that could be solved trivially by exposing a simple batch insert/update api to have both the convenience of an ORM and the performance of db specific optimisations.
[1] https://www.amazon.com/Designing-Data-Intensive-Applications...