A Beast Arose From The Night
The elders told tales of how, in the olden days, the Beast had risen on Sunday, consumed data and excreted analysis, not resting until Saturday; only to rise again the next day. In those days the Beast was constructed of VBSCRIPT. But over time the Beast had been attacked with .NET and multithreading and now merely roamed in the darkness of the night, from the time the clock struck three until the light of the sun at seven in the morning.
In their hubris, us village craftsmen thought we could feed the Beast a tenth more of it's preferred food, and in return the valuable analysis would be waiting for us with the rising of the sun. The Beast in all its incarnations has consumed from many tables, and output a list of which things were most like each other, piled on to one table. And though we knew that such an operation is on the Order Of N Squared, a tenth measure more data should have but grown the beast little more than a fifth measure. And so we left our offering for the beast and went to sleep.
In the morning we awoke to find carnage and screaming and timeouts. The villagers were doing their best to go about their business, but could tell something was wrong. For the Beast had not been content with a mere fifth more consumption of our resources, but had stayed awake more than twice as long as the nights before; and it was consuming many resources. The Beast's many arms were pulling things off the table and putting them back so fast that the arms were crashing into each other, a deadly dance of deadlocking.
Hue and cry went out amongst the heroes of the village. The skills of many would be needed to subside the Beast again. We first said the incantation to put the Beast to sleep while we worked. IT was called upon to provide a larger cage for the Beast, comprising an octet of cores and much memory. Development plied the Beast with hashsets and performance traces. And, perhaps most interesting to those now listening to my tale, together the heroes made many changes to the sql of the Beast. XML Parameters were replaced with TVPs to reduce CPU load. A merge statement was constructed to avoid delete-insert, thus removing need for a lock (dead or otherwise) on the target table. And a wise clustered index was chosen to work with both the merge and the consumers of the data, minimizing fragmentation and maximizing index use.
It was with trepidation that we reversed our incantation and left our offerings once again for the Beast. All was quiet and calm. When we checked the logs, what we found surpassed even our wildest expectations. The Beast risen and gone back to its rest in a mere twenty six minutes. Furthermore the Beast consumed the merest of resources while it worked, calmly processing data and being not a bother to those (admittedly few) sharing its environs in the night.
I VANQUISHED THE BEAST! With help, of course, from my fellow heroes of the village.