11. Let the Adventure Begin! (9m)

Alright, it’s test time. You’ve learned a lot about how to rewrite your T-SQL to improve performance. Now, it’s time for a fast-paced adventure where you have to answer questions fast to slay your performance dragon.

Speaker 1:             Now that you’ve learned some new skills why don’t you join us on an adventure, tell me what is your name, Fartbringer, [00:00:13] very good, a quick introduction to your party members, this is Hashmatcher [00:00:23] and this is Voltaire Perfmon [00:00:26]. Quick one word about your character gentleman, Hashmatcher.

Speaker 2:             Handsome.

Speaker 1:             Voltaire.

Speaker 3:             Witty.

Speaker 1:             Outstanding. Rare and noble qualities indeed. Follow us as we continue our search for the lost staff of IO. You descend down a stair case, torches on the wall illuminate the way ahead, suddenly the torches extinguish and you are left in total darkness.

As your eyes adjust you see an eerie green glow at the end of the hall, you follow that hall to the end.

Speaker 2:             We proceed.

Speaker 1:             You enter a massive round chamber, the ceiling is at least 50 high and nearly as high a pile of bones.

A pile of bones that occupies most of the room, a ghostly figure, transparent and glowing green floats up and down the pile, retrieving and smashing bones one at a time.

The door slams behind you and will not open, you are trapped. The figure looks up, takes note of your presences and then continues on uninterested in you.

Speaker 3:             I ask who it is.

Speaker 1:             He says, I am the cursed cursor, I cannot leave this world until every bone is smashed.

Speaker 3:             [00:02:00] Does he know of a way to get out?

Speaker 1:             The only door is a trap door under this pile. What will you do Fartbringer? Um, very good but how? Genius, the figure says. Mounted on the wall is a lever, the ghost pulls the lever.

With a deafening boom a cylindrical section flies down from the ceiling like a press and smashes the bones to powder. A huge cloud of dust renders the room … What’s the word for you can’t see … You can’t see.

Thank you the figure says, I am free at last, as are you.

He blows the powder away to reveal a door in the center of the floor, and then smiling he vanishes.

Opening the trap door you discover a ladder leading to a passage way below.

Speaker 2:             Let’s keep going.

Speaker 1:             The hall empties to a small square room. Mounted on the wall on the opposite side of the room is a large wooden panel, dense with engravings.

It resembles a giant table of names and numbers. As you step forward to read closer a voice echos through the chamber. Many great warriors have come before to settle many and ancient score. They recorded their mighty haul until they were the ones to fall. Tell of a way to reveal the sums of the last four battles before [00:04:00] they were done.

Speaker 3:             It sounds like we need the total gold of the last four battles for each warrior listed.

Speaker 1:             Hashmatcher.

Speaker 2:             I know the list of warriors then I join them back to the list with another-

Speaker 1:             Wrong, flames leap forth from the wall and singe you for 5 hit points.

Speaker 2:             Just a flesh wound.

Speaker 1:             Fartbringer? Excellent a window function to get the last four rows, but tell me which of these functions is the correct one?

You speak out loud the function, numbers begin to singe away from the columns until finally the last four numbers for each warrior remain.

The room begins to shake and the huge wooden panel appears to be coming loose from the wall.

Speaker 3:             I move to the other side of the room, let’s back up guys.

Speaker 2:             I move for no man.

Speaker 1:             The panel falls on Hashmatcher crushing him for 3 hit points.

Speaker 3:             I told you. Here’s something I haven’t told you, your helmet looks like the chimney on my dad’s meat smoker.

Speaker 1:             (laughs)

Speaker 3:             I want to throw some wood pellets and a pork shoulder in there.

Speaker 1:             As you step over Hashmatcher’s comically flattened and prostrate body you hear a sound coming from the end of the hall.

Speaker 3:             This is getting redundant with the halls and stuff.

Speaker 1:             Shut up, I need a day job.

Speaker 2:             What is the sound?

Speaker 3:             Barbecue.

Speaker 1:             It is a loud, almost electrical humming.

Speaker 3:             Let’s check it out.

Speaker 1:             At the end of the hall is a larger room, roughly 30 feet by 30 [00:06:00] feet. Circling the room are words floating by.

Upon closer inspection it’s query text, furiously spinning revolutions as if orbiting the center of the room. In the center you see a short pedestal and sitting upright in that pedestal, like a flagpole in its base, is the staff of IO.

Speaker 2:             I tried to touch one of the queries.

Speaker 1:             You get jolted for 8 hit points, I hope you have some left.

Some queries have date part in them, now those are made up of seemingly endless if’s.

Speaker 3:             How do we penetrate the ring of queries?

Speaker 1:             Fartbringer what would you do? Once you begin you must invoke the changes quickly.

I will tell you what it is you see, you tell me the alternative to coding it. Fartbringer are you ready? Begin.

Endless if’s that do nothing but set values, correct. Cursors that do nothing that update or insert rows based on conditional logic, correct. Filling in gaps in numerical data, correct. Date part, correct. Detail in aggregate using common table expressions in the same data set, correct.

Speaker 3:             Fartbringer’s going to do it.

Speaker 1:             Last one, all the remaining queries have the same formula written in them, how can we only write this formula once? The last queries evaporate and all that remains in the room is your party and the staff of IO in the pedestal.

Speaker 3:             I take the staff. [00:08:00] No, wait Fartbringer should do the honors.

Speaker 1:             Fartbringer you close your hand around the staff and lift it from the pedestal, it is amazingly light. You can feel its energy pulsing through your hand and arm, it is truly a magical possession.

In claiming it you have earned the necessary experience points to level up.

Thank you for joining us on this adventure and congratulations (clapping).

Speaker 2:             Congratulations.

Speaker 3:             Well done, Fartbringer, well done.