PaperlayerView exact revision on arXiv

Source-linked AI summary

Text-to-SQL Empowered by Large Language Models: A Benchmark Evaluation

Dawei Gao, Haibin Wang, Yaliang Li, Xiuyu Sun, Yichen Qian, Bolin Ding, Jingren Zhou

arXiv:2308.15363v4cs.DBcs.CLcs.LG

TL;DR

LLM-based Text-to-SQL lacks a systematic benchmark covering prompt effectiveness, efficiency, and open-source models. The paper evaluates prompt strategies and supervised fine-tuning, proposes DAIL-SQL, and reports 86.6% Spider execution accuracy while identifying fine-tuning and efficiency trade-offs.

  • Problem

    LLM-based Text-to-SQL lacks systematic evidence on prompt effectiveness and efficiency, open-source LLM feasibility, and cost-effective prompt engineering.

  • Method

    The paper benchmarks question representations, example selection and organization, open-source LLMs, supervised fine-tuning, and token efficiency, then integrates findings in DAIL-SQL.

  • Results

    86.6% execution accuracy on Spider makes DAIL-SQL the first-place solution, surpassing the previous best by 1.3% with much less token cost.

  • Takeaways & Limitations

    The study highlights open-source LLM potential, the importance of representation and training data, and a post-fine-tuning decrease in in-context learning capability.

  • Takeaways & Limitations

    The paper tests only two rule implications, fine-tunes only on Spider training data, and evaluates databases that may be too small for mass-table settings.

Abstract

from arXiv · show

Large language models (LLMs) have emerged as a new paradigm for Text-to-SQL task. However, the absence of a systematical benchmark inhibits the development of designing effective, efficient and economic LLM-based Text-to-SQL solutions. To address this challenge, in this paper, we first conduct a systematical and extensive comparison over existing prompt engineering methods, including question representation, example selection and example organization, and with these experimental results, we elaborate their pros and cons. Based on these findings, we propose a new integrated solution, named DAIL-SQL, which refreshes the Spider leaderboard with 86.6% execution accuracy and sets a new bar. To explore the potential of open-source LLM, we investigate them in various scenarios, and further enhance their performance with supervised fine-tuning. Our explorations highlight open-source LLMs' potential in Text-to-SQL, as well as the advantages and disadvantages of the supervised fine-tuning. Additionally, towards an efficient and economic LLM-based Text-to-SQL solution, we emphasize the token efficiency in prompt engineering and compare the prior studies under this metric. We hope that our work provides a deeper understanding of Text-to-SQL with LLMs, and inspires further investigations and broad applications.

1 INTRODUCTION

The paper identifies missing systematic evidence on effective, efficient, and open-source LLM-based Text-to-SQL, then benchmarks prompt strategies and proposes DAIL-SQL.

  • Research gaps: Existing LLM-based Text-to-SQL research lacks a systematic study of prompt engineering, particularly question representation and detailed instruction choices.Similar representations can produce significant performance gaps because their task instructions differ.
  • Research gaps: Open-source LLMs remain underexplored for Text-to-SQL, especially regarding in-context learning and supervised fine-tuning.The paper frames supervised fine-tuning as a way to enhance their task performance.
  • Research gaps: Prompt efficiency is an open challenge because API calls are expensive, time-consuming, and rate-limited, particularly for prompts containing multiple examples.Prior work observed an inverted-U relationship between prompt length and execution accuracy but left efficient prompt engineering unresolved.
  • Study design: The benchmark systematically evaluates prompt effectiveness and efficiency across question representation, example selection, example organization, and open-source LLM feasibility.The study compares zero-shot representations, few-shot example strategies, and open-source models with in-context learning and supervised fine-tuning.
  • Main result: 86.6% execution accuracy is achieved by DAIL-SQL on Spider, surpassing the previous best state-of-the-art solution by 1.3% with much less token cost.DAIL-SQL encodes structure as SQL statements, selects examples by skeleton similarity, and removes cross-domain knowledge from examples.

2 PRELIMINARY

The paper situates Text-to-SQL as natural-language translation into SQL and frames question representation, in-context learning, and supervised fine-tuning as central LLM-based approaches.

  • Task definition: Text-to-SQL translates natural-language questions into SQL queries over relational databases.The task connects natural-language processing with database systems.
  • Task definition: Earlier Text-to-SQL methods used rules, query enumeration, or encoder-decoder sequence-to-sequence models, while newer approaches use LLMs.The related work spans database and natural language processing communities.
  • Prompting framework: Prompt engineering is divided into zero-shot and few-shot scenarios according to whether examples are included in the prompt.Zero-shot methods primarily address how to represent the question and relevant database schema.
  • Training paradigm: Supervised fine-tuning uses additional task-specific training data to make LLMs more suitable for downstream Text-to-SQL.The paper contrasts this training paradigm with in-context learning.
  • Study scope: Question representation, in-context learning, and supervised fine-tuning are treated as three essential knobs for LLM-based Text-to-SQL.The paper studies and discusses these three aspects systematically.

3 METHODOLOGY

The methodology formalizes question representation and in-context learning, compares prompt components and example strategies, and introduces DAIL-SQL to preserve useful question-to-SQL mappings efficiently.

  • 3.1 Question Representation: Question representation combines a natural-language question with useful database-schema information and may include instructions, rule implications, or foreign keys.The study compares five representative representations in zero-shot Text-to-SQL.
  • 3.1 Question Representation: Table 1 compares existing question representations and reported zero-shot execution accuracy, including instruction, rule-implication, and foreign-key components.These components respectively describe the task, guide SQL generation, and provide foreign-key information.
  • 3.1 Question Representation: The representations include basic, text, OpenAI demonstration, code, and Alpaca-style prompts, differing in instructions and schema presentation.Code representation presents database structure directly in SQL syntax and can include column types and key information.
  • 3.1 Question Representation: A fair systematic comparison is needed because prior representations use different LLMs, frameworks, and prompt components, obscuring individual component effects.The paper specifically highlights uncertainty about foreign-key information and rule implications.
  • 3.2 In-Context Learning: In-context learning selects helpful question-SQL-database triples and organizes them into a prompt for generating SQL on a target question and database.The paper focuses on cross-domain settings where the target database is absent from the example databases.
  • 3.3 DAIL-SQL: DAIL-SQL selects examples using both question and query information and organizes them to retain question-to-SQL mappings.It uses code representation and reaches 86.2% execution accuracy before the separately discussed self-consistency extension.
  • 3.3 DAIL-SQL: Self-consistency raises DAIL-SQL from 86.2% to 86.6% execution accuracy but is very time-consuming and costs many times more than the original solution.The paper therefore focuses on the original DAIL-SQL configuration.

4 EXPERIMENT

The experiments benchmark question representations, example strategies, and open-source LLMs across zero-shot, few-shot, and supervised fine-tuning settings. Results identify effective prompt components, model-dependent behavior, and strong gains from DAIL-SQL and fine-tuning.

  • Question Representation: 75.5% execution accuracy is achieved by OD P with GPT-3.5-TURBO, while GPT-4 prefers the simpler BS P representation.OD P performs consistently across four LLMs, whereas AS P performs poorly with several models.
  • Question Representation: 0.6%–2.9% execution-accuracy gains generally result from adding foreign-key information to OpenAI LLM prompts, with exceptions for two combinations.For Vicuna-33B, the effect is unstable: BS P improves by 5.0%, while OD P and AS P are adversely affected.
  • Question Representation: More than 6% exact-set-match and 3% execution-accuracy improvements result from adding the “with no explanation” rule across representations and LLMs.Removing this rule from OD P causes execution-accuracy drops of 1.3%–2.4%.
  • Question Representation: 51.5% exact-set-match accuracy and 78.4% execution accuracy are achieved by OD P with foreign keys and GPT-3.5-TURBO.The study identifies this as the most effective and economic combination in its evaluation.
  • Example Selection and Organization: 82.4% execution accuracy is achieved by DAIL-SQL with GPT-4 in the 5-shot setting, while question and query similarity generally correspond to higher accuracy.The highest reported results are 83.5% on Spider-dev and 76.0% on Spider-Realistic using DAIL-SQL with GPT-4.
  • Example Selection and Organization: 72.3% to 83.5% execution accuracy is the improvement for GPT-4 on Spider-dev as contextual examples increase under DAIL Organization; Spider-Realistic rises from 66.5% to 76.0%.Adding examples can instead reduce accuracy for GPT-3.5-TURBO and TEXT-DAVINCI-003, while Vicuna-33B improves consistently under DAIL Organization.
  • Open-Source LLMs: 68.5% execution accuracy is achieved by CR P among open-source LLMs in zero-shot Spider-dev evaluation, with full database knowledge compensating for model limitations.CodeLLaMA-34B reaches only 40.3% with the natural-language TR P representation.
  • Open-Source LLMs: 9.1% to 34.0% and 15.3% to 36.6% are the Spider-dev average execution-match progressions for LLaMA and Vicuna as model scale increases.LLaMA-2-CHAT-70B reaches 39.6% average performance.

5 DISCUSSION

The discussion distills empirical guidelines for prompt design, open-source models, and supervised fine-tuning, while identifying several limitations and open questions.

  • Code Representation Prompt and OpenAI Demostration Prompt are recommended, while foreign keys and rule implications can improve question representation.
  • Question and SQL-query similarities together indicate effective example-selection strategies.
  • Powerful LLMs such as GPT-4 can use question–SQL pairs effectively and efficiently, whereas weaker models are advised to use full-information examples.
  • For open-source LLMs, model scale helps Text-to-SQL, but training-corpus quality is more important; supervised fine-tuning is necessary and promising.
  • The paper tests only two rule implications, fine-tunes on Spider training data alone, and evaluates databases that may be too small for mass-table settings.
  • Current evaluation prioritizes correctness over efficiency, leaving efficient SQL generation among correct alternatives unexplored.

6 CONCLUSIONS

The conclusion presents DAIL-SQL as a systematic approach combining prompt engineering and supervised fine-tuning to study effectiveness, open-source models, and efficiency in Text-to-SQL.

  • The paper systematically studies LLM-based Text-to-SQL through prompt engineering and supervised fine-tuning.
  • DAIL-SQL addresses neglected question–query mappings and the trade-off between example quality and quantity through new prompt engineering.
  • DAIL-SQL refreshes the Spider leaderboard with 86.6% execution accuracy and ranks first.
  • The study finds open-source LLM potential, emphasizes training corpus and model scaling, and reports diminished in-context learning after fine-tuning.
  • DAIL-SQL is reported as more efficient, highlighting token efficiency as important for prompt engineering.

A.2 Implementation Details

DAIL-SQL masks database-specific information, ranks candidates using question and query similarities, and uses selected examples to construct a prompt for SQL generation.

  • Question similarity is computed after schema-linking and masking table, column, and value information from questions.
  • Query similarity compares database-independent query skeletons using Jaccard similarity against a preliminary predicted query.
  • The submission configuration uses threshold 0.85, GPT-4, specified prompt components, and self-consistency voting over five generated queries.

B.1 Detailed Performance of Different Question Representations

This section reports zero-shot evaluations of question representations, foreign-key information, rule implications, and their effects across model settings. It also compares these representation choices with one-shot contextual examples.

  • Foreign-key information: Foreign-key information is evaluated through an ablation against settings without foreign keys.The comparison is reported in Table 6.
  • Rule implication: Rule implications are tested with and without the instruction “with no explanation,” alongside the opposite instruction setting.A separate evaluation examines the “Let’s think step by step” rule.
  • Contextual examples: One-shot contextual examples improve exact-set-match accuracy consistently across LLMs, while execution-accuracy gains vary by model and representation.The passage reports benefits for GPT-4 and TEXT-DAVINCI-003, but for GPT-3.5-TURBO only TR_P and CR_P improve in execution accuracy.

C.1 One-Shot Evaluation on Different Question Representation

The one-shot evaluation compares question representations and example organizations on Spider development and realistic settings. Contextual examples improve exact-set-match accuracy consistently, but execution-accuracy effects depend on the LLM and representation.

  • Question representation: One-shot evaluation compares different question representations against zero-shot results on Spider-dev.The comparison is documented in Figure 8 and Table 9.
  • Example organization: Few-shot example organization is evaluated on Spider development and Spider-Realistic datasets.Tables 10 and 11 report the detailed comparisons.
  • LLM-based methods: The study focuses on a systematic evaluation of LLM-based Text-to-SQL methods as their performance becomes more competitive than PLM-based and rule-based approaches.Table 12 compares reported execution accuracy across these approach families.

D.3 Experiments in Few-shot Scenario for Different Model Sizes and Training Corpus of Open-source LLMs

The few-shot experiments compare open-source LLMs across model sizes, datasets, and training conditions. Results show substantial variation among models and evaluate supervised fine-tuning under different representations.

  • Few-shot open-source LLMs: 59.0% execution accuracy is achieved by LLaMA-2-CHAT-70B in few-shot evaluation, while Falcon-40B reaches 19.1%.CodeLLaMA-34B outperforms LLaMA-2-CHAT-70B by 12.4% in the few-shot scenario.
  • Model size: CodeLLaMA-34B outperforms LLaMA-2-CHAT-70B by 12.4% in the few-shot scenario.The comparison is presented as further evidence that larger parameter counts can be beneficial.
  • Evaluation settings: Open-source LLM evaluations cover zero-shot Spider-Realistic and few-shot Spider-dev settings.Tables 13 and 14 provide these evaluations.
  • Training setup: Fine-tuning uses 8,659 Spider training samples and eight 64G A100 GPUs.The reported setup uses global batch size 256, cosine learning-rate scheduling, and a searched learning rate range of [1e-6, 1e-4].
  • DAIL-SQL evaluation: DAIL-SQL performance across open-source LLMs is summarized separately for comparison.Table 15 reports the corresponding model performances.
  • Supervised fine-tuning: Supervised fine-tuning is evaluated on Spider and Spider-Realistic with different representations and LLMs.The numerical results are reported in Table 16.

E.1 Financial Efficiency

The financial-efficiency analysis compares API costs for GPT-4 and GPT-3.5-TURBO with local Vicuna-33B inference costs and time. It also places DAIL-SQL's performance in the Spider leaderboard context.

  • API costs: $323.2 and $12.0 are the estimated total API costs for GPT-4 and GPT-3.5-TURBO, respectively.The estimates use API prices reported on November 8, 2023.
  • Local inference: Vicuna-33B inference time depends substantially on the number of output tokens, not only on local inference cost.The OpenAI Demonstration Prompt tends to elicit longer explanations accompanying SQL queries.
  • Inference efficiency: The “with no explanation” implication rule both improves performance and saves Vicuna-33B inference time.Its time-saving effect comes from suppressing explanations.
  • Leaderboard performance: 86.6% execution accuracy places DAIL-SQL with self-consistency first on the Spider leaderboard reported on September 19, 2023.Without self-consistency, the solution is reported at 86.2% execution accuracy.

F.2 BIRD Leaderboard

On the BIRD leaderboard, DAIL-SQL outperforms the previous state-of-the-art DIN-SQL on both development and test execution accuracy. However, BIRD comparisons are complicated by the importance of incorporating domain knowledge and the use of supervised fine-tuning by top-ranked solutions.

  • 4.04% on BIRD dev and 1.51% on BIRD test: DAIL-SQL outperforms DIN-SQL in execution accuracy.
  • BIRD requires effectively leveraging the benchmark’s specific domain knowledge.
  • Top-1 and top-2 BIRD solutions use supervised fine-tuning, whereas DAIL-SQL does not.
Loading 2308.15363v4…