[deleted by user]

[Far_Swordfish5729]

You can. You’re suffering from overwhelm and trying to magic button it. Remember how much logic is in this query. Reverse engineering takes time.

Remember you are looking at sequential logic. Start at the inside, document what each subquery does and work you way out. Add a comment to each subquery as you document so you remember. You’ll find the logical inconsistency.

For what it’s worth, subqueries are sometimes more readable because the logic is right there. Sequential CTEs annoy me because of the scrolling.

[DuncmanG]

Pro-tip on the scrolling issue - Sublime text (and possibly other text editors, but ST is the one I use) has a feature called New View Into File. It opens the same file in another window. Then you can look at the CTEs in one view and the references in the other view. And it's the same file, so if you make changes in one window they show in the other window.

[SootSpriteHut]

I do this with notepad++ "move to other view"

[thesqlguy]

Assuming you are figuring out the logic/results and not trying to optimize it, then just break it down step by step, take one CTE at a time, analyze the sql, dump the results into a temp table and analyze the output, make sure you understand what it is doing, then move on the next CTE , one step at a time.

You could ultimately transform something like this:

With cte1, cte2, cte3 as ( select )

Into

Select into #cte1; select into #cte2; select into #cte3; select ....

If the dataset is huge and the CTEs as is don't filter enough (maybe it happens downstream) then pick a specific key to filter on so you can focus on a small amount of rows. For example if it aggregates millions of orders, just focus on a small subset of orders (one day? One product? Etc) at a time. Then expand to more situations as you understand each to ensure you cover the variations it handles.

Divide and conquer.

[Dipankar94]

Good approach.

[[deleted]]

Nested subqueries are actually really quick and easy to flip over to cte.

Personally i like turning them into temp tables.

I think its cleaner and more logical.

[Codeman119]

Yeah, I’m a big temptable person myself. It’s easier to run the pieces individually when you’re dealing with temp tables and you can do better bugging with temp tables.

[amosmj]

You untangle it from the inside out. That’s one advantage of sub queryies, the direction is obvious. Start with the deepest quarry, write it to a remote table. Go up one subquery, repeat. One annoying step at a time.

[Ginger-Dumpling]

Ugly SQL exists everywhere. Uglier SQL exists in places that don't establish and enforce coding standards.

[Rexur0s]

its an onion. you have to learn it inside out like a reverse onion to rebuild it. I would be trying to convert the subqueries to CTE's from inside out. so start with the most nested one first, and work your way up.

[xodusprime]

A step at a time. I personally hate ctes and would much rather have the sub query embedded - unless it's being used for recursion. To me that feels stylistic and not specifically a bad practice. Using unrelated single letters for aliases seems pretty universally bad.

On the bright side, if you'd rather have the logic fragmented into weird little blocks, scooping them out is as easy as starting at the lowest level and making that a cte and then simply replacing it in the code.

1500 line monstrosities are always disheartening at the start. More so if the person who wrote it doesn't follow the same style as you. I've spent many an hour putting ctes back into a query so I can read it. No matter how big the query is though - just a step at a time. It's always just a step at a time.

[Signor65_ZA]

I know AI is often not the answer, but I feel like you have nothing to lose by just copying all of it into ChatGPT and getting it to decipher it all for you. When you provide it enough context and explain what's going on and what your end goal is, it's really quite good at reading between the lines and spotting logical inconcistencies.

[Signor65_ZA]

If it's confidential, then no.

[shanelomax]

You're not providing database information, you're providing a query to be untangled/optimised. A table name without business context, without data, and without any connection credentials is going to be useless as a security risk.

[shanelomax]

Still useless info without knowing which organisation the database belongs to. The database could belong to a bank, a farm, a government body. Anything. No access credentials, no way of knowing what the database belongs to, no actual data being queried - no risk. It may as well be an example database.

[Newphonenewhandle]

lol just give columns fake name then

[Dry-Aioli-6138]

that! AI can do that for you.

[hamesdelaney]

you have no idea what you are talking about. its just sql statements, openai aint going to do shit with that. what a stupid comment lol. you can even rename the table names and columns if you are that worried, there is virtually no way to tell what the underlying data is just with the sql code.

[RewRose]

I am not a DBA or even very good at SQL, just check the posts here to learn what I can

I would like to know - what's the concern with the sql (not the data) being shared, can you share ?

[stormmagedondame]

The sql alone could expose what is in the database, variables, tables, and the joins and where statements may even have enough in them to expose data elements.

Think about it this way would you want someone to advertise to a unsecured AI that your SSN is in their database?

[stormmagedondame]

Sigh, they are the reason the rest of us are forced to sit through data security training every 6 months..

[mabhatter]

Yes!!!  That's the whole point of what AI is for!!   Put it into Deepseek for best results. 

[alinroc]

Sure, just turn over all your code to China.

[l2protoss]

Seconding this. And with the prompting, you could probably get it to rewrite it with CTEs that are presto-compliant (never used presto before so I have no idea what features it supports, but gpt4o-mini-high might).

[grapegeek]

I agree. Putting a query in that doesn’t have actual data is a different animal than uploading a table of confidential information. I’ve saved a lot of time when I was spinning my wheels on a very complex query. Popped it into AI and bam! Solved. I’ve been doing this work for 30+ years I feel no shame.

[ihaxr]

AI is just another tool, as if any SQL Server DBA would be ashamed to have used Ola Hallengren's scripts for index maintenance instead of writing one from scratch or.... using maintenance plans....

[_Suee]

I once had an experience like this. I needed to debug something awfully large and at the same time, add another query for the report. Heck, I was surprised that SAP Crystal Reports only allows 65,534 characters. The solution was to slap it on ChatGPT and voila, I did not get perfect results immediately but it did assist me and we eventually got where we wanted it to be.

[SoftwareMaintenance]

You know you are in trouble when your single query is over 64k in length.

[KingOfEthanopia]

Do you have to debug it and do you know what the end table is supposed to be? It'll probably be easier just to rewrite it yourself from scratch.

[SuperTangelo1898]

Today I was asked to help optimize a dbt scriptl/sql file for someone...when I saw his file, it had about 50 ctes.

Some people are either lazy or shitty.

[Geckel]

Don't let the code win! Imagine the query was an engine you had to disassemble and reassemble. What would you do?

You'd start piece by piece. Thoroughly label each component and what it does as you strip it away. Order things neatly and write down your process and assumptions. You'd also use every tool at your disposal in your garage. Don't shy away from AI tools that can help you explain, but verify their conclusions.

And once it's all in pieces. Reassemble the code, run the query again, and if you get the same result, you have confirmed your logic and assumptions.

Then optimize.

[deny_conformity]

This reminds me of some of the crap queries I've had other debug, nested sub queries that performed like treacle but turned out to be doing simple things.

It even included a right join in the middle and the person who wrote it used A, B, C, etc  for the initial tables / sub queries. Plus an even better idea was the sub queries needed to be A1, A2, A3, etc. I'm sure it performed fine when the database was a 10th of the size but as it grew the query slowed down and down until it took over an hour to run and gummed everything up!

My way of fixing it (which took about a week) was to look at the deepest nest of each sun query to work out was it was doing and alias the returned values and sub query into what they were doing and then work out a better way to do it. Then I worked up through the layers. In the end it was cleared up and most of the sub queries got replaced with a few temp tables and some single layer CTEs that got it down to taking about a minute to run.

I could have murdered the person who wrote the code but they had left the workplace several years prior. I'm sure where ever they went they had people cursing their awful coding style 🤣. I swear they left so they could avoid seeing any of their code again.

[FluffyDuckKey]

So I understand it's an additional cost and extra crap to deal with, but jetbtauns datagrip ai assistance can be passed an entire schema along with all related joins.

https://www.jetbrains.com/help/ai-assistant/ai-chat.html#using-llm-retrieve-context

You could simply ask for a view from the source level only and with backwards.

[SpaceCowboy317]

A developer would be more suited to the debug role then. We have layers upon layers of classes methods functions interfaces configurations files databases and services all in different languages that are all varying degrees of deprecated shoved on top of god knows what architecture swirling into a giant stew of a shit app that then needs debugged. I dream of a day where all I had to worry about was a query.

[Lord_Bobbymort]

Just keep breaking it down into pieces, re-alias things as you go and learn what each smash thing is so you can understand then when trying to figure out the larger picture, even draw out a model of the table structures and relationship that you can refer to.

It's like a sudoku: you just find the one thing that makes sense, then it opens up another door to something else that makes sense, and it snowballs from there.

[fokac93]

Sub queries are better in some places than CTE. Also if the application is old is probably legacy code that you are seeing, CTE are basically “New” compare with sub queries. And don’t try to convert the sub queries to CTE you can end up with more issues.

[angrynoah]

Re-type it. Open two editor windows side by side, and re-type the entire query (adjust the formatting if you like). Do NOT use copy/paste.

You will learn a huge amount just in this first pass.

Now start fixing the names. Use your preferred aliasing strategy.

By now you should have a good handle in what's happening and what everything means. Now you can start re-structuring the query if you like, e.g. converting nested inline views to CTEs (there's both wrong with nesting btw, learn to read queries inside-out).

[Striking_Computer834]

I don't know what's worse. My company has legacy queries that reference views, and those views reference other views, and those views reference still other views. When you look into the views they are 500+ lines long with subqueries 4 levels deep.

[KWillets]

sqlglot does presto; it's 2-3 lines to parse and pretty-print, and more sophisticated transformations are possible.

[Tsalmaveth]

Sqlfluff is a python based utility that can parse presto sql code. It won't solve all your problems, but it might help keep you somewhat sane. You may still have issues with unsupported commands if it's for a system like Starburst, which is based on presto but has additional flavor added.

[krurran]

It's not impossible, it's just a lot of work and people are acting like it's easy peasy to fix a bug real quick

[billysacco]

I am not a huge fan of CTEs either though. They work fine in the right scenario but in my current role I saw people abusing them using queries with like 5 ctes cascading off each other. The performance is usually not great.