How Agoda Automates SQL Optimization Using ChatGPT

The High Cost of Manual SQL Tuning

Agoda's database developers were dedicating a significant amount of time to optimizing SQL stored procedures (SP), amounting to roughly 366 person-days of work. A staggering 320 of those days were spent analyzing performance test failures flagged by the CI/CD pipeline. This bottleneck contributed to lengthy merge request (MR) approval times, which sat at a 90th percentile of 4.1 hours.

Inefficient SQL queries are a common pain point for many tech companies, leading to performance degradation, increased infrastructure costs, and scalability challenges. Application developers often struggle with complex database tasks like creating efficient joins and subqueries, avoiding excessive nesting, and designing proper indexes, all of which can result in slow response times and a poor user experience.

Integrating ChatGPT into the CI/CD Pipeline

To tackle these challenges, Agoda decided to integrate AI into its development workflow. The goal was to reduce manual review time, accelerate MR approvals, and empower developers with self-service tools for performance tuning. Pichamon Rungarun, a staff software engineer at Agoda, explained the motivation behind this initiative:

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 team engineered an automated step within their CI/CD process. This step feeds critical information to ChatGPT, including the SQL code of the stored procedure, the relevant table structures and indexes, and a performance test report. The large language model (LLM) then analyzes this data and generates a set of optimization recommendations. These suggestions often include a completely rewritten version of the stored procedure and advice on creating or modifying indexes to improve efficiency.

SQL stored procedure optimization process with ChatGPT (Source: Agoda Engineering Blog)

From Suggestion to Decision

Once ChatGPT provides its optimized version of the stored procedure, the CI/CD pipeline automatically reruns the performance test. The results are then presented to both database and application developers in a clear, side-by-side comparison. This data-driven approach allows them to make an informed decision on whether to accept the AI-generated changes, significantly lightening the load on DB developers and streamlining the review process.

Example of SQL procedure optimization with ChatGPT (Source: Agoda Engineering Blog)

Challenges and the Road Ahead

While the initial results are promising, the team at Agoda is actively working to address some limitations and expand the tool's capabilities. A key focus is developing an automated logic validation system. This will ensure that even heavily revised stored procedures recommended by the LLM maintain the exact same business logic as the original code.

Looking forward, Agoda plans to make this GPT-based optimization tool available outside of the CI/CD pipeline, allowing developers to get performance tuning support without needing to open a merge request. The team is also working on ways to help DB developers fine-tune the prompts sent to ChatGPT to further improve the quality of its suggestions. Finally, there are plans to apply this automated optimization process to existing stored procedures in production, specifically targeting those with the highest CPU utilization to reduce resource consumption and lower infrastructure costs.