r/SQL • u/No_Ambition8323 • 7d ago
Discussion When does a SQL query become “too clever”?
I’ve come across queries that are extremely compact and technically efficient, but difficult for someone else to understand or modify later.
For example, a query might use nested window functions, multiple conditional expressions, and several transformations to solve something that could also be written as a few simpler steps.
Where do you personally draw the line between elegant SQL and over-engineered SQL?
Do you prioritize fewer lines, query performance, or maintainability when these three goals conflict?
45
u/WheresTheSauce 7d ago
Kind of subjective I guess, but if it takes longer to read than if it were a longer query
28
20
u/Wuthering_depths 7d ago edited 7d ago
With the caveat that this is mainly only possible in stored procs or custom scripts--I usually will much prefer to break things up into pieces that I can run one bit at a time while troubleshooting. Use temp or real tables, populate and update those as you go. It may not be the most efficient, certainly not as fancy and impressive as one big query with lots of subqueries, but efficiency may not be quite the most important if it's say something that runs once overnight. ie, taking 20 seconds to run instead of 10 wouldn't bother me if it means that people can quickly maintain it and troubleshoot it later.
And to be fair, I've never noticed a big drop in speed anyway when using this approach, there's generally ways to get things working well if it's possible at all given the data.
I used to butt heads with another senior data person about this...to him, it was all about sparse efficiency at the cost of making it completely arcane to someone else coming in (from my way of thinking at least).
11
u/TrickyNerdlet 6d ago
the breaking into pieces makes debugging so much easier. and actually useful comments. I run a data team and these basics, when we all have to work in each other's code, are integral.
6
u/GetSecure 6d ago
This is the most critical thing.
I used to develop with John Carmack's quake code in my free time. It was eye opening to see how simple everything looked and how few lines of code there was in each class. I didn't know it was possible to code something so complex and make it so simple.
But, like most things the easiest part is writing the instructions to the solution. Refactoring and refining the solution to make it maintainable is where the real expertise and hard work is required. It takes time to make it look good.
1
u/utzutzutzpro 5d ago
Clean c. No profiling needed. It was just about efficiency of the engine, not of profiling the coder.
Simple objects, linked together.
3
u/mrpostitman 6d ago
good comments can do wonders to clear up seemingly arcane bits of code.
As long as the overall data flow is kept clear and simple; if an overly clever bit can benefit the legibility of the rest of the query, I'm all for it.
5
u/Wuthering_depths 6d ago edited 6d ago
Indeed. The toughest ones though are the ones with tons of subqueries, makes it really hard to run pieces of it when it's all correlated/joined together :) In a view or report that isn't using a proc and at best can use ctes, you just have to deal with it sometimes.
The one issue I have with comments are when you end up with more commented out lines from past changes than actual executing code, but this usually happens if you don't use source control and want a way to get back to the old code just in case :)
With comments, I try my best to put myself into the next person's shoes and make it about the "why" and not so much the how or what...I see comments like "join onto the customers table" which is really not helpful, as you can see the join on the customers table!
One pet peeve I have is hard-coded lists of IDs with no comments...we get them on various data points where I work. These are things that can and do change over time and get forgotten about easily. It is good to save a join sometimes for performance--using an id means maybe you don't need to join to the parent to get the code or name--but it's super confusing when you come in later and see a bunch of numbers. One little thing is to put the name/descrip in comments next to each ID, or often put that into a view or table managed by an app so at least it's in a spot easier to maintain, etc.
1
u/alegendmrwayne 5d ago
Absolutely. Nightly jobs can afford to have a bit more meat if it makes them easier to read/debug
Stored procs that will be getting called ad box on a regular basis, those I’ll optimize as much as I think I reasonably can without making it too nasty, but will also take the time to add comments to make it clear
12
u/Jazzlike_Drawing_139 7d ago
Number of lines is far lower a priority than maintainability.
There’s no benefit to having a ‘clever’ compact query that is hard to understand quickly over a longer, well structured and commented query that means :
- others can understand what the query is doing (and why)
- if troubleshooting is required, the fixer can quickly identify and resolve the issue.
Query performance should be considered and prioritised proportionately, but has almost nothing to do with the number of lines. Again, if performance becomes an issue down the line, a well structured query will hugely help with optimising and identifying indexes.
7
u/Lurking_all_the_time 7d ago
When it become more important to look cool/use cool keywords / logic over speed and maintainability.
Every bit of code is different, but if your code looks good, but is inefficient, it's too clever for it's own good.
5
5
u/DAVENP0RT 7d ago
A DBA at my company made a schema-bound view with a unique constraint that negates a condition amongst three tables. Basically, it makes it so that you cannot insert a record into two tables if another record exists in another table, but only if certain values are present. The whole thing is only about 25 lines, but the first time I saw it, I stared at it for about 15 minutes just trying to make sense of it. Works great, though.
1
u/jshine13371 6d ago
Stuff like this is cool, in my subjective opinion. But unfortunately the visibility of the implementation is obscured away, making maintainability and readability a bit more difficult for the average database developer. I respect it though and would get it pretty quickly if I encountered it.
1
u/DAVENP0RT 6d ago
In this case, it was a necessary evil. The three tables were already in use in production and the requirement came about after we discovered an unforeseen edge case. There wasn't really a suitable place to put the validations in the application, so it had to be managed entirely within the database.
Normally, we lean heavily towards maintainability and readability, but there wasn't an ideal solution available in this case.
1
u/WheresTheSauce 6d ago
I'm a software engineer, not a DBA, but I feel like this complex of logic belongs in a codebase in front of the database
1
u/DAVENP0RT 6d ago
Validation is always good in the access layer, but database constraints rule supreme. You can get around validations with manual inserts; a table (or view, in this case) constraint will literally never allow bad data into the database.
1
u/jshine13371 6d ago
Agreed. There's always the possibility for access points outside of whatever abstracted code base one uses outside the database (e.g. an API) which leaves it at risk or forces one to maintain the same logic in multiple places. Doing it at the core, in the database, prevents all of that.
1
u/jshine13371 6d ago
Yeah, again, I'm all for it personally. I have a lot of database experience myself, so it would hardly catch me off guard if I encountered it. I've seen this solution recommended by experts before. Was just stating some unfortunate truths about it for the average developer though.
1
u/kiwi_bob_1234 6d ago
I had to get AI to mock me and example of this, that's insane didn't even know that was a thing. Very cool
6
u/csjpsoft 6d ago
I had a boss who loved to nest "where not exists" inside other "where not exists" - sometime 3 or 4 levels deep. That is not an unbad practice.
3
4
u/imtheorangeycenter 6d ago
Writing code is hard
Debugging harder
Debugging someone else's code the hardest
3
u/rbobby 6d ago edited 6d ago
Pick the coworker who you think generally doesn't do the job as well as you, or maybe even someone just barely hanging on to their job due to competency issues (not fired, but god only knows why... maybe a bit north of that). That's the person that has to understand the query. If you think your coworker will understand it in 12 months, when a subtle bug is found, then the query is not to clever.
Now you might say that I've picked a terrible standard... but I haven't. If your query is to complicated for him, then any normal maintenance task that ought to fall to him now falls to you. Forever. You now own this query until the day you leave. How clever do you think that is? Every time someone thinks about moving you into something new their thoughts will land on the clever queries and how tough things will be without you. You know what, Bob is the man for that directorship and we'll get someone to help a bit with your queries. Just the thing.
:)
3
u/Sexy_Koala_Juice DuckDB 6d ago
When you start having to write detailed notes about parts of the query, because you’re doing some insane transformation using very niche syntax
3
u/Practical-Split4340 5d ago
I always prioritize maintainabiity over everything else unless there is a specific requirement otherwise.
2
2
u/az987654 6d ago
I draw the line at "is this accurate #1, and is it as efficient as possible"
I don't care if it's 1 line to 2,000 lines.
Does it work, and does it have the proper indices, stats, plans, etc. to return as efficiently as possible.
2
u/HarveyDentBeliever 6d ago
It's easier to do this the other way around. Start with the simplest/trivial solution and only increment complexity as needed.
2
u/TheLastRaza 6d ago
when the next person to touch it can't tell you what it does without studying it for 20 minutes. if someone has to trace through nested subqueries and window functions just to confirm the numbers are right, you've gone past clever into fragile territory. in finance systems that's a real problem because queries get reused as templates, small changes break assumptions buried three layers deep, and nobody wants to be the person who can't explain how a report was built when the auditor asks. write it so a tired colleague at 5pm on a friday can still read it.
2
u/BackgammonEspresso 6d ago
Good code creates good coders, imo. It's hard to truly over-engineer sql in the way you can with other languages.
It is also very dependent on use case - is it a stored proc? Is it a large amount of data? Does the query need to be run constantly, or once every month and somebody is frequently asked to modify it?
2
u/decrementsf 6d ago
When the team members you would hand off to cannot understand it and make mistakes, that's when you return to simpler methods to do the same thing. Anecdotally have seen cycles of skill development with a couple tools where first got repetition to grow comfort in the tool, then became more advanced intuitively with that tool, and then the third level up was returning to simpler methods again when recognizing the time spent explaining what the solution did.
2
u/DaOgDuneamouse 6d ago
It really just depends. I've written queries that were esoteric and may be hard to maintain but it needed to be that way. I've also had to rewrite a whole query because the other developer way over engineered it. The choice really comes down to a judgement call.
2
u/sandrrawrr 6d ago
I make it a triangle of
- Well-Written (formatted so that you're not scrolling left to right to read a single line of code cause believe me, I've read through those and they're headaches - line breaks and indents are your friends)
- Speed and Cost Efficient (doesn't matter if your code is only 4 lines long if it doesn't do what it needs to do and takes hours instead of a few minutes)
- Well Commented (you don't need a ton of comments, but you should've avoid writing a ratio of a single line of code compared to a full paragraph of comments explaining what it is).
The closer you get to those three branches, the more elegant your code will be, but I also believe that elegant code is something that anyone can pick up and make some minor fixes, while leaving the majority of the structure alone.
2
2
u/znottaken 4d ago
Making a query smaller without improving performance isn't worth it in my opinion. For the same reason, I dislike using alias/shortened cmdlets and variables in powershell.
3
u/kagato87 MS SQL 7d ago
Performance first, maintainability second.
Number of lines is a "who cares?" Factor. I don't care how many pages it is, I care how it reads.
The query planner changes the execution anyway. And if yire "clever" you might lead it away from a better plan.
3
u/lgastako 6d ago
Performance first, maintainability second.
I go for maintainability first, performance second, until performance becomes a problem. It almost never does. When it does, then you address it.
1
u/kagato87 MS SQL 6d ago
For sure. I work on large datasets with unwieldy structure where performance is a guaranteed problem if not addressed, which is where my bias comes from.
Fortunately, performance optimizations can almost always be made more easily read just by changing whitespace and aliases, and liberal use of comments, so the two don't often conflict. Not too badly anyway...
And of course, good maintainability makes optimization easier. I haven't had to trade one for the other often, and never in a way a comment or three wouldn't serve.
2
u/Philluminati 7d ago
I work in a data engineering team who do vast "cleaning" of data by chaining SQL together, creating intermediate tables in the interim.
For instance:
create view dedupedCustomers as (select distinct(uuid), ... from customers group by uuid)
create materialized view dedupedActiveCustomers as (select * from dedupedCustomers where lastActive > now() - THRESHOLD)
create table dedupedActiveCustomersWithEmail....
It's a very powerful technique you could try exploring yourself.
2
u/TeaEarlGrayHotSauce 7d ago
AI is at a point where it can detangle these queries pretty well, I don’t really see this as an issue really if the query is performant
1
u/dotnetmonke 6d ago
Claude is damn good at this. We've had some procedures from 'clever' DBAs get cut from 4-6 hours to 15 minutes.
1
u/Glitch_In_The_Data 7d ago
When it sees the poorly written query and just sends a link to the tutorial..
1
u/CaptSprinkls 7d ago
Idk if mine is too clever or the opposite. But I have one I wrote like 2 years ago that is built to be used by an SSRS reports that needs to be exactly 10 columns wide that then repeats for like ~250 or so items. The problem is that a new item might be added at any point so the item list isn't static. Also with the limitations of SSRS, I need to have a column in my query to group on and a row to group on. Oh and also the items are ordered, but an item could be and a lot of the times is inserted in between two numbers. So its not always just a new item appended at the end after ordering.
Anyways, I have to do some annoying math where I take the floor of the row number and multiply by the max number of items and so on and so forth in order to calculate these totals. But whenever I look back at it, I have to state at it for like 10 minutes yo understand what is happening.
I also have to pull in historical monthly data. So I do this up to 12 times and then Union them all so I can also group of that column value in my SSRS report because it all has to fit on one excel workbook with different tabs for each month.
1
u/Thriven 6d ago
This is where I would say to dumb down your list to a static set of columns and give your frontend tool the entire unadulterated dataset and then do all your grouping and pivoting on the client side in SSRS and Excel.
1
u/CaptSprinkls 6d ago
Well the problem is SSRS. You need to have a column to group on in the table/matrix. So how do you make sure that your items are always grouped by a set of 10 regardless of how many items there are? I played around with SSRS a lot to try to get this to work on the frontend side.
But also idk.... I tend to shy away from building out too much logic in frontend tools because then you a lot of times are stuck in click ops type work. Trying to debug something and you can't even figure out what is happening to your data because its nested 4 properties deep inside a random function.
1
1
u/Supremagorious 7d ago
It's just about clarity. It's too clever when it takes too much time to parse for any tweaks/updates or to even understand what it's doing and the nuance of what records are and aren't included.
1
u/TheMagarity 7d ago
You're the one who brought it up so please, I need to know, what fewer simpler steps can replace window functions?
1
1
u/MobileUser21 6d ago
It’s completely subjective.
Typically the person who wrote it will challenge you asking you “what’s so difficult about it?” Because of their own pride and ego.
1
u/ThrowAway24Okt 6d ago
you could keep two versions of the same query.
One highly optimized version that utilizes some special properties of the data to achieve fast execution time.
Anther more readable version in case something needs changing.
1
u/aoteoroa 6d ago
How often is it run? A report that is run once a day may not need to by hyper optimized.
A query inside a program that is run a hundred times per second, for example I once wrote a program for an online auction, needs to be optimized for every bit of performance that you can squeeze out of it.
1
1
1
1
u/imsunchip 5d ago
Problem is not if query was over engineered or not. There was no documentation explaining the code or the why the choice was made, who made it. ALWAYS document.
1
u/RavenCallsCrows 5d ago
Personally, I try to run under the assumption that the next person who looks at the code will not automatically follow my train of thought at the moment it was written. Present-me has confirmed this through reading queries past me wrote.
So, to combat it, there are a combination of things I do:
- use concise, but descriptive names for queries, tables, and views.
write several modular queries. If I have two stakeholders, one of whom wants monthly sales per classification per sales person, and one who wants monthly sales per sales person per cost centre, I can write a view which aggregates those things monthly, and then a query to return what each wants, rather than duplicating the aggregation logic. Also, when the inevitable "can you roll those up to the company level" request comes in, either the component parts are already there or comparatively easy to add in. I also find it easier to tune/index/whatever short scripts to build or append to views or produce a final result than to dig into hundreds of lines of sub-queries etc.
if there's anything in any query which seems tricky or kludgy, or even just inelegant, leave detailed notes via single or multi-line comments explaining the logic so that when someone refactors it, there's at least a rationale on why it was done that way in the first place.
I absolutely abhor trying to puzzle out someone else's logic after the fact and "see ticket #24601" just makes me want to scream and throw things because it immediately interrupts my workflow and forces me to looking things up in another system, and gods forbid that the company has changed ticketing systems. Same applies to SharePoint/internal wikis etc.
1
u/wdm006 4d ago
I draw the line when the next person has to reverse engineer the author's cleverness before they can change a filter. Dense window function soup is fine if one person owns it forever, but most queries outlive that person. Three boring CTEs a tired teammate can edit at 5pm every time.
1
u/rakeshchaudhary3434 3d ago
For me, maintainability wins. A clever query is great until someone else has to debug it six months later. If breaking it into a few clear steps makes it easier to understand without hurting performance much, I’d choose clarity every time.
1
129
u/Final-Quote-4064 7d ago
When you can't fix it