It's the most I've ever seen, for sure. Since I've been complaining about this 3rd party vendor problem for 3 years and watching nothing get done and it now being a serious problem that's interfering with our processes, I wrote some Proof-of-Principle code and sent it to the people that now suddenly care. It took me 3 iterations but the code now runs just once per document, does everything it needs in one fairly short stored procedure, and runs literally 960 times faster (125ms instead of more than 2 minutes per document.
SQL Server squawked at me for my code (a previously tried and true nasty fast method with fewer levels for something slightly different) being too complex to create an execution plan even the code. I was gob smacked because it only consisted of only really super simple simple cascading CTEs.
The rewrite to simulate that same code in a manner that SQL Server could actually handle came at a cost. It took 2-9 seconds to compile and SQL Server saw fit to recompile it every time it was called. While that is still 13 to 60 times faster than the original 3rd party code, it wasn't good enough for me, especially because of the recompiles.
I stuck with it and seriously changed tactics. I ended up with some fairly short code that only takes 125ms per document and doesn't need to recompile even in the face of changing parameters for every document.
So, to answer some of your questions from the article....
While youâ€™re thinking about it, think about this - how do you react when the problem is hard. The canâ€™t find a place to start kind of hard, the hours and hours into it and still no progress kind of hard. Do you stress out? Blow up? Give up? Grind through? Or can you smile at a challenge worthy of you, enough to make you exclaim â€œWatson, the game is afoot!â€?
I could have quit on the first iteration when SQL Server told me my code was impossible to parse. I've been at war with the optimizer for 22 years and I wasn't about to let it win this one. The optimizer actually works pretty well when you apply liberal doses of pork chop grease to it. 😀
I could have settled on having a 2-9 second compile time during the second attempt and have the code be 13 to 60 times faster than the original code. Even if it wasn't due to constant recompiles, that still seemed pitiful to me especially since I know that we need to double the scale of all of this in the next year or so. I wouldn't blame someone else for settling especially since I did it all on my own time yesterday (a Saturday on a holiday weekend). It's just that this company has been good to me and the people I work with are good folks and it was simply my turn to turn to. It's also not my favorite system and I really wanted to do it right the first time so I wouldn't have to revisit it in the future. (Enter... motivation :P)
The third try was really tough because I had to scrap 90% of the previous code and start mostly fresh. I abandoned all previous preconceived notions and went with the "Ok... pretend you've never seen this type of problem before" notion and it worked. At 960 times faster and all in a relatively small single stored procedure that gets rid of a ton of other code, it was all worth it.
There's also a sense of pride in craftsmanship that goes into all of this. To be honest, a lot of people didn't know how to solve the problem (and neither did I until I tried and got some "how it currently works" information from my boss and he's another reason why I like working for this company ;)). They also scoffed and used the word "impossible" when I told them that I thought it could actually be done all in T-SQL. Heh... yeah, no... I couldn't let that go unpunished even though providing them with a solution isn't really a punishment. 😛 It's that pride in craftsmanship thing again. It's kind of like the statue carver that says, "There's a horse in this hunk of jagged rock... I just have to find it". 😀
To paraphrase to my own liking the motto of the Army Corp of Engineers (which I'm not affiliated with), "The difficult can be done immediately... the impossible takes slightly longer".
I'll also agree that sometimes weeks long interruptions make such tasks take a lot longer and even with really good notes and comments in the code, when you finally do get back to something like this you end up looking at it all and realizing that you've just become Chief "Werda", Chief of the totally lost "Fuhgowee" tribe. 😀
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)