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.