A Comparison of Prompting Strategies for Text-to-SQL Generation on the Spider Benchmark

Text-to-SQL is the task of turning a question written in plain English into a SQL querythat returns the correct answer from a database. Large language models can perform this taskwithout any training, but the way the model is prompted can change how well it does. Thisreport compares three common prompting strategies, namely zero-shot, few-shot, and chain-of-thought, on the Spider benchmark using a small and low-cost Gemini model. All three strategiesare evaluated on the same set of 150 development questions so that the comparison is fair. Wemeasure execution accuracy, which checks whether the generated query returns the same resultas the reference query, and exact match, which checks whether the query text is the same afternormalization. Chain-of-thought prompting gives the best overall execution accuracy at 73.3percent, compared with 70.7 percent for zero-shot and 69.3 percent for few-shot. The benefit ofchain-of-thought is largest on the most difficult questions, where it reaches 69.2 percent against50.0 percent for zero-shot. Few-shot prompting performs slightly below the zero-shot baseline,which we attribute to its fixed example questions not matching the many different databaseschemas in the test set.