…or, How I Learned to Stop Worrying and Love the Bomb
I had a SQL dev’s dream come to me via email yesterday: a high-urgency, high-profile request for data that was both interesting and doable. I spent a few hours on it last night and this morning, and handed over a beautiful solution that was well appreciated. The end.
Shiny!
Well, not really the end. This task made me think hard about the right level of “right”. Of course there’s more than one wrong way to retrieve data: for example, anything that wastes massive effort or resources, or comes back incomplete or otherwise incorrect. But there’s a balance to all things… in this case, a balance between “BEST POSSIBLE SOLUTION” and “slapped-together piece of nearly unusable crap “. We’re too often pushed toward the latter by time constraints and demanding situations, but we can also be lured into unproductivity by the shiny shinyness of the former.
What the hell am I talking about? I’ll tell you.
To take the example of my task: It was a request for two spreadsheets representing data from 2010 and 2009…a moderately complicated pivot of (real-life) entities and events, and which participated in which. The way the data was structured, I had a few options:
1. Best Solution
The absolute best solution (or, one of the best solutions) would be to
- Set up a user-defined table datatype of event keys, and declare an @Event table of that type (to be passed into a stored procedure).
- Write the SP with some dynamic SQL to create and populate a #temp table using participant Entity data JOINed to the @Event parameter table, and a final select out.
- Bust out an SSRS report to use that SP, and set all that up in our user interface to be called on demand, or schedule it to run monthly and publish to a shared folder.
For this particular solution, the dynamic SQL would’ve kicked my butt for too long, and the report would’ve taken (in my opinion) more time than it’s worth. Plus, we have other considerations: the SP should really be flexible enough to produce this report, or allow for a report on a single Entity’s participation in Events; that’s another layer of complexity. Finally, this is a report that won’t run often: maybe twice a year, in my estimation. That’s an awful lot of time to spend on a setup that the users need NOW NOW NOW.
2. Crap Solution
Create a manual pivot SELECT statement, based on the events needed. This is a horrible solution: while it’d probably be significantly faster than the Best Solution above, it’s be a huge pain in the butt, and it would have to be done for the 2010 data, and redone for the 2009 data, AND redone any time someone wants to rerun for 2010 data (events are still being added this year, you know). The simplest solution, even in a quick-fire situation like this, is not always the best solution.
3. Middle Ground
My solution was a temporary table of event keys, a manually created “pivot” temp table, and a cursor to update it for each event column. THAT’S RIGHT, A CURSOR! Cursors shouldn’t be part of your everyday code, absolutely. But sometimes they are beneficial, and have a minimal impact. This was such a time: it simlpified and quickened my development cycle, had a minimal impact (cycling through 114 repetitions, not millions), and isn’t at all likely to be used often (no more than twice a year max).
Several of you just blew a gasket, because this isn’t something you would have done. I understand, and I’m with you…but there’s right, there’s wrong, and then there’s the right level of right.
Happy days,
Jen McCown
