In 2013, a performance consultancy was engaged to address a critical performance issue faced by a client in the clothing retail sector. At the end of each month, a batch process would run for four hours, completely blocking access to the back-office application for hundreds of users. This situation incurred significant costs and disrupted operations.
The primary objective was to eliminate the blockage for users and enhance the performance of the batch process, which was developed in Visual Fox Pro and interfaced with a SQL Server database.
The initial briefing
A meeting was convened with key stakeholders: the consultancy team, including senior leadership, and more than five representatives from the client. During the meeting, the client's DBA presented SQL profiler data, highlighting the queries executed during the batch job.
Analysis of the profiler data revealed a concerning pattern: out of 20,000 queries executed, approximately 70–80% were identical SELECT statements. Each of these repetitive queries consumed significant CPU resources and had execution times exceeding 100ms, indicating a major performance bottleneck.
The root of the issue lay in the use of a function in the WHERE clause of the query. SQL Server, which normally leverages indexes for rapid data retrieval, was forced to perform a full index scan rather than an index seek because of this function. As a result, the query was executed 14,000 times, severely impacting performance.
During the consultation, it was identified that the application applied an ISNULL function to a column that allowed null values. The consultancy proposed a simple yet effective solution: removing the ISNULL function by validating data within the application and updating any existing nulls to zero in the database. This eliminated the need for costly index scans and significantly improved query performance.
The First fix
By removing the ISNULL function and implementing validation in the application, the consultancy reduced the execution time of the batch process from four hours to just 15 minutes. This drastic improvement exemplified the common performance optimization principle that resolving a single bottleneck can yield significant results.
The consultancy team approached the challenge using the Pareto principle: 20% of the effort produced 80% of the results. Further improvements, while possible, would require more extensive efforts for diminishing returns.
Following the initial fix, the client was pleased with the outcome, but the consultancy identified additional opportunities for refinement. These included cleaning database tables, modifying the application to batch queries, and other optimizations. Implementing these enhancements further reduced the batch processing time to five minutes.
A comprehensive report and presentation were delivered to the client’s vice president, detailing the pragmatic improvement process and the successful reduction of batch execution time from four hours to five minutes. This project not only resolved a critical issue but also fostered a long-term partnership between the consultancy and the client.
Final recommendations
The consultancy concluded the engagement by emphasizing the importance of approaching complex performance issues with a fresh perspective. Taking breaks, stepping away, and returning with a clear mind can often reveal straightforward solutions to seemingly intractable problems.
This project demonstrated that structured analysis and focused effort can unlock significant performance gains, ensuring efficient and uninterrupted operations for the client’s critical systems.