Text-to-SQL systems allow users who are not technical experts to convert natural language queries to runnable SQL queries, although their accuracy tends to decrease with the complexity of the database schema. When using complex schemas (e.g. with many-to-many joins, column names that are ambiguous, multi-table dependencies), the problem of complex schemas is serious and standard prompting strategies may prove ineffective. This paper compares four prompting strategies, such as constraint-based prompts, schema summarisation prompts, few-shot example prompts, and stepwise reasoning prompts, at different levels of schema complexity. Benchmark schemas were built with more and more tables and intra-table connections with each other, and the accuracy of their performance was determined using exact match accuracy, execution accuracy and the join prediction accuracy. Tables and figures demonstrate timely structures, measurement criteria and error distributions based on the level of complexity. Findings have shown that stepwise reasoning and schema summary prompts are more effective than other schema summary strategies with more than 7 tables and multiple many-to-many joins. Prompts based on constraints maintain SQL constraints successfully but are weak in dealing with the ambiguous names of columns, whereas few-shot examples are subject to prompt length and token constraints. According to the error analysis, complex joins and column ambiguity are the primary causes of wrong SQL generation. The findings can be used in practice by developing well-trained prompt patterns in LLM-based Text-to-SQL systems and offer both a systematic and reproducible evaluation platform to research and practice.
Sai Lalitesh Pothukuchi (Thu,) studied this question.