How Agoda Uses ChatGPT to Revolutionize SQL Stored Procedure Optimization
Agoda, a trailblazer in digital travel services, has taken a significant leap in optimizing its SQL stored procedures (SP) by harnessing the power of ChatGPT within its Continuous Integration/Continuous Deployment (CI/CD) process. This strategic integration has not only streamlined the optimization process but has also alleviated the workload of database (DB) developers.
The Journey Towards Optimization
Before the advent of ChatGPT-assisted automation, Agoda’s database developers were dedicating around 366 person-days solely to SP optimization. Of this considerable time investment, 320 person-days were spent troubleshooting performance test failures flagged by the CI/CD pipeline. The core objective? To decrease the time taken to approve merge requests (MRs), which had reached as high as 4.1 hours in the 90th percentile.
Intent Behind ChatGPT Integration
As Pichamon Rungarun, a staff software engineer at Agoda, explains, the goal was clear:
“To reduce manual effort and accelerate SP tuning, we integrated GPT into our development workflow. Our goal was to reduce manual review time, speed up MR approvals, and give developers access to self-service tools for performance tuning.”
The Impact of Inefficient Stored Procedures
Agoda recognized that inefficient stored procedures and SQL queries had a cascading effect on performance, cost, and scalability. These inefficiencies could lead to slow response times, increased resource consumption, and overall scalability challenges. Developers often grappled with typical issues like:
- Inefficient joins and subqueries
- Excessive query nesting
- Poorly designed indexes
- Lack of visibility into query execution plans
By addressing these challenges, Agoda aimed to enhance system performance and user satisfaction.
More Read
The ChatGPT-Driven Optimization Process
To tackle the SP optimization challenges, Agoda’s engineering team developed an innovative automated step that leverages ChatGPT. When feeding the model with SQL code, table structures, indexes, and performance test reports, the LLM generates tailored optimization recommendations.
How It Works
- Input Gathering: Developers provide the necessary data, including the SQL code and performance metrics, to ChatGPT.
- Optimization Suggestions: ChatGPT rewrites the queries and suggests adjustments to indexes, forming an optimized version of the stored procedure.
- Performance Comparison: The CI/CD pipeline reruns performance tests to evaluate the new recommendations and presents a side-by-side comparison to both DB and application developers.
Continuous Improvement and Future Plans
Despite the early achievements in utilizing ChatGPT, Agoda is not resting on its laurels. The engineering team is focused on refining the process and addressing current limitations, particularly in automated logic validation. It’s essential to ensure that even after significant revisions, the stored procedures maintain the same underlying logic.
Expanding ChatGPT Capabilities
One of the team’s ambitious goals is to extend the functionality of ChatGPT beyond the CI/CD pipeline. This will allow developers to access GPT-based support without requiring a formal merge request. By doing so, they can gain insights and suggestions in real-time as they work.
Moreover, engineers are working on fine-tuning GPT prompts to enhance the quality of suggestions provided. This involves training developers to craft better queries, which in turn could lead to even more accurate optimization proposals.
Targeting Resource-Intensive Stored Procedures
Looking ahead, Agoda’s team plans to apply their ChatGPT-driven SP optimization techniques to production stored procedures that consume the most CPU. By focusing on these critical components, they aim to significantly reduce resource utilization and infrastructure costs.
Conclusion
Agoda’s pioneering use of ChatGPT in stored procedure optimization represents a significant advancement in database management practices. By embracing AI technologies and automation, the company is positioning itself to enhance both performance and operational efficiency, ultimately delivering a better experience for users and developers alike. As the landscape of database development continues to evolve, so too will Agoda’s commitment to leveraging innovative solutions for continual improvement.
Inspired by: Source
