Voice of the DBA

Author: way0utwest

  • Cursors Without Performance Penalties

    One of the very common ways that many developers learn to write code is through loops. Almost every language as some sort of FOR and WHILE looping structure. Many people build these in T-SQL, which often creates performance issues as the engine inefficiently deals with the RBAR (row by agonizing row) processing.

    That seems like it might change in the next version of SQL Server. There is an interesting research paper from the SQL Server team that explains how they are investigating a technique that will make decode the inner workings of any code that is inside a cursor and apply the work efficiently to any table operations. If this actually turns out to be true, then cursors in T-SQL might be like cursors in PL/SQL, where this is the preferred way to write code.

    Suddenly cursors will have no real performance penalty. If that’s the case, I suspect we’ll start to see lots of new refactored code in many organizations. Loops make sense and are easy to read. I don’t know that I’d change any existing code, but I’d consider changing new code. Do you feel the same way? I wonder how many of you would prefer writing loop based code as opposed to complex single statement SELECTs.

    I’m not sure when we’ll actually change our habits, and you probably agree if you clicked the link. Today is April 1, or April Fool’s Day, so this whole thing is completely fabricated. Hopefully you smiled and this helps you cope with the day a little better.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.

  • The Power of Git–Getting Code

    I find git to be incredibly useful, and this is a quick example of one of the ways that this matters to me.

    This post is also available in video format here: https://youtu.be/Drwt6nseeJM

    I got this link from Jes Pomfret for a Jupyter notebook she’d built: https://github.com/jpomfret/demos/blob/master/Notebooks/dbatoolsMorningChecklist.ipynb

    I see this on GitHub, but this isn’t easy to download, though this could have just been a file share.

    2020-03-25 10_47_31-Window

    However, since it’s on Github, I can easily get this as a copy of code that stays updated. To do this, I’ll copy, or “clone” the code to my machine.

    First, I go to the root of the repository, which is really a folder of code. This is the “demos” repo in this case (https://github.com/jpomfret/demos)

    2020-03-25 10_48_20-Window

    There’s a large green button on the right, that is the Clone button. Click the clone button and copy the URL.

    2020-03-25 10_48_41-Window

    I’ll do this in a cmd prompt, though you could use the open in GitHub desktop, or any git client. I prefer the cmdline, because this is really how I learn and get used to doing automation. GUI tools make this easy, but the cmdline is what I want to remain familiar with for DevOps. I can clone this with easy code:

    git clone https://github.com/jpomfret/demos.git

    You can see the results here.

    2020-03-25 10_49_57-Window

    I have all this code now on my machine. I can open this notebook in Azure Data Studio (ADS). Here I’ve picked “Open File” in ADS and browsed to the folder.

    2020-03-25 10_50_39-Window

    And I see the notebook.

    2020-03-25 10_50_45-Window

    If I don’t like this, or want to get rid of it, I delete the folder.

    rmdir demos /s

    And the code is gone.

    2020-03-25 10_51_35-Window

    There’s more to this, because I can keep the code up to date with Jes’ changes or even copy the code to my own workspace on Github and keep it up to date there with my machines, or share with others.

    There’s a lot of power to Git, and you can easily get started by doing what I did here and testing Jes’ notebook. Install git today and try this out for yourself.

  • Daily Coping 1 Apr 2020

    I’ve started to add a daily coping tip to the SQLServerCentral newsletter, which is helping me deal with the issues in the world. I’m adding my responses for each day here.

    No April Fools for this one. This is a big one, and it’s likely important. The tip today is “call a loved one to catch up and really listen to them.”

    I called my Mom recently. She’s in a retirement community, and locked down. There have been some illnesses among the staff and to avoid spreading, they have reduced some contact, ask most people to just stay in their rooms or apartments and deliver food. Fine for my Mom. She has a kindle, a piano, and enjoys the quiet. She exercises in her room and enjoys it.

    Good catchup there.

    Not so good for me as my Mom is fairly conservative and as of last week, she didn’t think we needed the shutdown, we should have all businesses open and “we don’t have that many cases.” This was before the US surpassed all other countries.

    I try to be understanding and appreciate other points of view. I also think that a number of very conservative outlets are criminally negligent in downplaying the seriousness of this. I do think some liberal/progressive news outlets may be sounding too many alarms, but in the place where mistakes are serious, I can tolerate a little more chicken little news than dismissal.

    So I listened, debated a bit, and tried to provide her company, swallowing some of the stress that this caused.

    I’ll also have to do this again every few days. Looking forward to more virtual happy hours with friends that take me away from news.

  • Goal Progress Report for March 2020

    I made goals at the end of February, when I returned from sabbatical. Since then, the world has been turned upside down with a pandemic, and I’ve been struggling to cope.

    However, here is the progress report.

    Reading

    As I write this, I’ve had a reminder from Outlook on my screen for two days. The reminder is to work on goals, but I’ve managed to not do this. Mostly because I’ve been busy and haven’t had the time to actually combine career work with work work.

    In addition, at the end of the day, I’m worn out. I’m not looking to tackle another project. Even on weekends, I’ve been taking care of some other things and haven’t wanted to work on something. I haven’t had the mental focus.

    That being said, early in March, I did get through 4 chapters in Pro Power BI Desktop, actually following along and building my own reports.

    No other progress. I’m falling behind here.

    Projects

    One of the things I wanted to do was build a Power BI report. I actually started, though not from a database. That’s easy to correct later, but for now, I just used Excel. I got a partial report completed, but I was struggling with display v aggregates. As a result, I stopped for now, hoping to do more Power BI work.

    Falling behind here, but the whole world is. I’m giving myself a break and hoping to get more momentum in April.