Zero-Shot, Few-Shot, or Chain-of-Thought? An Empirical Study of Prompting Strategies for LLM-Guided SQL Query Optimization
Resumo
Query optimization remains a central problem in database systems, and large language models (LLMs) have shown promise as advisors on code and SQL tasks. A practical question is open: when an LLM is given rich DBMS context, how much does the prompting strategy affect whether the model produces a semantically valid optimization, and whether that optimization is worth applying? We address this question through a controlled empirical study. We evaluate three prompting strategies – zero-shot, few-shot, and chain-of-thought (CoT) – on three different LLMs: Llama 3.3, GPT-4.1-mini, and the reasoning-tuned DeepSeek-R1, across the 22 TPC-H queries on PostgreSQL (198 trials in total). Each prompt is built from the same DBMS context (execution plans, schema, indexes, workload history, slow-query logs, tuning guides, and curated examples) so that the strategy is the only independent variable. We find four results. First, Llama 3.3 is strikingly robust in the zero-shot setting, producing a valid optimization in 95.5% of trials. Second, GPT-4.1-mini requires few-shot prompting to reach the same level, rising from 36.4% to 95.5% validity. Third, chain-of-thought roughly halves validity for both general-purpose models, and the failure is in the content, not in the output format: across the three models, 91% of CoT failures are well-formed JSON with a rewrite or index that the semantic-equivalence check rejects. Fourth, the reasoning-tuned DeepSeek-R1 behaves differently: its validity is strategy-stable (68%–86%) rather than collapsing, and its median speedup on valid trials is the highest of any model-strategy pair; its zero-shot failures are predominantly incorrect query rewrites, consistent with a pattern we call reasoning amplification, where, without worked examples, reasoning-tuned models drift toward textbook-looking optimizations that are DBMS-specific wrong. Strategy choice dominates model choice for validity; few-shot is the safe default, and examples act as necessary anchors for reasoning-tuned models.
Referências
Tom B. Brown, Benjamin Mann, Nick Ryder, Melanie Subbiah, Jared Kaplan, Prafulla Dhariwal, Arvind Neelakantan, Pranav Shyam, Girish Sastry, Amanda Askell, Sandhini Agarwal, Ariel Herbert-Voss, Gretchen Krueger, Tom Henighan, Rewon Child, Aditya Ramesh, Daniel M. Ziegler, Jeffrey Wu, Clemens Winter, Christopher Hesse, Mark Chen, Eric Sigler, Mateusz Litwin, Scott Gray, Benjamin Chess, Jack Clark, Christopher Berner, Sam McCandlish, Alec Radford, Ilya Sutskever, and Dario Amodei. 2020. Language Models are Few-Shot Learners. In Advances in Neural Information Processing Systems (NeurIPS). arXiv:2005.14165
Gao Cong, Jingyi Yang, and Yue Zhao. 2024. Machine Learning for Databases: Foundations, Paradigms, and Open Problems. Proceedings of the ACM on Management of Data (SIGMOD) (2024). DOI: 10.1145/3626246.3654686
Carlo Curino, Djellel Eddine Difallah, Andrew Pavlo, and Philippe Cudré-Mauroux. 2012. Benchmarking OLTP/Web Databases in the Cloud: The OLTP-Bench Framework. In Proceedings of CloudDB. 17–20.
DeepSeek-AI. 2025. DeepSeek-R1: Incentivizing Reasoning Capability in LLMs via Reinforcement Learning. [link]. arXiv:2501.12948
Djellel Eddine Difallah, Andrew Pavlo, Carlo Curino, and Philippe Cudré-Mauroux. 2013. OLTP-Bench: An Extensible Testbed for Benchmarking Relational Databases. Proceedings of the VLDB Endowment 7, 4 (2013), 277–288.
Jianling Gao, Nan Zhao, Ning Wang, Shuang Hao, and Haoyan Wu. 2022. Automatic Index Selection with Learned Cost Estimator. Information Sciences (2022).
Victor Giannakouris and Immanuel Trummer. 2025. Demonstrating 𝜆-Tune: Exploiting Large Language Models for Workload-Adaptive Database System Tuning. In Proceedings of the ACM on Management of Data (SIGMOD). ACM. DOI: 10.1145/3626246.3654751
Xiuqi Huang, Xiaofeng Gao, and Guihai Chen. 2024. DITune: A Reinforcement Learning Based Framework for Automated Database Index Selection. In Proceedings of the IEEE International Conference (ICDE). IEEE.
Takeshi Kojima, Shixiang Shane Gu, Machel Reid, Yutaka Matsuo, and Yusuke Iwasawa. 2022. Large Language Models are Zero-Shot Reasoners. In Advances in Neural Information Processing Systems (NeurIPS). arXiv:2205.11916
Jiale Lao, Yibo Wang, Yufei Li, Jianping Wang, Yunjia Zhang, Zhiyuan Cheng, Wanghu Chen, Mingjie Tang, and Jianguo Wang. 2025. GPTuner: An LLM-based Database Tuning System. Proceedings of the ACM on Management of Data (SIGMOD) (2025). DOI: 10.1145/3733620.3733641
Nhat Le, Quan Ninh, Tung Le, and Huy Tien Nguyen. 2025. Distilling Large Language Models for Structured Query Language Generation. Computers and Electrical Engineering (2025).
Meta AI. 2024. Llama 3.3 Model Card. [link].
OpenAI. 2025. GPT-4.1-mini. [link].
OpenRouter. [n. d.]. OpenRouter – Unified LLM API. [link].
Tobias Schmidt, Viktor Leis, Peter Boncz, and Thomas Neumann. 2025. SQLStorm: Taking Database Benchmarking into the LLM Era. Proceedings of the VLDB Endowment 18, 11 (2025), 4144–4157.
Transaction Processing Performance Council. [n. d.]. TPC-H Benchmark Specification. [link].
Dimitris Tsesmelis and Alkis Simitsis. 2022. Database Optimizers in the Era of Learning. In Proceedings of ICDE. IEEE.
Xiuwen Zheng, Arun Kumar, and Amarnath Gupta. 2024. Generating Cross-Model Analytics Workloads Using LLMs. In Proceedings of DaMoN. ACM. DOI: 10.1145/3627673.3679932
