Agoda started utilizing ChatGPT to optimize SQL stored procedures (SP) as part of their CI/CD process. After introducing the automated LLM-assisted step, the company observed shortened stored procedure optimization times, which lightened the load on DB developers. Agora works on making ChatGPT more accessible for SP optimization outside of the CI/CD pipeline.
Agoda’s DB developers have been spending approximately 366 person-days on SP optimization, of which 320 person-days were dedicated to analyzing SP changes that resulted in performance test failures, reported by the CI/CD pipeline. The company wanted to reduce the merge request (MR) approval times (90th percentile of 4.1 hours).
Pichamon Rungarun, staff software engineer at Agoda, outlines the goals behind using ChatGPT:
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.
Inefficient SPs and SQL in general impacted performance, cost, and scalability for the company, potentially resulting in slow response times and frustrated users, higher resource utilization, and scalability bottlenecks. Yet, application developers often encounter challenges when working on database queries, including inefficient joins and subqueries, excessive query nesting, missing or poorly designed indexes, and a lack of visibility into query execution plans.
SQL stored procedure optimization process with ChatGPT (Source: Agoda Engineering Blog)
The team created an automated step that generates SP optimization recommendations using ChatGPT by feeding the SQL code of the stored procedure, table structures, and indexes, along with a performance test report, to the LLM. As a result, GPT suggests an optimized version of the SP by rewriting queries and recommends creating or modifying indexes.
Based on ChatGPT’s optimized version of the SP, the CI/CD pipeline reruns the performance test and provides a side-by-side comparison to DB and application developers, helping them make an informed decision.
Example of SQL procedure optimization with ChatGPT (Source: Agoda Engineering Blog)
Despite the initial success in leveraging ChatGPT, developers are working on improvements to address some limitations, including automated logic validation to ensure heavily revised SPs implement identical logic, despite substantial changes recommended by the LLM.
The team is working on making the functionality available outside of the CI/CD pipeline, so that developers can leverage GPT-based support without needing to open a merge request. Additionally, engineers are seeking to assist DB developers in fine-tuning GPT prompts to enhance the quality of GPT suggestions. Lastly, the team plans to apply SP optimization to production SPs that are responsible for the highest CPU utilization with the hope of reducing resource utilization and lowering infrastructure costs.
