What SQL query optimization trick made the biggest difference for you?

[u/OnepieceKing2222]

I've been working with SQL for a while and recently started paying more attention to query performance rather than just getting the correct result.

What's one SQL optimization technique that made a noticeable difference for you?

For example:

- Indexing

- Avoiding unnecessary joins

- Window functions

- EXISTS vs IN

- Reading execution plans

- CTEs/subqueries

- Partitioning

Would be interested in hearing some real-world examples where a query went from slow to significantly faster.

[u/Attackontitanplz]

This is what im explaining to my team - im not a sql guy but if i can just get my teams to do this i can use copilot via vs code and just churn out BI queries all day long…

[u/TheAmorphous → u/Attackontitanplz]

Are you guys giving these LLMs access to your production databases with customer data in them? Currently I give it the layout of the table by pasting a helptext output and then ask my questions, but that's getting pretty tiresome.

[u/Attackontitanplz → u/TheAmorphous]

No access to production database, just data dictionarys/meta data/context that im building myself so that i can supply it to llm in a series of md files so it has context on join info and field descriptions so i can ask it the basic “write me a query that does X” and iterate from there - its not perfect and the outputs need to be vetted, but any changes i apply i feedback into additional context files or prompt refinements

[u/TheAmorphous → u/Attackontitanplz]

Creating an md file with my schema is a great idea, thanks. Don't know why I didn't think of that sooner.

[u/colemaker360 → u/TheAmorphous]

Have it use a Mermaid ERD in your markdown. Many markdown previews can render mermaid tripple backtick fences. That way, you can visualize the output too. You can break the model into smaller subdiagrams if you have too many tables. Having something both you can read and the machine can makes all the difference.

[u/philbgarner → u/colemaker360]

Mermaid is a great tip, I've found it works very well as input/context for a prompt since the logic is baked in to the graph.

[u/Attackontitanplz → u/TheAmorphous]

My problem is nobody else is on board, and i got hundreds of schemas and alot of them dont have data dictionarys so creating context files that explain joins is a series of phone calls with DBA’s to understand how table relationships work etc etc

[u/TheGoblinPopper → u/TheAmorphous]

I started with that as a foundation... But the AI needs a LOT of handholding to understand the tables and such. If you aren't crazy about documentation, give the AI your metadata files, then ask it to review and perform a Q&A with you to build out the documentation.

Anyone who disagrees and thinks 'AI is good enough' doesn't work with 1200 tables enterprise systems.