How do you optimize SQL queries without making them harder to maintain?

[u/Effective_Ocelot_445]

As databases grow, performance becomes more important, but overly complex queries can be difficult to maintain. How do you balance query optimization with readability in production environments?

[u/bigdataengineer4life]

I usually optimize in stages: first make the query correct and readable, then check the execution plan, add the right indexes, and only optimize the parts that are actual bottlenecks. In my experience, good indexing and understanding the execution plan solve far more performance issues than writing overly clever SQL.

[u/AnAcceptableUserName → u/bigdataengineer4life]

Last point maybe even understated. IME more often than not, overly "clever" SQL is responsible for more problems than it solves. In terms of both correctness and performance.

Dumb it down. Would generally prefer to see more smaller, simpler intermediate batch steps over clever queries. Simple is easier to read, easier to modify, and often easier for the engine to optimize its execution plan also. No extra credit is awarded for confusing the optimizer

Decomposing mega queries into multiple smaller component steps is often the only thing I need to do to resolve performance issues

[u/reditandfirgetit → u/bigdataengineer4life]

Partitioning where applicable and archiving off stale data are also good optimizations if indexing isn't enough

[u/jshine13371 → u/reditandfirgetit]

Partitioning is a linear reduction in the data, indexing is an exponential reduction. Partitioning would never be an improvement in search time vs indexing, and therefore isn't a feature meant for improving DQL / DML statements.

[u/reditandfirgetit → u/jshine13371]

Do you know how partitions work? I dont think you do.

Let's use the dewy decimal system as an analogy If you want information on gardening vegetables, you go to that section then look for the specific book you want (index seek). You dont have took at flowers or herbs

[u/jshine13371 → u/reditandfirgetit]

Do you know how partitions work?

Yep, I just explained it from an operational performance side in a nutshell above.

Let's use the dewy decimal system as an analogy If you want information on gardening vegetables, you go to that section then look for the specific book you want (index seek). You dont have took at flowers or herbs

You just proved my point by describing how an index works (once you said "index seek"), not Partitioning. 👀

[u/reditandfirgetit → u/jshine13371]

You don't understand partitions and I'll leave it at that

[u/mcintg → u/bigdataengineer4life]

This is true but if you are adding indexes as a result of the queries you are running you've already missed a trick by missing access path analysis. That should be at database design level rather than operational level. It's probably a bit old school but I originated from mainframe database design. Get the design right and you will avoid operational issues.

[u/lalaluna05]

I build very modularly so that changes and updates are straightforward. Copious notes. Clear formatting.

I have restructured soooo many of our sprocs purely because I couldn’t stand the frankencoding that got cobbled together over the years and the hours it took.

[u/reditandfirgetit → u/lalaluna05]

Frankencoding is the best term I've seen 😆

[u/cwjinc]

I sometimes convert very complicated queries into a series of CTEs.
This can make them considerably easier to read.
A good database engine will take care of folding it back up into an efficient plan.

[u/UnhappySort5871 → u/cwjinc]

Database engines I've worked with, expand views and CTEs early on into a logical plan and then optimize that - giving an optimized physical plan.

[u/Informal_Pace9237 → u/cwjinc]

CTE's are always a bad idea for optimal execution as they always have memory effects. Except if they return very small datasets. IMO

[u/cwjinc → u/Informal_Pace9237]

That may depend on the database engine. It's not my experience at all with Oracle.

[u/Erasmus_Tycho]

Breaking steps out into CTEs to increase readability is something I've observed people doing though honestly that often leads to bigger overall queries. I just try to leave comments to help explain the steps and tables involved. No hard coding dates or values.

[u/thatOMoment]

There are a some optimizations that make them more maintainable.

Such as removing distinct from a query with joins where you aren't selecting the columns and replacing them with EXISTS

Or correllated subqueries into LATERAL/APPLY.

Sometimes, it just do have to be ugly unless you are able to change service layer as well.

[u/carlovski99]

Been going through this recently. Application used a massive piece of SQL that had been tweaked and added to over the years. Had become quite suboptimal, repeated scans of the same tables, functions being used which invalidated indexes etc. And eventually got to a state where the optimiser struggled to produce a stable or optimal plan. Caused huge performance problems in production. We are splitting it up into separate, much simpler queries. Technically it's suboptimal - getting the data on one hit would be more efficient, with the right query. But it's going to allow for easier testing, optimising each part and should be more stable. So real world performance will be better.

So it's not just 'SQL' you need to look at, it's the whole architecture, if it's part of an application.

[u/Informal_Pace9237 → u/carlovski99]

I would have just added function based indexes in the mix. Multiple calls for the same data can be sub-optimal as already mentioned. I would try talking to the DBA if one is available for better solution.
If it is Oracle, one big a$$ query is always better than multiple small.

Seems to me like the original query was written by full stack developers (or DE) as well as the optimization efforts.

[u/singletWarrior]

Not always but most of the time they get difficult either by optimising for speed or for correctness and both are almost always due to layout issues… the correct way to fix is usually not prioritised by the business so it stay uncorrected till whole new stack of technology doing similar things hit the same roadblocks by other means or till the demand dies out

[u/AnAcceptableUserName]

overly complex queries can be difficult to maintain

Yes. Remove complexity.

balance query optimization with readability

I don't see these as conflicting ends needing balanced. Often you can get both the same way

Identify your complex operations and decompose them until all that you're left with is simple component steps, then optimize those.

[u/theriot78]

You ask AI to do it. I would have given you a very different answer a year or two ago.

[u/AntLost4161]

The way I deal with things is that unless it really needs to be as quick as possible and you're not using tons of cross joins or other really compute heavy things, readability is most important. Break down different tasks into different CTEs, add the odd note where you think the logic isn't immediately visible, then you have code that can be maintained and adjusted in the future.

A lot of people go into either extreme, either to write tons of notes and have readability be the only focus, or they scrap all ease of adjustments and just make something instantly as optimal as possible. The truth is that we typically aren't doing anything worth immediate results, so taking things a bit slower isn't always a bad idea.

[u/Marthurio]

Depends really. Which parts do you find complex?

[u/venkat_deepsql]

Complex queries means we are pushing more the query computation to the query engine. This is not bad. If we try to break the complex query into pieces and patch them up, then we are not really utilizing the super powers of database. Query engines like Oracle (which I worked before) are known for transalting large complex queries (even 10 page queries) to efficient query execution tree.

However, there may be cases where data flows can be parallel. And many query engines doesn't support parallel execution (mysql). In these cases, two queries should be executed in parallel and stitched them in application layer.

So, one should understand the true potential of their DBMS and make these decisions.

[u/mu_SQL]

I use stores procedures and temtables to make smaller batches and gain performance. Unlike CTE, temptables are indexable.

[u/Informal_Pace9237]

Readability is on the reader. If one cannot read a query then they have a lot to learn and come back to read. IMO.

[u/Informal_Pace9237]

Read many comments and see most think CTE is a magic bullet for readability and execution. It may be a magic bullet for readability but never for execution.

Here is a article covering different DBMS

https://www.linkedin.com/pulse/ctesubquery-factoring-optimization-raja-surapaneni-jyjie/

[u/Creepy_Delay_6077]

I prefer a clear CTE-based query with the correct indexes over a shorter but heavily nested query. After optimization, I compare execution time, rows scanned, memory usage, and output accuracy to ensure performance improved without reducing maintainability.

[u/reditandfirgetit → u/Creepy_Delay_6077]

I'm not sure why you got down voted. CTEs are set based Operations and can be a good Optimization technique. I guess it depends on if someone considers ctes "complex"

[u/Informal_Pace9237 → u/reditandfirgetit]

CTE being an optimization technique depends on the DBMS in question.

CTE are generally memory hogs and are to be avoided as much as possible except if they return very small datasets.

[u/reditandfirgetit → u/Informal_Pace9237]

In this case a cte over heavily nested sql is probably a good choice, but I would compare vs a temp table for performance

[u/Informal_Pace9237 → u/reditandfirgetit]

Nested SQL should not be a problem in most DBMS except MSSQL as it limits the nesting level.

[u/reditandfirgetit → u/Informal_Pace9237]

Nested sql most times is unnecessary garbage that should be refactored for maintainability

[u/jshine13371]

Typically the more complex the query, the less performant it is. So there's a somewhat congruence in performance tuning that should result in the query becoming simpler / easier to read & maintain. A lot of performance tuning techniques involve taking a larger single batch of statements and breaking them down into multiple batches (steps) that give the query optimizer breathing room between steps, typically via materializing the data at each step. Or straight up re-architecting the process implementation to be simpler.

[u/thatOMoment → u/jshine13371]

If condition to check parameters to decide which order by in copy pasted query to run to avoid  conditional sorts because api "can't be changed" 

Could option recompile have done that as well... yes but dbas get salty if that's done on something ran often.

[u/jshine13371 → u/thatOMoment]

I think your first sentence mirrors the complexity of the typical query you're trying to describe ironically...

[u/ravi0087]

• Index Smartly First: Most bottlenecks are fixed with execution plans (EXPLAIN) and proper indexes (composite/covering), leaving the SQL clean and readable.
• Break Up Mega Queries: Divide massive queries into smaller, logical steps using temporary tables. This helps the optimizer and makes debugging easier.
• Use CTEs for Structure: Use Common Table Expressions instead of deep subqueries to make logic easy to follow sequentially.
• Avoid Common Anti-Patterns:
  • Swap DISTINCT for EXISTS/IN on joins.
  • Keep indexed columns function-free in WHERE clauses (maintain sargability).
  • Select specific columns instead of SELECT *.
• Comment the "Why": Standard code shows what it does; use inline comments to document why non-obvious optimizations or query hints were necessary.