What exactly does this mean?  We’ve heard of “code patterns” in other programming languages, but what does it mean to use T-SQL code patterns to develop high performance T-SQL?  Having now been called out by a couple of folks in the SQLverse that I respect, most recently the editor of the SQL Server Central (SSC) web site SQL MVP Steve Jones, I believe it is time to “come out” so to speak and come clean on what I mean by this and how to achieve it.

In order to do this, I think it is necessary to expand a little on the rather enigmatic profiles you may have seen of me.  The reason for this is simple, and I’ll start with a little bit of advice: “Don’t develop your applications based on the advice of just any yahoo that finds a way to post an internet page.”  I’ll return more to this in a while, but for now you need to ask yourself: “Should I take the advice of DwainCSQL?”  Read my extended bio below and answer that question for yourself.

My Extended Bio

I started coding T-SQL around the middle of 2011, and SSC published my first article on T-SQL in early 2012.  Prior to my first experience really coding SQL, I had limited experience with ad-hoc SELECT statements only (and obviously FROM and WHERE clauses).  At the time, the simplest of JOINs required me to use Google to recall the syntax.  I was project manager for a project that required a rather complex stored procedure to be built.  I became exasperated with the development team, because after three weeks of testing it, they simply could not get it right.  So I threw my hands in the air (after beating my head against my desk many times) and said “let’s see if I can write this thing myself.”

After five days of Googling and coding, one tiny excruciating bit at a time, I came up with a stored procedure (SP) that I thought would do the job.  One issue was found by our testing team during testing, none were found in User Acceptance Testing (UAT) and after it was moved into production no error was ever recorded for that SP despite the fact that it runs every two minutes (24×7) and has been doing so for more than four years now.  The Googling was to teach myself the T-SQL code constructs that were needed (and I’m talking real simple things like UPDATEs, INSERTs and JOINs) to make my SP work.

A little later on in the same project I was faced with a second challenge.  The development team had created another SP to handle a different scheduled task, one which was also required to run every two minutes.  They came up with something that processed 1,000 rows in about twelve minutes.  This was obviously unacceptable, because it needed to process those 1,000 rows within the interval between two scheduled runs.  Off I went to Google to teach myself about how to write high performance T-SQL.  It was at that time that I ran across the term RBAR, which stands for “row-by-agonizing-row,” and I immediately recognized the wisdom of the term and wrote “no-RBAR” on the whiteboard behind me.  This of course got me a lot of questions (like what does it mean?).

I set about to rewrite that SP, and through some relatively simple techniques I managed to get the processing time for the 1,000 rows down to less than two seconds.

These two challenges in T-SQL taught me two things: 1) anyone can learn the language (even a non-technical project manager) and 2) performance counts.  I later came to realize that performance always counts, even if it is not explicitly stated as a non-functional, system requirement.

I also learned one other thing from this experience.  I really liked T-SQL!  And that of course got me to reading about it (and later writing about it).  One of the first things I did, probably in late 2011 or so, was to get a login account on the SSC web site, because I realized that a lot of the good information I’d gotten, to get me through those coding challenges above, I got from there.  That’s also around the time that two things happened: 1) I started fielding questions on the SSC forums (mostly for fun and) to improve my skill level and 2) I learned from whom the term RBAR originated, namely SQL MVP Jeff Moden.

Being of the brazen sort, sometime after my first article was published I believe, I reached out to Jeff and told him part of the above story and how his RBAR term really stuck with me and how I’d embraced it.  It was really the combination of the two coding challenges that hit me at nearly the same time that got me interested in high performance T-SQL and how it can be achieved.  Somewhere, shortly after that first contact with Jeff (and I owe him the utmost gratitude for encouraging me during my formative years), he gave me a piece of advice that has influenced nearly all of my writings, ramblings and ruminations since then.  It was simple and to the point, like most of the advice that he dispenses: “never make performance claims without proving it in code.”

Since that first article I’ve had a total of 23 articles published on SSC (some have received more than 30,000 views), 21 articles published on Simple Talk and another 19 or 20 blogs.  You’ll see many if not most of those articles embrace Jeff’s advice, about proving the performance with code and statistics.

So now the question is, how did I get from T-SQL newbie four years ago, to where I am now (wherever that is)?  And more importantly, have I come far enough to be someone who’s advice you should listen to?

I can’t answer the latter for you, but this blog is all about the former.

Enter T-SQL Code Patterns

I honestly don’t have much experience with code patterns in other programming languages.  I get the feeling that they’re somewhat akin to something we used to do, back in the days when I was all about technical and coding, which we called code reuse.  And I did it all the time.

In T-SQL I think of code patterns a little bit differently.  Let’s first talk a little bit about algorithms.  There are at least two types: procedural and set-based.  Procedural algorithms can be written in T-SQL using SPs.  Usually they suck.  Set-based algorithms on the other hand, usually perform much better and they are the code patterns you need to embrace when it comes to T-SQL. Another piece of advice that Jeff likes to dispense is “it depends” (also short and to the point), and I’d agree with that here.  There are exceptions to this statement (“they suck”) but I’d suggest they are rare.

T-SQL code patterns are different in the sense that little code reuse is usually involved.  A code pattern in T-SQL is more like a concept that can be applied to a specific query circumstance.  Let’s look first at a really simple concept that represents a code pattern.

When doing a JOIN, it is best to try to JOIN a foreign key in the right table against its referenced columns in the left table.

This is a code pattern that is so simple in fact, many people may not recognize it for what it is.  It is a high-performance code pattern for the basic reason that the SQL optimizer is going to use the available indexing (usually the PRIMARY KEY’s clustered index in the left table) to come up with the lowest cost query plan.  It is hardly something you’ll likely be copying from one query to another, so it is not “reusable” in that sense at all.  It does have wide applicability that extends to nearly every query you’ll ever write (assuming you’re not like me in my beginnings when I didn’t even know the proper syntax for JOINs).

What about more complex code patterns?  There are many, but they are all IMHO concepts.  How did I learn about them?  Well, that would come from extensive reading.  Once I got interested in T-SQL and performance, I dived into reading as much material as I could about specific queries and their applicability to specific problems.  It didn’t take me very long to realize that there are about as many T-SQL solutions to a specific query problem as there are stars in the sky.  In fact, to solve a specific problem it is unlikely that any two intrepid T-SQL developers would do it the same way.  The literature is swamped by different people’s takes on different ways to solve a particular problem using a T-SQL query.

That’s where Jeff’s advice about always proving performance claims in code came in.  If I read about a particular solution technique and the performance was not proven in code, I would consider it basically un-tested until I found somewhere that it was.  Or otherwise, I’d need to test it myself to prove its efficacy.  In the end, the plethora of potential solutions would get winnowed down to just one (or perhaps a handful) that demonstrated the performance characteristic that would make them viable as a T-SQL code pattern.

Remember what I said above: “performance always counts.”  That’s how you know you’ve stumbled onto a code pattern that’s worth remembering.

So now you’re probably asking yourself two things: 1) how can I possibly remember the right code patterns to solve every querying situation I might encounter and 2) how can I sort through this huge amalgam of potential solutions to find the one that performs the best, and do that in the finite amount of time I have available to me to write my special query.

My answers in their simplest form are: 1) you don’t and 2) rely on source legitimacy.  Both of those require a bit of explaining so I will.

It is not necessary to remember the specifics of every elegant and performance efficient code pattern you find.  All you need to remember is where the pattern applies and who wrote about it.  Let’s face it, most of the really tricky code patterns have been tackled by experts.  And these guys, who are no mere mortal men mind you, have developed best-of-breed solutions.  Thanks to the Internet and Google, if you’ve found the code pattern once, it is quite simple to find it again when you need to, simply by remembering who wrote about it.  Google is your friend – embrace it.

I personally do not memorize the specifics of every code pattern I’ve ever encountered that I liked because it was of proven performance characteristic.  I am more than happy to look them up when I need them.  Yes I do remember a few, but that’s usually because I’ve had to use them extensively or I’ve had the opportunity to write about them.  For example, two that I do remember without look up are the Quirky Update and Gaps and Islands.  We’ll talk more about Gaps and Islands in a minute, but both of these are retained in my long term memory as more than a concept, rather as a technique for specifically the reasons I mentioned.

Lest we not forget my second answer “rely on source legitimacy,” the explanation of that is also pretty simple.  Rely on proven sources for your information.  These are authors in the SQLverse that demonstrate time and time again the code patterns that perform the best for specific query cases.  A short list of my favorite authors of code patterns (even though they may not themselves call them that) are listed on the Favorites page right here in my blog.  I will apologize to those other sources that I may have left out (especially if I’ve quoted or referenced their work in past articles) or because I simply haven’t identified them yet.  Rest assured I will maintain that Favorites page as new authors come to my attention.

There are many-score authors on T-SQL out there that never demonstrate performance.  My attitude is generally to avoid them.  Or at least to take their ideas with a grain of salt, and test the heck out of them.

A Little more Depth on one of my Favorite T-SQL Code Patterns

Early on in my T-SQL readings and mass accumulation of information that I hoped would lead me to be better at writing T-SQL I stumbled across a problem that for some reason piqued my interest, probably because of its complexity.  Once I learned of it, and quite honestly I had no real use for it other than to satisfy my curiosity, I couldn’t read enough about it.  What really ticked me off the most I suppose, and which led me further down my research path, was that I didn’t really understand how the solutions I was seeing were working, but that’s beside the point.

That problem is known as Gaps and Islands, which I said above I’d discuss in more detail.  I have linked in there probably the first article that I found with what I’ve called “un-tested code patterns.”  I should point out also, that this is a code pattern that you probably won’t need to use too often.  Still, as it is one of my favorites, I feel I should expound upon it just a bit to explain how I distilled down the solutions I found to the one code pattern that is easily remembered and worthwhile to do so.

As I continued my reading I found some good articles about Gaps and Islands, while at the same time flailing around rather blindly and failing miserably in my understanding of the solutions.

• Chapter 5 of SQL Server MVP Deep Dives written by SQL MVP Itzik Ben-Gan
• Group Islands of Contiguous Dates written by Jeff Moden
• And later, SQL 2012 Performance Test: Gap Detection by Microsoft Certified Master (in SQL Server) Wayne Sheffield

If you’ve looked at the Favorites page of my blog that I linked in earlier, it is no coincidence that you’ll find all three of these authors listed there.  All of them are highly representative of what I call source legitimacy, because they back up their performance claims with code and statistics, but perhaps more importantly they are lucid writers.  Their explanations are easy to follow and so are their examples.  In fact, it was only after reading these sources did the dawn of understanding arise for me with respect to the Gaps and Islands problems, and how they are solved.

If we take a look at the Deep Dives book, Itzik Ben-Gan’s chapter demonstrates and explains four solutions to islands and four additional solutions to the separate problem of gaps.  Jeff’s article on the other hand focuses on only one of the islands solutions (and that one is also in the Deep Dives chapter).

Wayne Sheffield’s blog, which I read later showed one of the new solutions for gaps that came about because of a new feature in SQL 2012 (the LAG analytic function).  His article was quite interesting in that it eliminated that code pattern on the basis of its performance (later I’ll call this an “anti-pattern”).

Finally my curiosity was sated!  I now understood the code patterns and had reliable benchmarks I could use to base my use case decisions on.  I had embedded the authors in my long term memory, so when the need arose I could easily re-locate those references and pick the applicable code pattern.

Nonetheless, I had the nagging desire to want to contribute something to this space.  I came to the conclusion that it seemed to be the playground of mostly Microsoft SQL Server MVPs, but being of the brazen sort as I mentioned before, that deterred my desire not in the least.

Then a few months later it hit me.  My Aha! moment.  That moment when I came to understand the relationship that exists between Gaps and Islands.  To put it as simply as possible, they are not essentially different as islands can be converted to gaps (my first realization), and that gaps can be converted to islands (although not quite as easily).  After some quick code-proofing (always a first step in all of my writing), my muse kicked in and I furiously composed The SQL of Gaps and Islands in Sequences, which the editor at Simple Talk kindly published for me.

Remaining true to my desire to back up my performance assertions with code and statistics, again that great advice given to me long before by my T-SQL mentor Jeff, I tested my new contribution to the Gaps and Islands solutions space.  While in neither case did my solution come out the overall winner, in both cases my solutions were competitive.  And that was a place I was happy to live in.

Ultimately this led me to the code patterns I now apply to either Gaps or Islands:

• Use the code pattern shown by Jeff in his linked article on grouping islands of contiguous dates for the islands problem.
• Use my solution on top of that one for the gaps problem. I called that solution Cross Apply Values – Islands to Gaps.

Because I had studied it so hard I can now easily remember Jeff’s islands solution.  Because of my aha moment, I can easily remember my little add on to take me from islands to gaps.  The code pattern had solidified and I could now apply solutions that performed admirably in these scenarios.  If for some reason, for example due to data irregularities or what-not, neither of these solutions meets the end game of the best performing solutions, I know where to go to look into alternatives.

Interestingly, the code pattern for islands (which I call the staggered rows approach) actually has applicability to a wider variety of problems, making it an extremely versatile code pattern that perhaps I’ll dive into in a future article.

A Couple More High Performance Code Patterns that I’ve had the Privilege to Author

There are way too many high-performance T-SQL code patterns out there than can be enumerated in a single blog.  Most of these have been authored or demonstrated by individuals that are highly skilled in T-SQL, much more so than you or me.  Look to the favorites that I mentioned and you’ll easily find many of them.

It has been my privilege on occasion to stumble upon a code pattern of my own making that seems to be the most desirable one to use based on performance.  These are so few as to be able to mention them all in this short blog, so I will do so and humbly ask your forgiveness if you feel I’m tooting my own horn as that is not my intent.

• My solution to a challenge posed by Itzik Ben-Gan to Identify a Subsequence within a Sequence. In that article incidentally is an alternate, high-performance approach to the problem by SQL MVP Peter Larsson, which is based on the staggered rows code pattern I mentioned earlier.
• I generalized that subsequences solution to what I call the Row Reduction approach to Relational Division, which I wrote about in High Performance Relational Division in SQL Server. Peter Larsson’s work is referenced quite frequently therein, because he’s really the man when it comes to relational division.
• Then there is calculating the statistical median in T-SQL, which has been written about by me (Calculating the Median Value within a Partitioned Set Using T-SQL), by SQL MVP Aaron Bertrand (Best Approaches for a Grouped Median) and again by me in another blog (An Even Faster Method of Calculating the Median on a Partitioned Heap). In that later blog, is yet another code pattern for median that remains in the un-tested stage because it hasn’t been run through all scenarios and against all contenders, but one day I’ll get around to it because it looks promising. Note that in the case of median, my solution was not the front-runner in all scenarios, but as usual I’m willing to settle for just being in the race.

Again, I want to emphasize that this blog is most emphatically not about me, it is about T-SQL code patterns.  If my examples that involve me haven’t yet convinced you, then read on for mention of just a few more.  Many of these are even more important and generally more versatile than the few examples I’ve bored you with so far.

Other High-Performance T-SQL Code Patterns that you can Explore

In no particular order, here are some additional code patterns you should try to familiarize yourself with time permitting, should you in fact have bought into this concept that I am espousing.  Some of the links are to my writings, while others are to mostly authors on my favorites list.  Once again, I’m sure there are others worthy of mention that I apologize for omitting.

• The Numbers or Tally table
• Calendar Tables (also here, here and here, which is where I first learned of them)
• Using dynamic SQL in search procedures (here, here and here)
• Splitting a delimited character string (and the massive discussion thread that embellishes on this code pattern)
• The CROSS APPLY VALUES approach to Un-pivot
• The T-SQL Window Functions (here and here)
• Cross tab queries (here and here)
• Hierarchies in T-SQL (here, here and here)
• In-line Table Valued Functions instead of scalar-valued, user-defined functions (sorry no link here, so I’ll leave it as a challenge to you to find a reputable source)

There is overlap in some of those links, but that is only because some of those articles cover multiple code patterns well.

And then of course, there are the anti-patterns (where better code patterns exist):

• Counting with Recursive CTEs
• Triangular Joins
• CURSORs in T-SQL (not really a code pattern per se, but certainly an anti-pattern that can be applied in many querying scenarios that should be scrupulously avoided)

That should boost your reading list a bit!  And I’ve barely scratched the surface.

When there is no Code Pattern to Fit

We all like to think that our T-SQL problems are special, that they are unique; and that in order to solve them we should write our own approaches to solving them, otherwise where’s the challenge?

That is utter nonsense.  The challenge is to quickly deliver high-performance solutions, which accurately solve business problems.  Solutions that stand the test of time by being bug free, regardless of what changing, underlying data patterns impact them.  Solutions that scale upwards as your applications become successful beyond your wildest imaginings.

I’ve tried to show you that learning high-performance code patterns is easy.  Read a lot, remember a little.  Utilize your memory bandwidth to its utmost by simply remembering the name of the code pattern and the name of the author that provided the best proof of performance, so later you can look it up when you need to.  You don’t need to learn them all at first to get started with this approach.  Start by learning a few, and then expand on that knowledge when the need arises.

Perhaps the biggest challenge when encountering a new T-SQL querying problem is to figure out what your predecessors called it.  Gaps and islands for instance, may not be intuitively obvious, but once you remember it and can identify the use cases, looking up the code patterns becomes relatively simple.  And when I talk about your predecessors, you can rest assured that for nearly every complex querying situation that you’ll encounter in your career, someone has explored it before you.  Sorry to say this, but some of those people (yours truly excepted) are probably a lot more talented in T-SQL than you, especially if you’re just a beginner.  Why not try to draw upon what they have to offer?

And on those rare occasions where you truly have a new and complex query to write, keep in mind these immortal words of SQL MVP Gail Shaw:

“Write the query the simplest way.  If through testing it becomes clear that the performance is inadequate, consider alternative query forms.”

Afterword

Learning some of the code patterns noted in this article will almost certainly help you to write high-performance T-SQL queries.  Learning though, is a never-ending cycle.  You should let it lead you to new frontiers throughout your career.  Learn about T-SQL query plans (SQL MVP Paul White’s and SQL MVP Grant Fritchey’s articles on my Favorites page should help you a lot with that) and learn the anti-patterns too, so you know what to avoid.

Learn who to trust, and learn when perhaps your trust should be placed in other sources.

And if you should perchance to fancy writing about T-SQL, be sure to give credit where credit is due.  Building on the work of others is perfectly acceptable (I do it all the time), as long as you credit the source and the person.  Try to add value and not just rehash what you have seen others do.  This expands the knowledge space for everyone.

Notice how I always hyperlink SQL MVPs to their Microsoft MVP page?  That’s because I figure these guys (and ladies) have earned the recognition and deserve to be honored for it.

To be honest, I’m not yet sure if I’ve convinced you to trust my words or not.  After all, I am just another yahoo that’s figured out how to post something to the Internet.  You’ll need to be the judge of that.

If you do trust me, I think that’s just peachy!  Perhaps you’ll then want to follow me on Twitter (@DwainCSQL), because there I can promise you nearly all-SQL tweets!

I hope to hear your comments on these ideas in the comments section below.  Until next time, happy and productive querying!

© Copyright Dwain Camps 27 May 2015.  All rights reserved.