Learn from… me!

This image has an empty alt attribute; its file name is T-SQL-Tuesday-Logo-e1518373137845.jpg

Kerry Tyler (b|t) asked us in this month’s t-SQL Tuesday topic to talk about a lesson we’ve learned, in order to help others learn FROM our mistakes without also making them! I’ve wanted to talk about this for a while, as I’ve now seen this behavior from both sides and I know how detrimental it can be to everyone involved.

What is this I speak of? Our fraught relationship with third-party software vendors. I know, I know, we all love to hate them. (Even those of us that ARE them – we also have third-party software, we completely get your pain.) In a couple former jobs, it was pretty clear: “Hey, Sales just sold software so we need the software to do this in a time frame that’s way too short, and just asking for pain all the way around.” Developers grumble, QA grumbles, shortcuts get taken, functionality and performance are very far from meeting expectations, and everyone – customers, developers, sales, PMs, account reps…. EVERYONE is pointing fingers at each other.

It’s at this point that everyone loses sight that we ALL have the same goal. It’s easy to do – each party feels like they’ve been strung along (which is probably true). No one is happy with the end result. Flashback to when I was on the phone with a customer, who had rolled out this wonderful new functionality and performance was so bad that literally no one could use one of the screens in the software. This was my first interaction with these customers, as my immediate supervisor had moved on to greener pastures a week earlier. It was Friday night (of course), and I had plans with my family and was a bit frustrated.

I was looking at the SQL (which, btw, was generated by the application and not in a stored procedure.) First of all, any SQL changes would require an application patch. Whoops one. Second of all, wow, was this SQL bad. Cartesian join of large tables, so many returned columns that indexing was really out of the question, and a poor plan. As I was looking at the code, I remember saying, “I don’t think I can fix this in an hour. I don’t think I can fix this in SIX hours.”

So, at this point I learned that teleportation is possible, because there is NO other explanation for how the account manager got to the spot standing next to my desk from his office upstairs in the timeframe of 1 second. He frantically motioned me to hit the mute button (which I did), and then followed with, “We can’t say that to the customer!”

My response: “I’m not going to lie, that’s going to be really obvious really fast that this isn’t anything we can fix RIGHT NOW.”

What we didn’t hear: the customer hit THEIR mute button, turned around and said, “Finally, someone is going to tell us the truth!”

That SQL didn’t get resolved that night. What ended up happening is that enough people on the customer’s end went home, so the remaining people could actually run the query. The next week the SQL was pulled out of the application and I wrote a performance-tuned stored procedure. However, something much more magical happened – we started trusting each other.

My new boss (since the previous one had left the week before) was also new to the company. He stepped in to work with this customer (who had very legitimate reasons to be frustrated.) A few other people who had been involved in these interactions were encouraged to step out and let us handle the problems. I started working very closely with the customer, and my boss started being very transparent with them. They were incredible testers; we started working together as partners when we had a release that we felt was ready, and would get excellent feedback from them. With the significant increase in communication, we could really focus on what was most important for them and they would help us get it right, before they rolled it out to production.

What did I learn from this? Shut up and start listening. Try to understand where folks’ frustration is coming from. BE HONEST. Remind everyone that you are all sharing the same goal: the customer being successful. Really focus on what you can do on your end to reach that goal. Encourage people around you to focus on what they can do to help reach that goal. No matter what your scenario, don’t think of people as customers – think of them as PARTNERS. Treat them as partners. Encourage them to BE partners.

I still have a relationship with a couple people from that customer – they have become friends. Real friends. I know I can reach out to them if I need help – hardware questions, home lab setup questions (one of them is a hardware guru). I am pretty sure they would reach out to me if they had a tSQL question as well. Some of them were going to join me at PASS Summit this year (partially because we know this awesome restaurant in Houston, but also because .. we’re friends.) They have brought a lot of value to my life 🙂 So… please take what you can from my learning, I hope it brings you the good things it’s brought me!

Life Hacks to Save Our Sanity

This image has an empty alt attribute; its file name is T-SQL-Tuesday-Logo-e1518373137845.jpg

Jess Pomfret (b|t) asked us in this month’s t-SQL Tuesday topic to talk about a life hack we’ve done that’s made things easier for us. This is perfect timing for me; I wanted to share a couple of stored procedures I wrote for the book (Refactoring Legacy T-SQL For Improved Performance). The CodeCascade procs can be found on a GitHub Repo called CodeCascade.

Actually, the idea for this code started back when I listened to Andy Yun (b|t) give a talk about nested views. I was dealing with a 2,000 line stored procedure that would be interesting to rewrite, but it really shouldn’t take me that long, right? I mean, 2,000 lines. Except that it called a function that was also 2,000 lines, which called a few procs, not to mention a few other things the original proc called, and then … well, yeah, you get the point. 20,000 lines of code later, I was like, WHAT did I get myself into?

So, since we all know how excellent we are *cough* at estimating time to perform tasks, I figured it would have been a good thing to have known about this cascading miasma of code before I dove in head-first. Andy’s presentation had code to show the cascade of a view – all of the views it called, and views they might call. I wanted to adapt this to work for stored procedures and functions as well. (Forewarned is forearmed, after all.)

So, I blithely knocked together some code to tell me exactly what objects (procs, views and functions) that my code called, using SQL Server’s dependency information. I went to run it on that exceptionally offensive proc. And guess what? It blew out after 200 recursions. So, I set the limit to 400 recursions, and… it blew that out as well. OH NOES! Well, I had also set a kind of breadcrumb trail field in this because I was particular about the sorting (I wanted each cascaded item to be under the parent that was calling it.) So, I had each object line check to see if that ID was already in the breadcrumb field, and if it was, add a message that it was part of a loop, and stop trying to recurse through that particular line.

Once that was polished up, I also did the “reverse” – what code CALLS my code, and what code calls THAT code? This is again based on dependencies, and checks for that eternal loop.

These procs have come in handy many times. I encourage you to use them and modify them if you’d like, but it’d be great if you left the intro flowerbox on that attributes the original code to me. If you do make changes that you think others would find helpful, please feel free to let me know and I’d be happy to add your changes and attribute them to you as well!

I hope these can help you as well 🙂

Dealing with Impostor Syndrome

Jon Shaulis (b|t) asked us in this month’s t-SQL Tuesday topic about impostor syndrome. Do we have it? How have we worked past (or around) it? The first question is easy – I don’t know anyone who hasn’t dealt with impostor syndrome, unless that someone is new enough to whatever job or task they’re doing that they don’t have enough experience to know there’s no way you can ever know everything.

The second question can be more difficult. Also, if you lose self-confidence in one area of your life, it can destroy your self-confidence in all areas of your life without you even realizing it. Being self-aware, which is something we should all strive for, unfortunately makes you more prone to this in my opinion.

So, we all think of ourselves as “normal” because we grew up with us, and we know how we feel, and how our family or peers were or are, and what we look like in the mirror, and how much we curse when we accidentally jam our foot into the doorframe – all of those things are familiar and “normal”. We know what we do know and all of those things that we don’t know. When we see someone who knows more or is better at something than we are, it’s very easy to fall into the trap of thinking that person knows so much more “stuff” than we do.

But there’s something that it’s easy to forget. You know something that no one else in any given room does. You are better at something than each and every person. And the opposite is also true: each and every person in the room can do something better than you, or knows something you do not. Everyone has unique experiences and our brains all do “thought algebra” differently. Wait, whut?

So I can look at a and b, and to me, a + b obviously equals c. To someone else, a + b = m. Neither of us are necessarily wrong, everyone just collates information differently, and not everyone solves things in the same way. Nothing has more than just a single solution (well, except the meaning of life, but since no one has figured that out yet…)

So what does this have to do with impostor syndrome? Everything, actually. It’s very hard to do, and I am just as bad as everyone else with this, but we all need to realize how special and amazing each and every one of us are. (It’s still hard to not say “each and every one of you”, and to include me in the amazing people. See?) Maybe there are skills we still need to learn, and we probably will learn those skills. But as people, we are all unique individuals with the capacity to do amazing things. We also need to surround ourselves with friends who remind of this regularly. Discussing this with people we trust, who we know care about us and not about what they can get from us, will go a very long way to help us stay in the right frame of mind when dealing with impostor syndrome!

Thanks for listening 🙂

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!