Thursday, April 2, 2020

Working from Home

If you are one of the many folks that are suddenly trying to do your job from home, I’d like to take a moment to pass on some things I’ve learned. I’ve been working from home for years. It’s a different mindset. A couple of things I’ve found that might help you deal with this change:
Stay focused. You can get a lot more done at home then in the office. There isn’t the office politics to deal with which give you more time to do your job. On the flip side the job can take over your life. Set yourself hours for work and stick to it. 
Use technology. There is Skype, Zoom, Teams and other programs that can let you connect coworkers and of course your phone. On a gig recently a coworker would typically call me in the morning and he and I would stay on the phone for an hour or more. Not always talking but it was like he was next to me at the office. We could talk about what we were doing and the typical chit-chat. 
Don’t watch TV while working. Some folks can do this, but really its not good. You’ll get less done and feel like you're rushed when you realize you didn’t get such-and-such done. Some music for background noise is likely better and less distracting. 
Eat meals like you would going to an office or job site. Have a breakfast before you start, minimize snacking and have a dedicated lunch time. This keeps you on schedule and like is better for your health then just snaking all the time. 
Get up and move around now and then. Doing a simple domestic task once in a while to help break up the day a bit is good. Or just walk about and check for the mail. Don’t get into a big project, like cleaning the bathroom or painting a room. You wouldn’t do that when at the office or work site so don’t do that when you’re working. 
If you are the manger of folks that are now working from home, you may have the hardest adjustment. This is complex and totally different way to manage folks. In a nutshell you have to trust that they are doing their job. Lording over them and calling them every minute and checking their status on Skype or whatever is a great way to drive them and you crazy. 
Set up scheduled status calls, keep them short. Give them assignments to do and realistic deadlines. Help them with the technology, as best you can get them things they need. For example, having more than one monitor can really boost performance for folks doing a lot of computer-based work. Be open to help them, be it you actually helping them get something done or helping them find the resources they need to get something done. 
Your management skills will really be tested in ways you’ve never imagined. I’ve managed remote teams and it’s a challenge. Hopefully your team will be in the same time zone, that helps a lot. 
Good luck!

Monday, March 30, 2020

Hints in the cloud or not

Hints aren’t hints; they are directives. 
Don’t think of a hint as some way of encouraging the optimizer to do something, it is a demand to do something.  Generally, hints are only “ignored” when you miss spell it or put in something that can’t be done.  For example, have the FULL hint and an INDEX hint on the same table.  There are also cases when optimizing will transforms a query in such a way that hints in effect disappear from the final query that the optimizer really uses.  This isn’t common in most shops but can happen.  

Hints are best used rarely and must be documented when used.

If you find yourself “always” using a hint, then you have a problem.  Two hints I’ve seen used “out of habit” are the NO_MERGE and MATERILZE hints.  Just because you have sub-query doesn’t mean you need the NO_MERGE.   It might not be getting merge anyway and maybe it’s a good idea if it is.  The same basic issues are true with MATERLIZE on CTEs (Common Table Expressions or Sub-Query Factors).  The MATERLIZE hint tells the optimizer to resolve it as a separate query, and turn the results into a temporary table like structure.  And often this is exactly what we want to have happen with a CTE.  But the same can be true of a CTE, it sometimes wouldn’t be merged into the main query and even if it is, sometimes this is a good thing. 

If your testing validates that the hint helps the optimizer do the right thing and you can’t get it any other way, document it’s use.  This documentation, doesn’t have to be elaborate and is really for you.  Always document the code as if you will be working on this code again in two years and have no recollection of working on this code before.  Help yourself understand what you did today.  

Hints can be a fast fix for a bug.

I know this may be a shock, but there are bugs in the optimizer.  This is where the use of a hint might be a better solution then some elaborate coding trick.  Cardinality can be an issue here, and the CARDINALITY hint can help.  And CTEs again can be a problem.  If the cardinality estimate is way off for a CTE and that CTE is used many times in the main query, then the odds of the main query having a good plan are slim.  

If you can’t rework the code of the CTE to get a better estimate then a CARINAILTY hint can really save the day.  BUT this is a hint that could go bad over time.  Always put in a comment as to when this was used and that it will need to be checked in the future.  There may come a time it wouldn’t be needed or that the value used needs to be updated.  Keep in mind that the number needs to be in the correct magnitude, not exactly right. 

Hint should be viewed as a temporary fix and not the final solution. 

All this leads up to this, using hints are temporary fixes to a problem.  Often, we are tuning around bugs or poor coding.  If you don’t have access to the author of the query then significate rewriting of the code can be difficult or impossible.  Using a few well-placed hints can get the code to at least run in an acceptable amount of time.   This can at least get thru an “emergency” and hopefully there will come a time when the code can be rewritten or the bug fix is applied and the hints are no longer needed.