We were recently asked to work on a piece of software that was struggling to execute reports. The problem, as it was described to us, was that reports were either taking forever to load or were ‘crashing’ the software and no data could be viewed.
The software in question utilised an older version of a database that was no longer supported. That in itself was not really the problem. When we investigated the issue, two things emerged: one was the overall design of the database and the second was the inefficient way the software was trying to extract information from the database.
In order to function efficiently, information held in a database needs to be stored in a logical way making use of indexes, having relationships modelled in the database using foreign keys and using the database to filter out results. This ensures that you get exactly the results you want consistently and quickly, whilst also avoiding broken links between tables.
In this particular case, the software generating the report was executing several queries to extract one piece of information. It was also pulling back lots of information that it didn’t actually need for the report and then having to sort the data several times over.
Once we had discovered what was causing the problem, we were able to re-write the software to make everything more efficient, significantly reducing the time taken for reports to be generated. We also made recommendations for the data to be migrated to a newer database.