Project Overview

In a service drop inspection management system operated by a client in the electric power infrastructure sector, we analyzed and tuned processes and SQL queries that were acting as bottlenecks in the inspection data list screens. This resulted in a significant improvement in response speed. We resolved the issue of reduced operational efficiency caused by display delays and contributed to enhancing the User Experience (UX) while maintaining the stable operation of the entire system.

Tech Stack and Development Tools

• Programming Language: JAVA
• Full-stack Framework: Client-proprietary framework
• Database: PostgreSQL
• Cloud/Server/Infrastructure: Wildfly server
• Task Management Tool: Excel
• Communication Tool: Mail (Outlook)

Client Issues

• Significant Response Delays in Inspection Data List Screens
  In the currently operating service drop inspection management system, the performance of screens displaying lists of inspection cases had degraded significantly. Specifically, waiting times of over one minute frequently occurred when searching or displaying vast amounts of inspection data, posing a major problem for the system’s practicality. Such long loading times caused significant stress for users and became a factor hindering smooth business operations.
• Decline in Operational Efficiency and Deterioration of UX
  The aforementioned response delays were not merely an issue of waiting time but adversely affected the entire daily inspection management operation. The accumulation of waiting times for screens to display reduced the processing speed of the overall workflow, significantly damaging the productivity of the staff in charge. Furthermore, poor responsiveness to operations greatly harmed the UX, leading to concerns about reduced reliability and user satisfaction with the system.

Client Requests

• Investigate the causes of response delays in the list screens.
• Renovate the target screens to improve response times to a level that does not hinder actual business operations.
• Ensure that the entire system remains stable after renovation and that existing functions continue to operate normally.

Our Proposal and Approach

• Deep Understanding and Compliance with System Common Specifications
  Before starting the renovation, we thoroughly read and strove to understand the system-wide common correspondence specifications and conventions provided by the client. By accurately grasping the proprietary framework and existing design philosophy, we built a foundation for formulating appropriate modification policies while maintaining the consistency of the entire system. This enabled us to implement a safe improvement approach that adheres to the “etiquette” of the existing system, rather than ad-hoc fixes.
• Identification of Bottlenecks and Optimization of Processing Logic
  We conducted a detailed survey of the current status of the list screens where response delays were occurring. We analyzed the processing flow within the program and identified the bottlenecks causing the time consumption. Based on this, we identified areas where wasteful loops or inefficient data manipulations were being performed, and modified and optimized the necessary logic to improve response speed. This reduced the processing load on the application side.
• Analysis and Tuning of SQL Queries for Speed Enhancement
  Regarding database access, which was the main cause of screen display delays, we conducted a detailed analysis of the target SQLs. While checking execution plans, we identified queries that took a long time to retrieve inspection data and implemented tuning such as rewriting SQL or utilizing indexes to extract data more efficiently. We aimed to improve the structure to retrieve necessary data at high speed while minimizing the load on the database.
• Verification of Improvement Effects Based on Quantitative Data
  To objectively evaluate the effects of the modifications, we measured and compared SQL execution times before and after the renovation. Instead of sensory improvements, we visualized “how much faster it became” with specific numerical data to verify the effectiveness of the renovation. This process ensured that the target response speed was achieved and enabled us to provide an improvement report with a solid basis to the client.
• Visualization of Changes and Creation of Detailed Documentation
  To ensure the transparency of the renovation content, we recorded the details of the SQL before and after modification, as well as a list of methods in the program that were changed. We compiled comprehensive documentation of the changes made and the functions affected to ensure maintainability. This documentation makes it easy to trace the change history during future maintenance or troubleshooting.
• Prior Sharing of Renovation Proposals and Consensus Building with the Client
  Before entering the actual modification work, we created documents summarizing the renovation proposals and impact scope for each screen and sent them to the client for confirmation. By going through the process of aligning perceptions and obtaining approval for the proposal content in advance, we minimized the risk of rework. Close communication with the client prevented unintended changes and ensured strict progress management so that improvements proceeded according to requests.
• Implementation of Regression Testing to Ensure Overall System Quality
  After improving the list screens, we conducted thorough re-testing (regression testing) of the entire system. In addition to confirming whether the target screens achieved the target response speed, we comprehensively verified that this modification did not cause any unexpected adverse effects (degradation) on other functions or screens. This guaranteed that the entire system would operate stably even after the improvement, realizing a high-quality delivery.