TSQL Tuesday #200 – “When I’m looking at a query, I bet it’s bad if I see ____.”

T-SQL Tuesday is the blog party started by Adam Machanic (b|t) over a decade ago and is now maintained by Steve Jones (b|t) on tsqltuesday.com. Each month, lots of people blog their thoughts on a common topic.

This month’s T-SQL Tuesday is from Brent Ozar (b|t) who is asking us to share the answer to “When I’m looking at a query, I bet it’s bad if I see ____.”

 

 

Give me “Just a Minute” to review the code – it should be written without “Hesitation, Repetition or Deviation”.

When doing frequent Code Reviews, I get concerned when the Code that I see looks “too familiar”. Some patterns or bits of code come up time after time. This pre-dates AI writing bad code – TBH many developers or Report Writers were able to (and no doubt still can) make a far worse mess than AI with patching together code.
This blog post has been kept short by the omission of Code samples, partly to protect the innocent, but you know who you are!

Hesitation

One type of Query meanders through multiple steps, gathering lots of data, storing sub-results in Temporary Tables.
Right at the end, you find a WHERE clause that filters the results in a way that could have been applied to the very first step!
Hesitation on the Filtering causing far too much data to be processed.
Review the code, get the WHERE clause to take effect as close to the start of the processing as possible.

Repetition

People often have a particular piece of Code that they have used in the past to give them what they wanted. It worked just fine, but then they wanted a bit more data – so they will adapt what they have, perhaps repeat a block of code with slightly different parameters, and join the results at the end.
Again, this can cause a lot of additional Data to be processed, SQL Server gets confused when joining too many Tables

Eventually, you end up with a monster Query that is highly repetitive and incredible inefficient. Sometimes the SQL Query Optimizer can handle it, but even if it can, doesn’t mean it should!

Deviation

How about returning just the Data that you require. SELECT * is a favourite here – does the Query need to return every column? Even if it does, the Field names should be specified to ward off errors in the future with the Application that is processing the returned Data. Keep it concise, if you need it, report or use it in a join. If you don’t, leave it out.

Review your code, maybe even hand it to AI and ask it to check for problems mentioned above.

 

I am indebted to Ian Messiter for “Just a Minute” and Nicholas Parsons for bringing it to life (BBC Radio 4), Adam Machanic for coming up with the idea of TSQL-Tuesday, and Brent Ozar for coming up with the topic for this month’s TSQL-Tuesday.

Best wishes, Nigel



Nicholas Parsons, who once turned directly to me in the audience, before responding to my heckle with “Thank You” and a sigh.