We work on reporting systems quite frequently. As businesses grow, over time their reporting requirements change and reports need to be updated. “Add this field, calculate this value,” and more. So what happens when all those changes to a report suddenly add up and the report is taking 20 minutes to run? A new client came to us with this problem. And here’s how we fixed it!

Analyze the problem

A slow report could mean some combination of factors:

• Lots of computation in the programming language?
  • Inefficient algorithm
  • Weak computer used for processing
• Lots of computation during the data access?
  • Inefficient query
  • Poorly formed data (lack of proper indexing, far too normalized data)
  • Too much data

So how do you determine the cause? Look at each one.

In our case, the report was a list of customers’ appointments with unpaid charges. The process was:

1. Get all the appointments, the appointment details, the different charges at each appointment.
2. Sum up the charges for each appointment
3. Skip the appointments with zero balance

Devise a solution

Often, one item on the above checklist will point out a single point of pain that can be eliminated to boost performance. In this case, it was trickier. Looking at our checklist, what is going wrong here?

• Lots of computation in the programming language? - nope
• Lots of computation during the data access? - maybe
  • Inefficient query - nope
  • Poorly formed data (lack of proper indexing, far too normalized data) – nope, database was set up fine in this case.
  • Too much data - yes

The process looks like a fine algorithm, until you realize for this client, most appointments have all charges paid. Pulling 60k appointments to find 1800 that have unpaid charges isn’t efficient.

Our solution was to look through the appointment charges and find all the non-zero-balance appointments first. Pulling 1800 records is much easier than 60k.

1. Get all the charges per appointment to find which ones have zero balance
2. Get the appointment details for the proper list

Relish the results

Do more database queries provide poorer performance? Not in this case. Moving from 1 query to 3 + n (1 per appointment) sped up the process tremendously. On the test data set, we saw marked improvements in time to generate this report:

Before: 1:41

After 0:06

I love bringing this kind of huge improvement to people, they’re always so excited to see these results. And not spend their time waiting and waiting for reports to run. Next time you’re working on a performance problem, I hope this helps open new options for making things run faster and more efficiently.