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.

An itchy trigger finger

Aren’t triggers just marvellous things? An hour after I had added a simple update trigger to the most active table in the database there were over 220 records in the audit table. With this kind of information I could easily create employee activity demographics that can be used to review resource allocation.

There seems to have been very little CPU load put on to the (DC) machine running the SQL Server. Not much of a surprise there as the system isn’t particularly complex at the moment and most data has a short active time frame after which it is rarely accessed. Future developments will see that change slightly, but even then the system won’t be doing hundreds of thousands of transactions an hour.

Management is quite excited with the increased auditing capabilities and is starting to see palpable benefits to having made the shift to SQL Server. All I have to start doing now is managing their expectations so this excitement can be turned into future expansion contracts.

A form with a view

The full data conversion from MS Access to MS SQL Server last Friday night has been very successful! Not only was there no data loss, but very few changes were required to the interface system to keep it working through the weekend.

Now the final chapter of the full conversion begins: creating views for all the long process queries that used to take minutes to return results.

I have already begun the process, starting with the most troublesome queries that are regularly used. The idea being that the most user time will be saved by optimising them first. The pay off is huge for a small amount of work. As the list to be converted shortens management can decide if they want to continue with the process.

I will also be using triggers to add some proper auditing of various tables (those related directly to cash value transactions mostly) which is sewhat of a worry as the company is going to be able to see just how often records are changed by their staff. All in the line of performing their jobs, of course, but through full auditing analysis of the “surface area” of the various systems can be done and I can begin to reduce it to as small a size as possible.

SQL Server conversion

I’ve been having great success with converting some of my split (front-end/back-end) MS Access 2007 databases to an SQL Server back-end.

The upgrade wizard was only so helpful, I had to turn off all the extra options to get the many years of data into the SQL Server. It has become clear to me that over time the data has had various levels of ‘goodness’ and the integrity rules have been quite open to interpretation, or sometimes just automatically turned off when the situation demands it (compact database function).

What the conversion process has allowed me to do is revisit all the data constraints for all the tables and make sure they correspond to the new system’s requirements. I have had to replace quite a few null values, but the overall effect has been to really focus in on how the system has been used in the past. In spite of the way it was designed to be used.

I am also looking forward to having a much less invasive auditing mechanism through the use of triggers. The old method relied heavily on calling the right sub-routines at the interface level which is not the most ideal solution, but certainly provided a level of functionalty that worked.