GPT Integration for SQL Stored Procedure Optimization in CI/CD Pipeline

agoda · E-commerce · 2024 · source Agentic: yes · promotional bias: low · source-backed · extracted with prompt 0.5.6 (schema v2)

Generated from extraction.json — do not edit by hand. Every claim cites verbatim source evidence; stated means the quote directly asserts the claim, implied means it supports it indirectly.

Problem

Domain: database performance optimization in CI/CD

Agoda needed to reduce the manual database-developer effort required to analyze failed stored-procedure performance tests, improve SQL quality, and speed merge-request approvals without letting GPT make unreviewed logic changes.

Constraints:

• every new stored procedure triggers a merge request and performance tests in CI/CD
• failed performance tests require dbdevs to manually analyze bottlenecks and suggest improvements
• dbdevs spend about 366 man-days annually on stored procedure optimization
• 320 of those man-days are spent on performance test failures
• 90th percentile merge-request approval times reached 4.1 hours
• recommendations must preserve stored-procedure logic while changing SQL and indexes
• developers and dbdevs need side-by-side performance comparison before applying GPT suggestions

Architecture

Agoda integrated GPT into its stored-procedure CI/CD workflow. Each new SP merge request runs performance tests; if optimization help is needed, the system prepares SP code, table structures, indexes, and the performance test report as context for GPT. GPT analyzes bottlenecks, rewrites queries, and suggests indexes. The CI/CD workflow posts a GitLab MR summary with original-vs-optimized performance comparison and a click-to-apply option, while developers and dbdevs review GPT's optimized SP and index suggestions before applying changes.

Diagram confidence: reconstructed (reconstructed from prose).

flowchart LR
  developer["Developer"] --> gitlab_merge_request["GitLab merge request"]
  gitlab_merge_request --> ci_cd_performance_tests["CI/CD performance tests"]
  ci_cd_performance_tests --> context_preparation["Context preparation"]
  context_preparation --> gpt_optimizer["GPT optimizer"]
  gpt_optimizer --> optimized_sp_and_index_suggestions["Optimized SP and index suggestions"]
  optimized_sp_and_index_suggestions --> mr_optimization_comment["MR optimization comment"]
  mr_optimization_comment --> gitlab_merge_request
  gitlab_merge_request --> developer
  gitlab_merge_request --> database_developer["Database developer"]
  logic_verification_framework["Logic verification framework"] --> gpt_optimizer

Components

Data flow

A developer opens a merge request for a stored procedure, triggering performance tests in CI/CD. The system packages SP code, table structures/indexes, and the performance report for GPT. GPT returns an optimized SP and suggested indexes, plus analysis of bottlenecks. The workflow adds a GitLab MR comment with a side-by-side performance comparison and a clickable option to apply GPT's commit, leaving developers and dbdevs to evaluate and act on the recommendation.

Why this architecture

Agoda placed GPT directly at the point where SQL performance failures already surface: the CI/CD merge-request workflow. Supplying code, schema/indexes, and performance reports gives GPT actionable context, while side-by-side comparisons and click-to-apply review keep humans in control before optimized SQL or index changes enter the codebase.

Models used: GPT

Scale

• Agoda dbdevs spend about 366 man-days annually on stored procedure optimization.
  “Each year, our dbdevs spend approximately 366 man-days on SP optimization”article, stated
• About 320 of those man-days are spent on performance test failures.
  “320 of those on performance test failures alone.”article, stated
• 90th percentile merge-request approval times reached 4.1 hours.
  “MR approval times continue to grow, with 90th percentile cases reaching 4.1 hours.”article, stated
• The launched GPT optimization workflow reached about 25% accuracy.
  “Since the launch of GPT for SP optimization, accuracy has reached around 25%”article, stated

Alternatives considered (roads not taken)

Keep performance-test failure analysis fully manual

Rejected because: Manual analysis consumed hundreds of dbdev man-days and slowed merge-request approvals.

Ask GPT to optimize SQL from stored-procedure code alone

Rejected because: Agoda supplies schema, index, and performance-report context so GPT can identify bottlenecks and produce actionable recommendations.

Auto-apply GPT rewrites without review

Rejected because: The workflow presents performance comparison and click-to-apply controls so developers and dbdevs can decide the next step.

Trust GPT rewrites without semantic equivalence checks

Rejected because: Some GPT rewrites change entire logic, so Agoda is building automated logic verification to compare original and optimized outputs.

Patterns

tool-use-orchestration (CI/CD optimization workflow)

The workflow coordinates CI/CD tests, performance reports, schema/index context, GPT analysis, merge-request comments, and click-to-apply commits.

structured-output-contract (SQL recommendation format)

GPT produces two actionable outputs for each stored procedure: an optimized SP and suggested indexes, with performance comparison shown in the MR.

human-in-the-loop-gate (safe code change)

Developers and dbdevs review GPT's suggestions and performance comparisons in the MR before choosing whether to apply generated SQL/index changes.

eval-driven-development (performance and equivalence checks)

Performance tests trigger the workflow, side-by-side comparisons validate the optimization effect, and Agoda is building logic verification tests to ensure behavior remains identical.

observability-tracing (performance diagnostics)

The GPT prompt includes performance test reports that expose execution times, bottlenecks, high resource use, full scans, and inefficient joins to guide recommendations.

Tradeoffs

Put GPT inside the merge-request workflow.

Chose CI/CD-integrated GPT recommendations and MR comments over Separate optimization tooling outside the developer workflow.

Rationale: Embedding suggestions in the MR makes performance context and click-to-apply changes available where developers and dbdevs already review SPs.

Expose self-service optimization while keeping review.

Chose Click-to-apply GPT suggestions over Fully manual dbdev optimization or fully automatic code modification.

Rationale: The feature empowers developers and reduces dbdev workload without removing the review step needed for correctness.

Use performance comparison as the MR decision artifact.

Chose Side-by-side original-versus-optimized performance comparison over A raw GPT recommendation without measured impact.

Rationale: The comparison lets reviewers judge whether the generated SQL/index changes produce enough performance improvement to accept.

Expand beyond MR-triggered optimization.

Chose A future assistant integrated directly with Agoda's database environment over Requiring developers to open a merge request before accessing GPT optimization.

Rationale: Direct database-environment access would make GPT optimization available earlier and more conveniently, but it is described as a future expansion.

Gotchas (what bit them)

Manual SQL optimization becomes a hidden approval bottleneck at scale.

Root cause: Each failed performance test forces dbdevs to inspect stored procedures, execution behavior, and bottlenecks manually.

Resolution: Feed performance-test context to GPT and surface recommendations directly in the MR to reduce manual review time.

GPT needs execution and schema context to produce useful SQL recommendations.

Root cause: Stored-procedure performance depends on table structures, indexes, execution times, bottlenecks, and resource usage, not just SQL text.

Resolution: Prepare SP code, table structures/indexes, and performance test reports as input context.

An optimization can silently change stored-procedure logic.

Root cause: For complex stored procedures, GPT may rewrite the entire logic rather than making a local optimization.

Resolution: Develop automated logic verification to compare original and optimized outputs across test cases.

Early accuracy leaves the system as an assistant, not an autonomous optimizer.

Root cause: The launch reports about 25% accuracy, so GPT is useful but not reliable enough to replace human and test review.

Resolution: Keep recommendations reviewable in the MR and continue improving prompts, test cases, and real-world production evaluation.

Evaluation

Agoda evaluates the workflow through CI/CD performance tests, original-versus-optimized performance comparisons in MRs, reported accuracy after launch, and planned logic-verification and production high-CPU SP evaluation.

Metrics: approximately 366 man-days per year spent on SP optimization · 320 man-days spent on performance test failures · 90th percentile MR approval time of 4.1 hours · around 25% accuracy after launch · side-by-side performance comparison between original and GPT-optimized SP

Outcomes

• GPT was integrated into Agoda's stored-procedure CI/CD workflow.
• Developers and dbdevs receive GPT recommendations inside GitLab MRs.
• The workflow produces optimized stored procedures, suggested indexes, and original-versus-optimized performance comparisons.
• The launch reached around 25% accuracy.
• Agoda reports reduced manual reviews, improved stored-procedure quality, and more self-service tuning.
• Agoda is building automated logic verification, a database-environment assistant, easier test-case creation, prompt fine-tuning, and production high-CPU SP evaluation.

The system targets a yearly workload of roughly 366 dbdev man-days spent on SP optimization, including 320 man-days on performance test failures, with 90th percentile MR approvals reaching 4.1 hours before/around the intervention.