It seems like every time I check out a server, the query plans are a nightmare. Users are freaking out, and management’s coffee is more Irish than Colombian.
Many times, the issue is that people are using presentation layer functions for relational processes. The where clause, joins, group by, and order by parts of a query.
These are things you should actively be targeting in existing code, and fighting to keep out of new code.
When you’re trying to get rid of them, remember your better options
- Cleaning data on input, or via triggers: Better than wrapping everything in RTRIM/LTRIM
- Using computed columns: Better than relying on runtime calculations like DATEADD/DATEDIFF
- Breaking queries up: Use UNION ALL to query for either outcome (think ISNULL)
- Using indexed views: If you need to calculate things in columns across tables
- Creating reporting tables: Sometimes it’s easier to denormalize a bit to make writing and indexing easier
- Using #temp tables: If you have data that you need to persist a calculation in and the query to generate it is complicated
Note the things I’m not suggesting here:
- CTEs: Don’t materialize anything
- @table variables: Cause more problems than they solve
- Views: Don’t materialize unless indexed
- Functions: Just no, thanks
Yes, finding and fixing this stuff is more work for you. But it’s a whole lot less work for the optimizer, and your server, when you’re done.
If that’s the kind of thing you need help with, drop me a line.
Thanks for reading!