top of page

T-SQL Tuesday - Code that makes you go 'mmmmm'

This one is in response to the current T-SQL Tuesday topic being hosted by Erik Darling ( / Erik Darling Data – SQL Server Consulting, Education, and Training) , a man i've listened to on podcasts and read articles penned by for many a year now and sponged an awful lot of knowledge from.

What code has made you have 'a moment' over the years? A happy, good, eye opening moment of realisation. It can't be your own code, which is handy as my code usually makes me weep, not from tears of joy either.

I have many, usually when something that I either didn't understand (a lot), thought I understood but didn't (also a lot), or just didn't know I could do.

The main experience i'm going to talk about is probably going to be WAY simplier than other contributors I expect, and going back years*... but is simply based around Join Ordering, and a method you can use for basic performance tuning. (Spoiler, using rowgoals.)

(The very first one was the basic premise of joins shown with code visualised by a Venn diagram, but that's when I was just a primordial tadpole in a small IT pond.)

"Join the tables in a way that the result set is filtered as fast as possible in the query plan."

Wait a minute fishing boy, that's not exactly rocket science is it mate?

SQL does this for us (usually) when generating a plan by use of its statistics. I'd never really thought about this previously, probably why i'd struggled with poor plans.

but... then a colleague mentioning...

"methods to influence how SQL chooses the ordering" - I think I then followed this up from code/presentation from Adam Machanic as to what, where, how.

Fooling SQL Server into thinking there is more rows in a resultset than there is, making it join those tables first, enforcing a rowgoal. In the example below, forcing the join between Production.TransactionHistory and Production.Product to occur first. Spiffing.

  ( --Force a rowgoal
  SELECT TOP(2147483647) 
  FROM  Production.TransactionHistory AS th
    INNER JOIN Production.Product AS p ON
      p.ProductID = th.ProductID
  ) AS x
  INNER JOIN Production.ProductionListPriceHistory AS plp ON
    plp.ProductID = x.ProductID
    AND x.ActualCost < plp.ListPrice
    AND x.TransactionDate BETWEEN plp.Startdate and ISNULL(plp.EndDate,   '99991231');

Row...Row...Rowgoal your boat...

Right there, simple.

It was like the moment in The Blues Brothers where the ray of sunshine shines through the church window and strikes John Belushi. I won't quote his response from the movie, its a family blog.

It really made sense, how SQL estimated rowcounts (I was aware of statistics), and how it then started to estimate the join ordering to create a 'reduce rowcount quickly' plan. So if (when) it got something wrong, then how we could twist its arm slightly to bump it in the right direction again.

<Enter Lightbulb, stage right>

So many query plans after this highlighted potential issues instantly. Oh, these were the same plans that i'd looked at previously, but now the big fat row count/read line of doom reaching far to the left was jumping out.

Other query techniques without Rowgoals then suddenly made sense, if I have to rewrite a query, then think about if there is scope to subquery/cte the inner joins first.

If I do have to use this method, then I tend to comment the code to explain why this is here, otherwise it looks very strange. Yes, theres a FORCE ORDER hint , but I tend to avoid that if at all possible, the sledgehammer is a bit too big there.

All it took was that one little bit of simple code, and it unlocked my head and gave me my moment of sobriety, sorry, I mean clarity.

I don't know what I did before this to be honest (probably very little), and just spammed indexes like they were going out of fashion. Now my head was armed with just a better way of thinking about plans and code.

Its so obvious now I look back on it, always the way.

I still get it wrong though. :-)


* I realise I am opening myself up to abuse from friends/colleagues and former colleagues by them insisting that this was not years ago, and only last week.


Rated 0 out of 5 stars.
No ratings yet

Add a rating
bottom of page