Voice of the DBA

Author: way0utwest

  • T-SQL Tuesday #74–The Changes

    It’s T-SQL Tuesday time, the monthly blog party on the second Tuesday of the month. This is the first T-SQL Tuesday of 2016, and all you have to do to participate is write a blog post. It’s supposed to be today, but write one anytime you can and look for the invitation next month.

    I’ve got a list of topics here, but watch the #tsql2sday tag on Twitter.

    This month’s topic comes from @SLQSoldier, Robert Davis. The topic is Be the Change, and it’s a good one.

    Quick Changes

    I’m going to write about SQLServerCentral here. Years ago we were updating our email system to send a high volume of email in two ways. At the time we’d considered purchasing software from others, but found the cost to be significant at our volumes (5-6 figures a year). Instead we needed to handle emails stored in our SQL Server database in two ways:

    • Thousands of bulk emails sent overnight, as quickly as possible
    • Quick, high priority emails sent in response to actions

    These two conflicting requirements meant that a simple queue of emails to send wasn’t easy for us to design around. We also needed to deal with the issues of scaling, so we wanted to have mutliple separate machines that could help spread the load.  We were building a small .NET process that would run every minute and send a series of emails.

    Our design process led us to the need to build in priority levels into our table. We couldn’t think of more priorities, but we allowed for them with a smallint. Our bulk emails were inserted with a priority of 2, and the registration emails, forum notes, etc, were stored with priority 1.

    Once we had a separaton of emails, we needed a way to determine what was sent already. To do this, we used a NULL date for the sending date. This allowed each process to determine when new information had been inserted into the table, and needed to be processed.

    This worked well for a single machine. The process would:

    • query for xx priority 1 emails
    • send priority 1 emails
    • update sent priority 1 emails with the sent date/time.
    • query for yy priority 2 emails
    • send priority 2 emails
    • update priority 2 emails with sent date/time.

    The updates actually occurred for each email sent, so we could easily track the time/order of sends for troubleshooting purposes. We would query a few hundred emails each minute, let’s say 500, knowing that was the rate at which we could send emails. We wanted all priority 1 emails to go, so our value for yy would be  500 – xx.

    As we worked to scale things out, we also needed to track what items were queried by which client. Our solution here was to add a machine name to the data, which was blank when emails were inserted, but would be updated by a client with its name as it queried rows. Since we were looking to determine which emails to send, we’d update xx rows with the name of a client process and then query back those rows. The query used the sent date of NULL with the client name to get the correct rows.

    Using a combination of the date sent, the client name, and the priority, we could easily manage detecting and working with changes to this table and build a high volume queue that worked extremely well on SQL Server 2000, and all versions since.

  • The Advent of Code

    In early December, I ran across a post from Jeremiah Peschka on practicing your coding skills. Apart from the points made about practicing skills, he referenced a site called the Advent of Code. It’s a site that uses a Christmas theme and a little gaming to get people to solve programming problems. I mentioned it on my blog, and across a few weeks, a few people in the SQL Server community started working on the problems in SQL.

    Probably like most people that started this, I didn’t finish in December. In fact, I didn’t work on problems most days because life got in the way. However I haven’t given up. Around vacations and other events, I’ve continued to work on solving the problems in multiple ways, using different languages. I’ve tackled problems in Python, PowerShell, and T-SQL.

    I’d urge you to give some sort of programming challenge a try, mostly just to gain some practice in building algorithms and investigating the ways in which you can solve problems with software. If nothing else, the exercises challenge your brain and exercise your mind.

    I’m considering working on something like this for SQLServerCentral next year, with a series of complex problems that you can work on to practice your T-SQL and SQL Server skills. I don’t think I’ll make it an Advent of T-SQL for Christmas, but I do think having a series of challenging problems is a good way to drive your learning.

    If you’ve got suggestions for other programming exercises, feel free to pass them along.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.0MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • Hash Tables in PowerShell–Advent of Code Day 3

    I continue to work on solving the Advent of Code puzzles in both PowerShell and T-SQL after completing them in Python.

    When I hit day 3 in PowerShell, it was a few new tricks to learn, one of which was reading a large string from a file. However the interseting thing for me was learning to work with hash tables.

    The Day 3 puzzle looks at moving Santa around on a grid. We don’t know the size or shape of the grid, just that we get directions in 1 of 4 ways (^<>V) and then must move to a new house. The first puzzle asks how many houses get one present.

    This is interesting, and naturally to me the first thing that occurs is a dictionary. I used one in Python, adding new elements as I received new coordinates. I wasn’t sure how to do this in PowerShell, and ended up searching a bit about two dimensional arrays, thinking I could perform a count there, adding indexes as needed. However I ran into hash tables while searching, and this was a better solution for me.

    The short part of working with hash tables is that you declare them with a simple command. In my case, since I had a delivery at coordinates (0,0), I wrote this:

    $houses = @{“0,0” = 1}

    This uses the @{} syntax to set up a key value pair. Essentially a dictionary. From here, I computed new “keys” from the directions, and could add new values like this:

    $houses.Add(“1,1”, 1)

    Of course, I had to check for existing values, and if there were existing values, I had to increment them.

    $houses.Set_Item(“1,1”, $houses[“1,1”] + 1)

    With that code, I could easily solve the puzzle. However I was struck by the various ways I work with the hash tables. I use braces, {}, to declare the table. I use brackets, [], to access elements and then parenthesis, (), when calling methods. All of that makes programming sense, but it’s something to keep in mind, especially as those three marks mean different things in Python.

    I also learned how to search with ContainsKey and ContainsValue, as well as how to Set_Item and Get_Item, which didn’t appear to work with the ISE Intellisense.

    All in all, it was interesting working with a hash table and good to learn PowerShell supports them. They are very handy constructs when building up a set of data that you’ll need to work with, and you need more than the simple buckets an array provides.

  • 100 Hours

    How long does it take someone to learn to be good at building software in some language? I’m sure many of you have heard of the 10,000 rule that notes the truly gifted in a field need 10,000 hours of practice to become experts and master their craft. There’s some debate about how true that is, but certainly becoming an expert takes tremendous dedication, certainly years of time.

    I ran across a piece that referenced a slightly shorter rule, the 100 hour rule. The rule is fairly simple: “for most disciplines, it only takes one hundred hours of active learning to become much more competent than an absolute beginner.”

    One hundred hours is nothing. That’s a long week or two of active learning to get good at something. But how good? If we look at software development, what can you accomplish in 100 hours? After all, absolute beginner is a low bar. That’s the level my Mom is at and I’m not sure how much I could teach her beyond “Hello, world” in a few hours. Even in 100 hours, I’m not sure she could solve many problems.

    The good thing is that most of us aren’t absolute beginners, especially in software. We have frames of reference for many parts of how computers work. I suspect that 100 hours of deliberate, guided, practical (meaning typing and practicing skills), would turn lots of people into a good junior developer, maybe even a mid level developer. I think many senior developers would be very competent in a new language in a few hundred hours, because they’d learn the syntax translation quickly and then start understanding the tips, tricks, and gotchas that experts in that language might know. The algorithms and problem solving skills are often the same between languages.

    For most of us looking to grow our careers and improve our technology skills, I would guess we could learn a new skill every year, becoming competent, with 100 hours of practice. Reading books and articles is good, but the hands on practice is most important, and that’s what really drives home active learning. That’s something I’d encourage many of you do, with exercises, puzzles, or maybe even start a small project that help you learn. It will help your career, by improving skills, and might even be fun along the way.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.5MB) podcast or subscribe to the feed at iTunes and LibSyn.