We’re always looking to deliver optimum performance in our web app and continually run benchmarks and tune our database to shave off a few milliseconds here and there. Obviously this becomes more of a challenge when the database starts to inflate but due to our ongoing efforts on this front we’re delivering better page load times now than when the database was a quarter of its current size.
One of the recent leaps forward we made was in relation to the page load times within the Purchase Management screen, this is basically the screen that list out all purchase invoices, it can be called in a number of ways to produces detailed views on outstanding invoices, ageing statistics and supplier filtered views. Until about 2 weeks ago these pages were creeping up to the 6-7 second mark. This is far too slow for such a critical part of the application so we decided to run some detailed reports to see where the latency was.
Unlike some other accounting platforms we don’t run any critical infrastructure in the public cloud and instead run our application on purpose built, on-premise hardware (I blogged about this here). This allows us to leverage the maximum amount of processing horsepower per £ spent and gives us highly predictable performance. Also our database servers are parked right next to our web servers and wired together using gigabit crossover cables so there’s negligible network latency.
So going back to the purchase management screen we ended up pulling apart the SQL Query to see which JOIN or sub query was taking the time. This is somewhat a process of elimination but also the query plans in SQL 2012 are very useful. The query that pulls out the invoice list is quite complex and relies on a number of other data tables being dynamically joined and parsed before we can output to the page.
After some analysis we discovered that the query time had almost doubled due to a join that was being carried out to determine if any receipts scans had been attached, this was relying on a string comparison in the join which was far from ideal! To solve this we re-indexed the whole receipt scan table and modified the inner join on the master table to reference an integer ID. The NET effect was a significant reduction in page load time with most load times dropping from 6-7 seconds to 1.5 - 2.5 seconds.
This type of performance tuning results in an all round better user experience, it’s very annoying that feeling that you’re wading through treacle (OK maybe a first world problem). Many of the dilemmas we face when designing our database layer is deciding whether to use high speed cached data or slower dynamic data. We always push for the latter particularly in core parts of the application and every effort is made to ensure this data is retrieved and delivered as quickly as possible.
So whenever you’re waiting a long time for pages to load, we want to know about it. It’s food for our programmers 