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.