Posted in tSQL Tuesday

The Death March

I’m, um, writing this for a friend. Yes, that’s it.
A friend. Or maybe a friend of a friend. We all know this friend, though. I’m sure you have one.  Anyway, Jeff’s (b|t) topic reminded me of a story my friend was just telling me, and it seemed like the perfect time to pass it on.

So, my friend was working on a project for a nursery who supplied landscaping companies. Another flavor of the software her company supplied was for orchards. As such, there wasn’t much place for plant information, just tree information. There was an EAV table though that allowed for many custom attributes to be added. So the customer had set up attributes for the plants in an order in the EAV table.

The customer then purchased the nursery supply sofware, and my friend got tapped to help the customer … smooth the way from how the data was imported into the old version and tie into the newer version.  There were two additional tables with the nursery supply version of the software, so my friend thought she could write a trigger to push values that came in from the import through that EAV table to rows in the correct new tables. (Even though triggers make her shudder.)

This sounded pretty easy until she discovered a problem: all the reports were tied to the EAV table, and the customer expected any changes in the new tables to push back to the EAV table so the reports would still work. Yipes!  And of course, the EAV table was one of the busiest in the database.  Additionally, this forms a horrible circle of trigger looping hell.

My friend was very clear to both her project manager and the customer in question that she felt that this was a poor idea, and that this could have a significant negative impact on the database. She agreed to develop it though and put it into a UA environment for the customer to test thoroughly.  The final solution ended up being fiiiive new triggers, 4 sanity brakes, three staging tables, two tables from the new version of the software, and a job calling a stored procedure. Oh, and the original EAV table. (Phew.)

Each set of triggers that populated any of the staging tables checked backwards to see if the record came from the previous staging table(s). If it did, it ignored it and didn’t continue to push the record forward. The job was for the final push from the EAV staging table into the EAV table, to avoid many incremental inserts/updates and instead do those in a batch.

Although the final verdict of this whole mess hasn’t played out at this time, both my friend and their customer weren’t very confident that this was going to be a success. There were additional issues such as data not being able to be definitively mapped, questions about how the data was imported into the EAV table, etc. I’m pretty sure I know what the endgame for this project is.  Well, there are a few different varieties but all of them resemble a train wreck.

For all of you out there currently working on one of these, my friend and I feel your pain. Best of luck!

Posted in Horses, tSQL Tuesday

Passion

Thanks to Aaron Bertrand (b|t) for a great topic to encourage me to make my first foray into T-SQL Tuesday, which I should have done a long time ago.  And thanks, Gene (b|t) for the inspiration as well!

So.. passion. That’s what makes us so special as a community. There are so many people that have true passion – for knowledge, for sharing that knowledge, and for building and supporting our community. And I, for one, was surprised to find that I had a passion for data. It was a pleasant surprise to find out I loved what I was doing and that I could feel so strongly about .. well, you know, boring data!

But… being a DBA and performance tuner isn’t what defines me. There is something else that is my sanity, my self-confidence, and my passion. And, lucky for me, that community is as awesome in it’s own way as the SQL Server community is. I am… a three-day eventer.

Huh? Well, to break it down to it’s simplest form, I ride horses. Or, at least right now, horse. And yes, although most of you who have talked to me for any length of time know this, you’ll also notice that I don’t talk about this much. Yeah, I still get the high-school-like jokes about glue and dog food when I talk about this with non-horsey friends. It’s kind of like talking performance tuning with non-SQL people. It’s better to just not do it.

But, why is riding horses so great? Well, because it’s all about building a partnership, and hard work, and focus. If you want to be good (and most of us keep working at getting better all the time), you need to put the effort into it. This isn’t much different than other things, but there’s two of you – two of you to work on things, two of you to keep in shape, and two of you to keep healthy and happy. But, then you always have a partner – you build trust and respect, and if you’re lucky, love. And an escape from the rest of your life, because sometimes, we all need to do that. At the barn, I can step away from everything that may be going wrong elsewhere.

OLYMPUS DIGITAL CAMERA

There are three parts to a horse trials; the first is dressage, which is the equivalent of a dance routine on horseback.

The second part is cross-country, a course of solid natural obstacles ridden at speed. The third part is stadium jumping – a course of rail fences that require agility and scope. Being good at one of these things, let alone all three, is a challenge. Add to this the fact that your partner is a 1200-pound animal who doesn’t always agree with you, and it can be… interesting. Humbling, certainly. And, absolutely incredible!

I had my first horse for 20 years. He was my best friend and my strength through cancer, two marriages, building a career, and everything in between. We never rocked the competition world, but I learned so much from him. He had a rough early life, and had a difficult time trusting people. Although it took time, I earned his trust and his love. As the years went by, we became a stronger and stronger team, and we did amazing things. Unfortunately, his legs weren’t as strong as both of our personalities. I laid him to rest a little less than two years ago, and I will never stop missing him.

I have a redheaded youngster now, who is wonderful in his own way. We both still have a lot of rough edges together as partners, though. Hopefully in a couple of years, we’ll manage to rub those off and be able to trust each other totally. Meanwhile, we’ll keep working on little things like convincing Ben to let go through his back, and me remembering to keep my heels down, both seatbones in the saddle, and my shoulders back. Always with the shoulders back 🙂

Thanks for listening!

 

Posted in PASS

The T-SQL Tuesday Post I Should Have Written

The November 2017 topic for T-SQL Tuesday was “Folks Who Have Made A Difference”.  And, although I don’t really have a good excuse to not have gotten this written on time, I still want to thank those who have made a difference for me regardless.

The first thank you goes to Steve Ingalls (known to most of my friends as Channel 0 Steve). He engaged me in my first professional database project – redesigning the chemical inventory database for the research branch of the Cleveland VA Hospital. It was a summer project between college years, and it was in (I know, I know *wince*) Access, but the guidance he gave me stood me in good stead then and ever since, as does his continued wisdom and humor.

The next shout goes out to Cheryl Johnson, who was my boss for many years while I worked for The Ohio State University (well, a small section of a segment of.) She allowed me to make mistakes and encouraged me to learn from them. I learned from her to take time in my work, which resulted in pride in what I created or helped create. She also showed me that women have a very definite place in the technology world.

That brings us to the the people who showed me #sqlfamily, including the best hugs ever.  I had the honor to be in Allen White’s Powershell precon at my first PASS Summit. (@SQLRunr) We connected because I might have been the smartass in the front row who likes to talk to presenters when they ask for audience participation. Allen has been nagging supporting me to become more involved with PASS and to give back to the community.  His encouragement has really meant a lot as I try to find ways to become involved – I’m not comfortable with public speaking, but maybe I’ll get there…. someday!

And last but by no means least, thank you to Mindy Curnutt (@sqlgirl). She and Eric Blinn (@SQL2TheSequel)  built up the dream team in our DBA Services group – each one of us had good basic skills, and exceptional skills in certain areas. I learned for the first time what it’s like to really work in a team of great people, and that I was valued for my contributions. This is no small thing. She is involved in many different activities and is inspiring in her work.

Thank you, all of you – for your support, for sharing your knowledge, and for being my friends.

Posted in tSQL

Basic Use Cases of Lag and Lead

Lag and Lead are both window functions available in SQL Server 2012 and forward. Most of you have probably at least heard the term window functions, but what does that mean?  Well, first of all, it has nothing at all to do with the Windows operating system. For those of you that have done some studying up on performance tuning of queries, you’ll remember that the best way to make your query run faster is to limit the data set that you’re acting against as soon as possible in the query/procedure.

A window function will use just the dataset returned by the query, and act upon THAT while still within the query. It can do additional aggregations or perform other tasks on the initial select of the query, without requiring subqueries or other hits to the actual database table. Lag will find the value of n rows back, while lead can find you the value of n rows ahead.  Great, right?  But, what does that mean in practical terms?

Let’s set up some simple queries to illustrate.

/* please run the code setup from the attached document: setupCode.adhocsql
Then, on the same connection, run this code */

SELECT a.tripNumber
    , a.stopNumber
    , a.stopAction
    , a.actArriveDate
FROM #stops a 
WHERE a.tripNumber = 121279 
ORDER BY a.actArriveDate;

So, we can see in the results 4 stops on this leg. 

Maybe we need to find out what action is supposed to happen at the next stop. This is how we would have had to write this code in the past, which would have involved 5 table scans and 15 logical reads:

SELECT a. tripNumber
   , a.stopNumber
   , a.stopAction
   , a.actArriveDate 
   , (SELECT TOP 1 stopAction  
      FROM #stops b  
      WHERE a.tripNumber = b.tripNumber 
         AND b.actArriveDate > a.actArriveDate 
      ORDER BY b.actArriveDate) AS nextAction
FROM #stops a 
WHERE a.tripNumber = 121279 
ORDER BY a.actArriveDate;

Here are our results:

And, as we mentioned before, the statistics IO and time:

Now, though, we can use LEAD to find out what’s going on in the NEXT row.

SELECT a.tripNumber
    , a.stopNumber
    , a.stopAction
    , a.actArriveDate
    , LEAD(a.stopAction,1,'N/A') 
            OVER (PARTITION BY a.tripNumber 
                  ORDER BY a.actArriveDate) AS nextAction FROM #stops a 
WHERE a.tripNumber = 121279 
ORDER BY a.actArriveDate;

As we can see in the result set, the nextAction finds the stopAction from the next row, and we see our default N/A on the last row (because there’s no “next” row!)

Let’s break this down:

The red rectangle denotes the column that we’re choosing. The green rectangle is the offset – that is, how many rows ahead we’re looking for the value that we want. The blue rectangle denotes the default value if there is no “next” row.  The orange rectangle denotes a partition – that is, we’re going to start this over for every tripNumber. And, there must be an order by in the OVER clause for lag and lead because we need to know what will determine the “next” or “previous” row. This is denoted by the pink rectangle. Please note that the order by in the lag/lead does NOT have to be the same order by that the query uses.

This is our statistics IO and time for this query:

The Window Function version causes 1 scan of the table and 3 logical reads. Yay, less work!

LAG allows us to look back at previous rows. Say we want to see how much time elapses between stops….:

SELECT a.tripNumber
    , a.stopNumber
    , a.stopAction
    , a.actArriveDate
    , DATEDIFF(mi
         ,LAG(a.actArriveDate,1) 
                 OVER (PARTITION BY a.tripNumber 
                      ORDER BY a.actArriveDate)
         , a.actArriveDate) AS minElapsed
   , LEAD(a.stopAction,1,'N/A') 
                 OVER (PARTITION BY a.tripNumber 
                      ORDER BY a.actArriveDate) AS nextAction 
FROM #stops a 
WHERE a.tripNumber = 121279 
ORDER BY a.actArriveDate;

Here, we didn’t specify a default, and so we get NULL for the value of that field. And for this query, we also get 1 table scan and 3 logical reads!

What would this look like if we wanted to find data for a few different trip numbers?

SELECT a.tripNumber
 , a.stopNumber
 , a.stopAction
 , a.actArriveDate
 , DATEDIFF(mi
 ,LAG(a.actArriveDate,1) 
 OVER (PARTITION BY a.tripNumber 
 ORDER BY a.actArriveDate)
 , a.actArriveDate) AS minElapsed
   , LEAD(a.stopAction,1,'N/A') 
 OVER (PARTITION BY a.tripNumber 
 ORDER BY a.actArriveDate) AS nextAction 
FROM #stops a
WHERE a.tripNumber IN (121279,121230,121333) 
ORDER BY a.tripnumber, a.actArriveDate;

Our result set shows the lag/lead row calculations completed within each tripNumber:

Checking on the statistics IO, we still see 1 scan and 3 reads. Woo hoo!

These and other Window Functions can be used for some pretty advanced tSQL, but for now we’ll leave it with these simple usage cases.  Thanks for reading!

Posted in PASS

PASS Summit 2017 Networking Dinner

Every year I’ve attended the PASS Summit, I’ve always started off the week by attending the Networking Dinner organized by Steve Jones (@wayoutwest|voiceofthedba.com) and Andy Warren (@sqlandy|sqlandy.com). Each year I have made new connections and sometimes friends who I have spent the week with. When I read on Andy’s blog that he and Steve weren’t going to be able to organize this event this year, I felt like I was going to be really missing out. After tossing around the idea, I decided maybe I’d have a go at organizing it!

I hadn’t planned my plane flight around arriving that early on Monday, so the first dinner time will be 6p. Please RSVP via Eventbrite so I can help the restaurant get a more accurate head count and we can get seats!  We cannot float you dinner and drinks unfortunately, but we can help you meet great people.

RSVP: https://www.eventbrite.com/e/pass-summit-2017-networking-dinner-tickets-38990845669

When you get to the Yard House, please look for folks wearing their PASS badges. We and the wait staff will help you find the group.

I hope you join us, I’m most certainly looking forward to meeting all of you!