Getting Functions Out Of The Where Clause

Raised Right

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.

Think about built-in string and date functions, wrapped around columns, and the problems they can cause.

These are things you should actively be targeting in existing code, and fighting to keep out of new code.

Nooptional

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

More Work

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!

3 thoughts on “Getting Functions Out Of The Where Clause”

Leave a Reply

Your email address will not be published. Required fields are marked *