In a programming language like C# or Java, you tell the computer what to do, in order.
Get the customers from California, then get their invoices, then sum the total.
SQL, on the other hand, is a declarative language where you declare the shape of your result set:
Get the total sales from Californian customers.
You’re declaring the output that you want, not the methods the database server uses to build it. Oh sure, you CAN use SQL to declare the shape of your query plan, but generally that leads to heartbreak and despair because:
- You don’t know the database engine’s internals as well as you think
- You’re backing the database server into a corner, forcing it to use specific techniques, when it has better ones available at its disposal
- Your data distribution is going to change over time, meaning the method you use to process the data today won’t make sense in a year or two
- Your database server is going to change over time as you patch and upgrade it, meaning it has newer and better methods to process data, and it needs the flexibility to leverage those
- Your hardware is going to change over time, and the query plan that made sense with 16GB RAM won’t make as much sense with 512GB RAM
When your application is young (1-5 years), use as few engine-specific hints as possible in your queries. Don’t tell the database server which index to use, what order to do its processing, or what kinds of joins to prefer. Give it as much flexibility over time as possible by keeping your query simple and letting the engine make different decisions as your data, database version, and hardware changes.
When your application is mature (5-10 years), then your data distribution, database server version, and hardware has started to specialize. This is the time where you can start bossing the database engine around with specific hints to tell it how to do processing.
But if you use those hints when your application is young, you’ll find that you’re constantly revisiting old code, having to fix query performance, because the query hints you used on a 1-year-old application just don’t make sense anymore, and they’re making performance worse instead of better.
6 Comments. Leave new
Even if the application is a little older, a significant re-architecting of the tables/databases can also cause those hints to bubble into boils…Thanks for the reminder
“ won’t make as much since with 512GB RAM”: since might be a typo for sense.
Doh! Good catch, fixed. Thanks!
and don’t do display formatting of data in SQL unless you really want you to aggravate your DBA. (lets do a big complicated string function to make a date say “May tenth, 2022” in SQL to show up on screen) Definitely don’t use convert() at time zone to filter a date range stored in UTC in a table with 800 million records if you don’t want your DBA to set your database max DOP to 1 for a little while until you decide to be good again.
completely agreed.
What I see in my company (cantonal gouvernment, CH, about 700 different apps), I must say that the overall knowledge of developpers (we mainly work with external consultants) how we should create data models and requests is dropping (usage of ORMs?).
Sometimes senior developpers (as they call themselves) have basic knowledge of SQL (maybe expert knowledge in their programming languages).
Just recently I had a request to create indexes on a new table without giving me the requests (“please create some indexes that will be useful” – without saying useful for what)