Performance issues of in-house developed finance system

The Client

Serious performance issues of Finance System for local manufacturing company

The Problem

We were approached by a company experiencing serious performance issues with their Finance System. The extent of which was severely impacting the usability of the system.

The system had an MS Access front-end with the data being stored in a SQL Server Database. The application had undergone many years of development and provided full functionality for the Company’s financial back office requirements.

The problems arose when returning a list of orders in the application. The issues presented themselves with the application taking a very long time to load the list, and the application seeming to hang for that user as well as for other users of the application that were trying to insert or modify data.

End users of the Application had been enduring the issue for many months which was causing a lot of frustration and required them to work longer hours to fulfill their workload.

The internal IT Department had spent many days looking into the issue without any success.

 

What We Did

It quickly became clear that one specific issue was causing both the slow performance and the application hanging.

Through analysing what was happening in the database it revealed that when the orders screen was populated it was taking around 5 minutes for the query to run. The query plan for the SELECT statement showed that it was efficient, and running it interactively in SSMS the data was returned in under 1 second. Networking was ruled out as being an issue. While this issue can be caused by web queries using different query plans due to parameter sniffing, this wasn’t the issue either.

The hanging of the application for other users was identified as being database blocking. The original query that was taking around 5 minutes to run had locks on specific tables, this stopped other users from doing any modification’s.

The reason why the query was so slow when run in the application was down to output formatting. When the screen in the application was populated with orders, it would return several thousand rows of data, but with only around 20 rows visible on screen, you had to scroll down to view the remainder. MS Access had been configured to use conditional formatting on the rows and when an order was overdue for example it was formatted in red shading. Although the actual SQL Select statement was providing the data in under 1 second, it was taking 5 minutes for Access to format the output. Until this happened the query in the Database was still active.

Once this was identified the behaviour of the conditional formatting and the number of default rows returned was changed and this fixed the issue.

We also suggested enabling Read Committed Snapshot Isolation.

 

 

The Benefits

Within one day the root cause of the problem was identified and fixed. This resulted in the system running normally for the first time in many months.

Further SQL Server Consultancy has been provided to the customer when they require that extra level of expertise.