It is important to understand the business impact of the work that is being delivered.

I often review systems that Vetasi are about to support and the checks include looking for performance problems. My analysis includes an automated log analysis which provides a very detailed report on the slowest pieces of SQL as reported by the slow SQL warnings.

The 30 second public query

Teams often use public queries to manage their work and it isn't unusual for queries to be fine tuned as people identify the best way of working. Where clauses are often extended to exclude irrelevant rows and this was the case with this customer.

The automated report highlighted that the amended query was taking 30 seconds to run. The query should have delivered a significant benefit but instead it was slowing down the team.

I queried the SQL/execution time and I was told that it wasn’t anything to worry about because “it only takes 30 seconds to run”. 30 seconds can feel like a long time and indeed the business impact can be significant.

The business impact of a 30 second delay

This was the true impact of those 30 seconds:

• 20 people would initiate the query at/about the same time so it wasted 10 minutes
• The 20 people would then use that saved query 8 times a day (e.g. once an hour) so it wasted 80 minutes a day
• Those 20 people did that work every working day so it wasted 400 minutes a week or 6 hours. 6 hours is getting close to a working day lost/the cost of an additional person
• Management reported a drop in productivity because their team seemed to have slowed down
• Other users reported performance problems and analysis showed that these were when these queries were being executed.

The opportunity cost of 30 seconds … 60 calls an hour.

There is also the opportunity cost of those lost seconds. Imagine that the delay isn't affecting a small team but is in fact delaying a floor of a call centre dealing with customer calls.

Users working in call centres are often expected to process a call within a set time e.g. two minutes. There may even be financial penalties if the calls are not closed within that deadline.

Assuming a theoretical rate of 2 minutes a call then an operator could expect to process 30 calls an hour.

If there is a 30 second delay then each call takes 2.5 minutes. At 2.5 minutes the rate drops to 24 calls an hour. That is 60 calls lost every day per person working answering phones 6 hours a day. That can quickly add up for call centres with lots of call centre operators.

And the original query that took 30 seconds?

The problem was that the query was performing a Full Table Scan so it was filling the cache with records and prevented other queries from using the cache.

Once the full business impact was explained then the query was quickly tuned. The indexing on the table was amended to include the relevant columns.

The final query took 5 seconds but its additional filters helped remove records that were incorrectly displayed previously and the overall result was the desired time saving.

A side benefit was that the team was happier because they didn't have the frustration of working through irrelevant records and they saw that their complaints had been listened to.