Managing Your Software Upgrade

Software Upgrade. We have recently been working on an upgrade project that has turned out to be quite interesting. The aim was to upgrade a very old Microsoft Access Database Application to run with a shared SQL Server backend database to enable faster load times and improved performance generally.

A Bit Of History

We took over development of this system for a Client after their original Developer retired. It is a very complex Job Tracking System and the Developer liked to write lots of code. Often using his custom code instead of existing functions, for whatever reason.

The Software Upgrade Target

We planned to move the Data from an Access Database to a SQL Server Database. SQL Server is Microsoft’s Flagship enterprise level database engine and can scale to unimaginably big systems. We however, choose the “Express” version as it’s free and will easily manage the data from the old application. SQL Server also offers other advantages of greater stability, more users (the Access Application has about 60 users) and Backups with an ability to Restore to any point in time if needed.

The Big Data Transition

The actual Data Migration from Access to SQL Server was very simple, as we expected. Most of the application functioned as expected, some took a little longer and there were a few minor syntax errors that we needed to adapt to the slightly different SQL Server syntax.

The Software upgrade problems came where the previous developer had used extensive looping of data records to perform tallies and calculations. As each of these required a request to go to the Server and a response to be returned as it worked through thousands of rows of data. Remote users suffered extensive delays as data travelled through the internet to their PC and Back. It was only after we investigated these delays that we realised the technique the previous developer had used.

The Software Upgrade Solution

Using our experience with both Access Data and Visual Basic Code we reworked the slowest functions in a more efficient manner. Several were rewritten entirely to run on SQL Server, while others were better optimised for the Server to perform more of the processing. Once completed the Server returned a “Result Set” – rows of Data for Access to Display on screen.

How Bad Can It Be?

To benchmark our progress, I went back to the original Database and timed a particularly slow Function. The hourglass / donut spun and spun and spun… I timed a full THREE MINUTES, that users were waiting in frustration for a single screen to load. – no wonder they were reluctant to use the function. Looking into this the process made thousands of requests to the Database, built no less than FOUR temporary tables (each pulling Data from SQL Server) and looped through these several times to calculate some values, then wrote these back to one of the Temporary tables for the screen to load data from. Sadly, I also discovered that due to overly conservative design, this whole process was unnecessarily repeated three times!

The Result of the Software Upgrade

Staff will now get the results they need without the frustration and delays.

It took a long time and some very extensive scripting of SQL Server but the Software Upgrade result was that this screen that previously took three minutes to load, would now load in between 0.5 and 0.8 SECONDS! Using the exact times, I calculated a load speed improvement of between 225 to 300 TIMES FASTER than before the optimisation.

We are continuing to test and optimise further before we “Go Live” but it’s looking like the Client is going to see significant speed increases and much reduced staff frustration. You can read of some of our other Optimisations such as Visual Efficiency, What’s a Few Seconds? and Quoting Efficiency

Custom Business Software

Our Custom Software has the ongoing advantage of being continuously improved to improve your efficiency and match your changing needs. Contact us now or click to book your FREE Initial Consultation online to discuss how ProcessIT can help you with a Software Upgrade…

Tech Tip – Get your Vaccine Pass

When New Zealand heads into its next Covid phase known as the Traffic light System you will need to use your Vaccine Pass to access stores and venues.

Those that do not have a Vaccine Pass will be denied access to many facilities’ country wide.

The fastest way to get a Vaccine Pass.

You will need a unique email address and a valid ID.

Request a pass

• Sign up at this website here https://mycovidrecord.health.nz/ You will need to give an email address.
• Once you have verified your email address you need to enter proof of identity such as Drivers License or Passport number.
• Next in the ‘Passes and certificates’ section, select ‘Request pass or certificate’. (as in Image on the right

• The ‘Select a pass or certificate’ screen will open. Select ‘My Vaccine Pass’ and then select ‘Continue’.

In the ‘Your details’ screen, enter the email address that your vaccine pass should be sent to and select ‘Request pass’. A confirmation screen will appear.

Within 24 hours (often only a few minutes) you should get an
email with your passport attached as a PDF. -Easy!

Print out the PDF and cut out the Pass for carrying with you, and / or…

To store the document on your Phone you can use Google Pay for Android or Apple Wallet on Apple. Just open the Email on your Mobile Phone then click on the appropriate link for Android (Google Pay) or Apple (Apple Wallet) to install the Passport to your Phone. Note that you may need to install the appropriate app first.

Until next time, stay safe, take care and happy computing…

Regards,
    Brian and James