A cursur-y look at SQL

I have been rewriting the invoice generating sub-system for the company today. I’m doing it in two steps; a direct port of the (VB6) code that currently runs and then I’ll see if it’s worth adding some SQL trickery to optimise the process. Step two probably won’t happen.

The ported process is hundreds of times quicker to run than the existing code. The original code takes about 10 minutes to complete – SQL Server takes 3-4 seconds. The original code has progress bars and other “this is going to take a long time” entertainment but if it’s only going to take less than 5 seconds to get the same results, I won’t be coding them across. They were only needed because it was taking such a long to complete.

I have used cursors to get through the iterative process of decoding what to do with each item that is ready to be invoiced. I know it’s frowned upon by “them that know” but quite simply, it is the most cost effective solution for the moment – there aren’t millions of invoice lines needing to be processed so the extra work required to “make it perfect” isn’t justified.