Voice of the DBA

Author: way0utwest

  • Hiring Heterogeneously

    This editorial was originally published on Jul 22, 2013. It is being re-run as Steve is on vacation.

    I wrote recently about tech interviews and hiring, and even on an interesting interviewing technique for senior people. However we need to hire more than just senior people. We need to hire junior people, and intermediate people. Those roles are important for both growing the experience of your staff, and not getting caught with a “” as a bottleneck or single point of failure. No, that’s not Brent Ozar, though someone with his talent might be the single point of failure in many organizations.

    Not everyone can be a superstar-expert-architect that decides how the system is built. Not all architects should spend time coding basic insert/update/delete code or adding clustered indexes to tables. We need a variety of talent levels that can get complete different types of tasks. There is tedious administrative work, supporting roles, necessary, though unexciting work like reviewing security, logs, audits, and more. While you can automate much of this busy work, there are still tasks that we must assign to people.

    There’s another consideration as well in hiring that all too often people overlook. Hiring too many people that are too similar, who may think alike, who may view problems the same way can lead to an environment that doesn’t grow and expand, that loses it’s creativity over time. There’s a great quote that says “Where all men think alike, no one thinks very much” (Walter Lippmann). It’s important to have a diversity of opinions, and when you hire new people, you should consider this. You want talented people, and people that get along, but not necessarily all thinking about problems in the same way.

    The world is a richer place for the diversity we have, and varying opinions, thoughts and ideas. We don’t all get along, but many of us can work together with mutual respect, considering each others’ viewpoints as we work to build solutions to the problems we face.

    Steve Jones

  • Hiring Guitarists

    This editorial was originally published on July 1, 2013. It is being re-run as Steve is on vacation.

    Hiring good people is hard, though in some cases it might not matter as much as others. If I am hiring entry level developers or DBAs, I have a lot of candidates, I’m not investing a lot, and I shouldn’t be too concerned if I have to let someone go and find a new employee. Given the fact that the people are often unproven in this case by definition (they’re junior levels), I should be prepared as a manager to make more mistakes at this level.

    However at the senior level, hiring needs to be done more carefully. The high costs, the limited number of candidates, the responsibilities I give senior people, along with the trust I need to bestow upon them means that I can’t afford to make the same percentage of mistakes at the senior level. Most importantly, I don’t want to hire expert beginners instead of experts at this level.

    I ran across a post on a way to hire senior developers that I thought was very interesting. In particular, I was struck by the analogy of hiring a guitar player. For the most part, I’d agree that if I wanted an experienced guitar player, I’d want the expert, not the expert beginner. If for no other reason than I don’t want to argue with an employee that does things my way, including making the same mistakes I’d make, and then explaining to me this is how I had specified things to be. I want a senior people to warn me, and influence me to do better.

    I’m not sure how I’d devise a similar level of test for a senior level DBA, though I do like the idea of giving them some scenario or simulation that has flaws or problems and seeing if they’d correct them, point them out, or leave them in place as they moved on to “play” the scenario. I’m sure there’s a way to do this, and a balance between how to evaluate the responses.

    Ultimately I want senior people to fix things, make them better, and provide a smooth path to increase quality. I want them to point out flaws, and warn me about issues. If I insist on making poor choices, I want their support, but if they can influence me to do better, I’d prefer they did. That’s one mark of a senior person.

    Steve Jones

     

  • Using Merge–#SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    I was playing with some data, loading it into staging tables and then moving it to a real table. I typically have done this with T-SQL, writing efficient upsert code that works well. However, I haven’t used Merge in a long time and thought I should practice a bit with the structure.

    Note: Merge isn’t that efficient and most experts do not recommend it (Aaron Bertrand, Dwain Camps). If you decide to use Merge, make sure you are aware of performance implications with your system. It should be fine with smaller sets, but be aware of potential issues if your data scale grows.

    There’s a nice Simple Talk article on Merge that helps you understand how this structure works. There are lots of tips and tricks with merge, but the basic idea is that I can decide to merge data from one table into another and handle the various cases of rows that exist or don’t exist, and what to do. This is the classic upsert, where we insert new rows and update existing ones.

    A Quick Scenario

    I was actually playing with some SQL Saturday data, so let’s use that and set up a few tables. We’ll set up an Event table and an EventStaging table with some data.

    CREATE TABLE Event
(EventID INT PRIMARY KEY CLUSTERED
, EventName VARCHAR(200)
, City VARCHAR(100)
, EventDate DATE
)
GO

CREATE TABLE EventStaging
(EventID INT PRIMARY KEY
, EventName VARCHAR(200)
, City VARCHAR(100)
, EventDate DATE
)
GO

INSERT Event
  VALUES 
   (1  , 'SQLSaturday #1 - Orlando 2007', 'Orlando', '2007-11-10')
, (4  , 'SQLSaturday #4 Tweener(Sun) - Orlando 2008', 'Orlando', '2008-06-05')
, (2  , 'SQLSaturday #2 - Tampa 2008', 'Tampa', '2008-02-15')
, (3  , 'SQLSaturday #3 - Jacksonville 2008', 'Jacksonville', '2008-05-03')

insert dbo.EventStaging
  values 
   (4  , 'SQLSaturday #4 - Orlando 2008', 'Orlando', '2008-06-07')
, (5  , 'SQLSaturday #5 - Olympia 2008', 'Olympia', '2008-10-11')
, (6  , 'SQLSaturday #6 - Cleveland 2008', 'Cleveland', '2009-02-01')
, (7  , 'SQLSaturday #7 - Birmingham 2009', 'Birmingham', '2009-05-30')

    The data is loaded into EventStaging and then needs to move to Event for the application. If you examinet the data, you’ll see that the events with ID =4 is in both tables with different data. Events 5, 6, 7 are only in the staging table and need to be moved.

    We can see the data here:

    2017-07-07 17_24_11-SQLQuery6.sql - (local)_SQL2014.Sandbox (PLATO_Steve (62))_ - Microsoft SQL Serv

    To move this data, let’s start with the merge header

    MERGE dbo.Event ev
  USING dbo.EventStaging es
  ON ev.EventID = es.EventID

    This opening is looking to merge data into the Event table using the EventStaging table as a data source. The join is included in the ON statement and follows the rules like any other join clause.

    The next part of the statement is similar to a CASE statement, with a series of WHEN MATCHED or WHEN NOT MATCHED statements with THEN clauses that determine what happened.

    MERGE dbo.Event ev
  USING dbo.EventStaging es
  ON ev.EventID = es.EventID
  WHEN MATCHED 
   THEN UPDATE 
    SET ev.EventName = es.EventName
      , ev.City = es.City
      , ev.EventDate = es.EventDate
  WHEN NOT MATCHED
   THEN INSERT (EventID, EventName, City, EventDate)
         VALUES (es.EventID, es.EventName, es.City, es.EventDate);

    The two statements I have listed handle the update and insert. The first says that when we match a row, meaning there is a row in EventStaging that matches Event on the EventID, we will update the Event table (that’s the MERGE target). In this case, the rows 5, 6, 7 will fall into this case.

    The WHEN NOT MATCHED is when there is a row in EventStaging that isn’t in Event, we insert the data. Note again, we don’t need to specify the table name in the INSERT.

    When we run this command, the four rows in EventStaging are processed, with 3 inserts and 1 update. After running this, we can see the results here:

    2017-07-07 17_24_40-SQLQuery6.sql - (local)_SQL2014.Sandbox (PLATO_Steve (62))_ - Microsoft SQL Serv

    The name of the Orlando second event (#4) has changed, as has the date.

    This is a quick look at Merge, and a handy command that you should consider using with smaller sets of data.

  • Accelerating Your Career

    This post was originally published on Apr 23, 2013. It is being re-run as Steve is on vacation.

    I ran across an article on ways to accelerate your career and for the most part I think these are good ideas and suggestions. Networking, finding a mentor, and more will help you no matter what business or field you are working. Some of the advice is geared towards those people looking to climb the corporate ladder and move into management, which is not necessarily what many of us want. If that’s the case, ignore those items. There was one item, however, that I thought was particularly interesting for data professionals.

    The seventh item on the list notes that you should spend 10-15% of your time working on a project that’s outside of the scope of your job or team. That might sound crazy, and even dangerous, but it’s a good idea if your boss isn’t opposed to it. Having knowledge about the way your business works, the way they use data, or solve a problem, could be valuable in your existing job. You might notice a pattern or way in which you could improve either your job or someone else’s. Over time, helping in a variety of departments builds friendships, increases your networking, and might show your boss you deserve a raise.

    Many of us end up working with data as a widget. The job of a developer or DBA is writing code or managing data, and sometimes don’t often think much about the actual industry in which we are working. Gaining deeper knowledge of the way your particular business works means you can better understand why you are asked to solve a particular problem. That knowledge can lead to a better solution. It might also make your job just a little more interesting.

    For the typical US worker, that’s 4-6 hours a week. For the IT person, that might be a little more time, but I think it’s worth the investment. I wouldn’t be overly driven in this area, and if I were working on extra projects at work, I’d eliminate (or cut down) on the time I was spending learning new technologies. Life requires balance, and if you tackle something new in one area, make sure you know what you can give up.