Writing T-SQL code for the engine, not for you

2024

TL; DR

Often because of how quickly we want to write a T-SQL query we create more extra work for the engine. People underestimate the power of rewriting a T-SQL query, but in the right cases, it will provide better performance. Let's look at some scenarios where this is true!

Session Details

You've been requested to analyze/change a query to include some new requirements. You are not sure what to do and your colleague/boss says "it's a quick change, you JUST need to copy-paste that block and change the filter". You are happy because after all, it's a quick change. You deploy the query change to production but now the business complains the Power BI report that was running in one hour now takes an extra five minutes.

You might think that was expected because you are getting more data.
However, this is where you should step back and ask, was the one hour ok? Couldn't it be faster already?
You have to stick around to find out. Regardless, let's stop pretending that running a query in one hour is OK when it should be possible to make it run in a couple of minutes by re-writing it.

This is just one of the development patterns I see almost every single day when analyzing and tuning T-SQL queries.

In this session, we will see some code patterns that won't scale and other pitfalls while writing T-SQL and which options we have to fix them to improve performance.

NOTE: This session is not about indexing.