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 (https://twitter.com/erikdarlingdata / 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.

SELECT
  x.TransactionID,
  x.name,
  plp.ListPrice,
  x.ActualCost
FROM
  ( --Force a rowgoal
  SELECT TOP(2147483647) 
    th.TransactionID,
    th.ActualCost,
    th.TransactionDate,
    p.Name, 
    p.productID 
  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. :-)


Rod



* 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.

Comments

Rated 0 out of 5 stars.
No ratings yet

Add a rating
bottom of page