This month’s invitation comes from Andy Yun who asked us to share something we’ve learned and which has subsequently changed our opinions. There were two immediate examples which came to mind, the first on a more technical note and the second somewhat more personal:
“Table Variables Are Fast”
Many years ago I’d found myself with a few queries in quick succession where being able to store a temporary copy of the results was beneficial. I’d seen discussions about Temporary Tables and Table Variables and their performance differences – sometimes there would be comments advocating one over the other and sprinkled in would be the “it depends” comments where the folks advised trying it both ways to see which was more effective.
So I set about crafting a couple of test cases for each query using both options of the temporary table and table variables – and you know what, the table variables were faster each time. Well that sort of makes sense right – if its a variable and we write the data into memory its going to be faster to access than putting it into another database which sits on disk right – so the query runs faster, the maths adds up, that must be right, hurrah for science!
Here’s the thing – how did the execution plan look in both cases? how were the estimated and actual record counts looking on the plan? how about the memory grant? what was the difference in numbers of reads or writes for those different plans? – on face value things were looking good but I hadn’t looked at all the metrics which might provide a better solution, I was too focussed on the time element alone.
As time went on it came to a point when the table variable wasn’t working as effectively and it was time to reassess the options and go about re-learning which was the better option. When you dive a little deeper into these two and start seeing how temporary tables can produce better estimates, how they have access to statistics, and how they can support parallelism in the plans – there’s a lightbulb moment when you see these working together and you reassess what was an internal ‘fact’ to you.
So after all of that and weighing up the options, which of them is the better choice to use? Well, it turns out that what I’d originally read was correct – it depends. There’s no right answer every time, it pays to experiment.
When you go into a task with only a few tools at hand you’re limiting yourself from the outset, and it can be made even more challenging if you’re not using those tools to their fullest potential. It never hurts to keep your tools sharp, who knows when you’ll need them.
“Just One More Thing”
On a more personal note, one area where my views have very much pivoted over recent years is around how and where I spend my time and energy. I now have fewer priorities but I make sure those which I have are much more important to me, and I try to focus on using my time more effectively than efficiently – or smarter than harder as it may be said.
It may be familiar to some of you – earlier in my career there was a lot of ‘one more thing’ which could be squeezed into the day, one more person who you can support. Its a warm fuzzy feeling of accomplishment you can get from going a step further, pushing yourself or adding a bit more value.
Approaching things in that way can lead to burnout as it did for me. Being efficient with my time and getting as much in as I could was at times rewarding, but it was also challenging. On the days when something else comes into play whether that be personal or professional, you’re short on one key thing – time.
Time is precious and it’s not always easy to see that, but you can certainly feel it when it comes to the crunch. Having been in those tight positions more than a few times, I decided to try a different approach to prioritising – keep the list of priorities small, keep the most important items in focus at the top, and make sure each of them are given my fullest attention when its the right time. This mean that whatever I’m doing and whoever I’m with they have all of me there with them rather than having one eye on being somewhere else or doing something else right around the corner.
It’s not just the priorities which get the fullest attention too – now if we hit an MI in the office or my personal plans change, that’s fine – I have the capacity to reassess and react and give those things my full focus too. Having this extra breathing room allows for reflection which can be critically important for reviewing and appreciating progress. We need to understand where we’ve come from, where we’re going, and what steps on that journey may or may not be working for us.
By reducing the number priorities down the end results are of a much higher quality. It might sound like I’m just doing half the work (don’t tell anyone though!) but the reality is that I haven’t got many less priorities than I had before, but now there’s a clear purpose, goal and approach to them rather than an infinite scroll of checkboxes.
Time is precious, but I’ve still got 24 hours in my day. I still give as much as I can to whatever I do. Now though, I get to give more of my energy to those things which matter the most and I’m more fulfilled because of it. Make yourself a priority, because you won’t be giving yourself and those around you everything you have if your burnt out on life.
Andy Brownsword 