Doug Lane

SQL Server Entertainer

T-SQL Tuesday #41: My Love of Presenting is Nothing New

by

TSQL2sDay150x150Bob Pusateri is hosting this month’s T-SQL Tuesday, and he’s decreed the topic be how we came to love presenting. I say presenting is really the same thing as performing, and my love of performing stretches back to my elementary school days.

I did a lot of performances when I was young. Nothing commercial, mind you. Skits at the Cub Scout Pack meeting (Den 4!). Chester the cat in a play-reading of Bunnicula. Playing alto sax in band. In junior high (it’s called middle school now, I guess), our concert band went on tour through elementary schools in Anchorage. In high school, our 90-piece symphonic and stage (jazz) bands toured small towns in Alaska and Canada. I got to mangle perfectly good music do improv solos and I loved every second of it. That’s the closest parallel I have to the feeling I get when I present. Which makes sense when we consider what the two have in common.

Like presenting, improv relies on a framework (key, tempo, measures) and the rest is filled in on the fly. The more you practice, the better you can express your ideas. Your comfort level with the fundamentals (scales, whole tones) allows you to be more free-flowing with your ideas while sounding like you’re playing coherent, complete thoughts. You learn tricks that you can re-use in other solos – my favorite was C-E♭-C-E-C-F-C-F#-C-G, surprisingly easy to play fast on a sax – in the same way you can re-use information, techniques, or jokes across different presentations.

With all this in mind, it’s no surprise I enjoy presenting, as it picks up where I left off nearly twenty years ago when I last played in a band. What got me started presenting was the realization that I had learned enough to stand in front of people and share my experience with a modicum of confidence. This blog post by Brent Ozar had a lot to do with it too.

I got – and still get – nervous when I present. It doesn’t bother me much, though. It’s like an old friend, a companion I’ve known for a good thirty years. I wouldn’t want to be without it.

T-SQL Wednesday #21 – Wait For It…Wait For It…

by

T-SQL WednesdaySometime in 2004, I had to revise a well-established import process that was clogging up the production SQL server with gooey slowness. The size of the data we were loading had grown dramatically since the process was last modified, and the server was overburdened.

At the time, I really only knew SQL syntax and a handful of basic commands you might see in Access: SELECT, INSERT, DELETE, and maybe PRINT. Basically, I was no more educated in T-SQL than an Office power user. Armed with what little I knew, I had to make our server responsive while this process was running. I opened the script and began to search for something, anything, to tweak.

After a lot of staring and head-scratching, I found something. A command that would ease up on the gas.

I began throwing this command everywhere I could in the hopes it would throttle back the process. I ran the process again, and it worked beautifully. No longer was the server unresponsive while importing these large data sets. Outstanding.

What command did I use, you ask?

I found a single instance of WAITFOR, sitting innocently at the end of the loop. That’s right. In a loop. Assuming this to be a magical command, I proceeded to  stripe the code with WAITFORs everywhere. Inside a loop, which was really a CURSOR. That sat inside a CURSOR. Which sat inside another CURSOR. (Are you vomiting yet? I’m nearly there myself.) Performance was abysmal — “We’ll let it run overnight, maybe it’ll be done by morning,” — but I had freed up the server.

I had traded a problem I couldn’t tolerate for one that I could.

At the time, I wasn’t really interested in learning T-SQL (or anything else about SQL Server for that matter). It’s therefore perfectly reasonable that I would be okay with exchanging one problem for another. Doing so required no development on my part. Solving the problem, however, would have meant learning about performance tuning and best practices.

I wouldn’t want to revisit that code; I know it’s awful (and hopefully no longer in use) and I wouldn’t get much satisfaction from rewriting it. I’m just happy knowing I don’t trade problems anymore. These days, I much prefer to solve them.

T-SQL Tuesday #016: Aggregations in Reporting Services

by

It’s Tuesday, which means two things:

  1. I may or may not be out of Craisins…not sure.
  2. Time for a T-SQL Tuesday post.

I’ll check on the Craisins later.  Let’s do the post.

Last I checked, most elementary school children can take a list of numbers and add them up as they go.  That is, give them a list of 3 + 4 + 5 + 1 and they’ll probably tell you 3 + 4 = 7, 7 + 5 = 12, and 12 + 1 = 13.  Simple, right?  If you ask them to sort it from lowest to highest, omit the odd numbers, and make a second list based on numbers in the first, they’ll stare at you, then tell you they had an accident in their pants.

Did I mention I'm pretty good at coloring, too?

Reporting Services works much the same way.  If you ask it to do things it can do as processes data row by row, like RowCount, Sum (with or without an explicitly defined scope), or even Previous, it’s pretty efficient.  However, if you ask it to start shuffling items around (sorting), cutting items out (filtering), or relating items to each other (subreports), Reporting Services is not able to deal with it as efficiently.  Why is that?

Reporting Services has no indexing on its datasets.  None.  Zero.  Zilch.  Nada.  The null set.

BingGoogle “Reporting Services Indexes” and you’ll get results less useful than those Ensure coupons the grocery store registers insist on printing for me.  (I’m a 35 year-old man!  Give me something I’ll actually use, like a dollar off Craisins!  I’ll bet that Ensure nonsense comes from a bloated Access database in Branson.)

Once the dataset comes back from the data source, it’s nothing more than an ordered heap.  Performance-wise, datasets in Reporting Services behave a lotlike ASP.NET datasets.  If you return thousands of records in either one, then sort and/or filter them in the dataset rather than getting them pre-sorted/filtered, you’re wasting precious seconds.  Several seconds may not seem like a big deal, but to a user watching the spinning green circle, the difference between two seconds and ten is an eternity.  That said, if your report is interactive and the typical use case involves a lot of manipulation, it may be worth letting Reporting Services shuffle the dataset, rather than pounding the data source again and again with variations of the same data.  It depends.

If you’re trying to decide where the summation logic should go when designing a report, treat the Reporting Services like a little 2nd grader.  If you want it to count the items as it goes (RowCount), add them up as it goes (Sum), or tell you what item it just looked at (Previous), you’ll be just fine.  If you want something more complicated than that and your dataset is not insignificant in size, ask its older sibling — the database engine — to do the work.  Otherwise, you’ll likely see Report Server poop its performance pants when you check ExecutionLog2.

But then, what do I know?  I’m still not sure if I’m out of Craisins.