Query optimization is the first place everyone looks when doing performance optimization on reports, but there is more to performance optimization than just optimizing queries.
Proper use of tables, joins conditions and wide data ranges are all solid techniques for optimizing queries. However, data quality is another factor in report performance.
Let me share an example from an organization we were working. The company was using the project management module of an ERP system to track hours, billing and expenses. They had more than 500 projects in the system and ran a weekly report that is configured to run on the database of active projects to provide detailed information on hours consumed, capacity, project status and resources breakdown.
The problem is that it takes one hour to run the report and requires a lot of computing power even after query optimization.
The report is developed to show the information for only active projects in the week. There is no process of closing and archiving the completed projects on regular basis. As a result, the query had to run through all the projects that are marked active, even though these are actually completed.
The solution to this performance optimization challenge was not query optimization in this case. Improving the process of changing the status of projects and archiving them resulted in the performance improvements that they needed. This resulted in better data available in the database and less time and computing resources to produce the query output.
After implementation of the process, the organization has seen significant decrease in the query run time.
The lesson learned here is that report performance optimization isn’t always about query optimization. The people that touch data at any point can also have an effect on performance.