Skip to main content Accessibility help
Hostname: page-component-55597f9d44-t4qhp Total loading time: 0.441 Render date: 2022-08-14T15:58:48.065Z Has data issue: true Feature Flags: { "shouldUseShareProductTool": true, "shouldUseHypothesis": true, "isUnsiloEnabled": true, "useRatesEcommerce": false, "useNewApi": true } hasContentIssue true

A SQL to C compiler in 500 lines of code

Published online by Cambridge University Press:  03 June 2019

Purdue University, West Lafayette, Indiana, USA (
University of Cambridge, Computer Laboratory, William Gates Building, 15 JJ Thomson Avenue, Cambridge CB3 0FD, UK (
Rights & Permissions[Opens in a new window]


HTML view is not available for this content. However, as you have access to this content, a full PDF is available via the ‘Save PDF’ action button.

We present the design and implementation of a SQL query processor that outperforms existing database systems and is written in just about 500 lines of Scala code – a convincing case study that high-level functional programming can handily beat C for systems-level programming where the last drop of performance matters. The key enabler is a shift in perspective toward generative programming. The core of the query engine is an interpreter for relational-algebra operations, written in Scala. Using the open-source lightweight modular staging framework, we turn this interpreter into a query compiler with very low effort. To do so, we capitalize on an old and widely known result from partial evaluation: the first Futamura projection, which states that a process that can specialize an interpreter to any given input program is equivalent to a compiler. In this context, we discuss lightweight modular staging programming patterns such as mixed-stage data structures (e.g., data records with static schema and dynamic field components) and techniques to generate low-level C code, including specialized data structures and data loading primitives.

Regular Paper
© Cambridge University Press 2019 


Ackermann, S., Jovanovic, V., Rompf, T. & Odersky, M. (2012) Jet: An embedded DSL for high performance big data processing. In International Workshop on End-to-end Management of Big Data (BigData 2012).Google Scholar
Amin, N. & Rompf, T. (2018) Collapsing towers of interpreters. In PACMPL, vol. 2. (POPL). ACM.Google Scholar
Armbrust, M., et al. (2015) Spark SQL: Relational data processing in spark. In SIGMOD. ACM.Google Scholar
Astrahan, M. M., Blasgen, M. W., Chamberlin, D. D., Eswaran, K. P., Gray, J., Griffiths, P. P. III, King, W. F., Lorie, R. A., McJones, P. R., Mehl, J. W., Putzolu, G. R., Traiger, I. L., Wade, B. W. & Watson, V. (1976) System R: relational approach to database management. ACM Trans. Database Syst. 1(2), 97137.CrossRefGoogle Scholar
Axelsson, E., Claessen, K., Sheeran, M., Svenningsson, J., Engdal, D. & Persson, A. (2011) The design and implementation of Feldspar: An embedded language for digital signal processing. In IFL’10. Springer.Google Scholar
Beckmann, O., Houghton, A., Mellor, M. R. & Kelly, P. H. J. (2003) Runtime code generation in C++ as a foundation for domain-specific optimisation. Domain-Specific Program Generation. Lecture Notes in Computer Science, vol. 3016. Springer.Google Scholar
Boncz, P., Grust, T., Van Keulen, M., Manegold, S., Rittinger, J. & Teubner, J. (2006) MonetDB/XQuery: A fast XQuery processor powered by a relational engine. In SIGMOD.Google Scholar
Bondorf, A. (1990) Self-applicable partial evaluation. Ph.D. thesis, DIKU, Department of Computer Science, University of Copenhagen.Google Scholar
Brown, K. J., Sujeeth, A. K., Lee, H. J., Rompf, T., Chafi, H., Odersky, M. & Olukotun, K. (2011) A heterogeneous parallel framework for domain-specific languages. In PACT.Google Scholar
Brown, K. J., Lee, H. J., Rompf, T., Sujeeth, A. K., De Sa, C., Aberger, C. & Olukotun, K. (2016) Have abstraction and eat performance, too: Optimized heterogeneous computing with parallel patterns. In CGO. ACM.Google Scholar
Calcagno, C., Taha, W., Huang, L. & Leroy, X. (2003) Implementing multi-stage languages using asts, gensym, and reflection. In GPCE. ACM.Google Scholar
Catanzaro, B., Garland, M. & Keutzer, K. (2011) Copperhead: Compiling an embedded data parallel language. In PPoPP. ACM.Google Scholar
Chamberlin, D. D., Astrahan, M.M., Blasgen, M. W., Gray, J. N., King, W. F., Lindsay, B. G., Lorie, R., Mehl, J. W., Price, T. G., Putzolu, F., Selinger, P. G., Schkolnick, M., Slutz, D. R., Traiger, I. L., Wade, B. W. & Yost, R. A. (1981) A history and evaluation of System R. Commun. ACM, 24(10).CrossRefGoogle Scholar
Chiba, T. & Onodera, T. (2015) Workload Characterization and Optimization of tpc-h Queries on Apache Spark. Technical Report RT0968.Google Scholar
Cohen, A., Donadio, S., Garzarán, M. J., Herrmann, C. A., Kiselyov, O. & Padua, D. A. (2006) In search of a program generator to implement generic transformations for high-performance computing. Sci. Comput. Program. 62(1). Elsevier.CrossRefGoogle Scholar
Consel, C. & Danvy, O. (1993) Tutorial notes on partial evaluation. In POPL. ACM.Google Scholar
Consel, C. & Khoo, S.-C. (1993) Parameterized partial evaluation. ACM Trans. Program. Lang. Syst. 15(3).Google Scholar
Crotty, A., Galakatos, A., Dursun, K., Kraska, T., Binnig, C., Çetintemel, U. & Zdonik, S. (2015) An architecture for compiling UDF-centric workflows. PVLDB 8(12). ACM.Google Scholar
DeVito, Z., Hegarty, J., Aiken, A., Hanrahan, P. & Vitek, J. (2013) Terra: A multi-stage language for high-performance computing. In PLDI. ACM.Google Scholar
DeVito, Z., Ritchie, D., Fisher, M., Aiken, A. & Hanrahan, P. (2014) First-class runtime generation of high-performance types using exotypes. In PLDI. ACM.Google Scholar
Diaconu, C., Freedman, C., Ismert, E., Larson, P.-A., Mittal, P., Stonecipher, R., Verma, N. & Zwilling, M. (2013) Hekaton: SQL Server’s memory-optimized OLTP engine. In SIGMOD. ACM.Google Scholar
Essertel, G.M., Tahboub, R. Y., Decker, J.M., Brown, K. J., Olukotun, K. & Rompf, T. (2018) Flare: Optimizing apache spark with native compilation for scale-up architectures and medium-size data. In OSDI. ACM.Google Scholar
Futamura, Y. (1971) Partial evaluation of computation process—An approach to a compiler-compiler. Trans. Inst. Electron. Commun. Eng. Jpn 54-C(8).Google Scholar
Gedik, B., Andrade, H., Wu, K.-L., Yu, P. S. & Doo, M. (2008) Spade: The system s declarative stream processing engine. In SIGMOD. ACM.Google Scholar
Graefe, G. (1994) Volcano - An extensible and parallel query evaluation system. IEEE Trans. Knowl. Data Eng. 6(1).CrossRefGoogle Scholar
Graefe, G. & McKenna, W. J. (1993) The Volcano optimizer generator: Extensibility and efficient search. In ICDE. ACM.Google Scholar
Grant, B., Mock, M., Philipose, M., Chambers, C. & Eggers, S. J. (2000) DyC: An expressive annotation-directed dynamic compiler for C. Theor. Comput. Sci. 248(1–2).CrossRefGoogle Scholar
Hatcliff, J. & Danvy, O. (1997) A computational formalization for partial evaluation. Math. Struct. Comput. Sci. 7(5). ACM.CrossRefGoogle Scholar
Isard, M., Budiu, M., Yu, Y., Birrell, A. & Fetterly, D. (2007) Dryad: Distributed data-parallel programs from sequential building blocks. In EuroSys. ACM.Google Scholar
Jones, N. D., Gomard, C. K. & Sestoft, P. (1993) Partial Evaluation and Automatic Program Generation. Prentice-Hall, Inc.Google Scholar
Jonnalagedda, M., Coppey, T., Stucki, S., Rompf, T. & Odersky, M. (2014) Staged parser combinators for efficient data processing. In OOPSLA. ACM.Google Scholar
Jørring, U. & Scherlis, W. L. (1986) Compilers and staging transformations. In POPL. ACM.Google Scholar
Kiselyov, O., Swadi, K. N. & Taha, W. (2004) A methodology for generating verified combinatorial circuits. In EMSOFT. ACM.Google Scholar
Klimov, A. V. (2009) A Java supercompiler and its application to verification of cache-coherence protocols. In Ershov Memorial Conference. Springer.Google Scholar
Klonatos, Y., Koch, C., Rompf, T. & Chafi, H. (2014) Building efficient query engines in a high-level language. VLDB 7(10). ACM.Google Scholar
Kornacker, M., Behm, A., Bittorf, V., Bobrovytsky, T., Ching, C., Choi, A., Erickson, J., Grund, M., Hecht, D., Jacobs, M., Joshi, I., Kuff, L., Kumar, D., Leblang, A., Li, N., Pandis, I., Robinson, H., Rorke, D., Rus, S., Russell, J., Tsirogiannis, D., Wanderman-Milne, S. & Yoder, M. (2015) Impala: A modern, open-source SQL engine for Hadoop. In CIDR.Google Scholar
Krikellas, K., Viglas, S. D. & Cintra, M. (2010) Generating code for holistic query evaluation. In ICDE. IEEE.Google Scholar
Lawall, J. L. & Thiemann, P. (1997) Sound specialization in the presence of computational effects. TACS. Lecture Notes in Computer Science, vol. 1281. Springer.Google Scholar
Lee, H. J., Brown, K. J., Sujeeth, A. K., Chafi, H., Rompf, T., Odersky, M. & Olukotun, K. (2011) Implementing domain-specific languages for heterogeneous parallel computing. IEEE Micro 31(5).CrossRefGoogle Scholar
Mainland, G. & Morrisett, G. (2010) Nikola: Embedding compiled GPU functions in Haskell. In Haskell Symposium.Google Scholar
McDonell, T. L., Chakravarty, M. M. T., Keller, G. & Lippmeier, B. (2013) Optimising purely functional GPU programs. In ICFP. ACM.Google Scholar
Mehta, M. & DeWitt, D. J. (1995) Managing intra-operator parallelism in parallel database systems. In SIGMOD. ACM.Google Scholar
Mogensen, T. A. E. (1988) Partially static structures in a self-applicable partial evaluator. In Partial Evaluation and Mixed Computation, Bjørner, D., Ershov, A. P., & Jones, N. D. (eds).Google Scholar
Moldovan, D., Decker, J. M., Wang, F., Johnson, A. A., Lee, B. K., Nado, Z., S., D., Rompf, T. & Wiltschko, A. B. (2019) AutoGraph: Imperative-style coding with graph-based performance. In SysML. Springer.Google Scholar
Neumann, T. (2011) Efficiently compiling efficient query plans for modern hardware. PVLDB 4(9).Google Scholar
Odersky, M. & Rompf, T. (2014) Unifying functional and object-oriented programming with scala. Commun. ACM 57(4).CrossRefGoogle Scholar
Ofenbeck, G., Rompf, T. & Püschel, M. (2017) Staging for generic programming in space and time. In GPCE. ACM.Google Scholar
Rao, J., Pirahesh, H., Mohan, C. & Lohman, G. (2006) Compiled query execution engine using JVM. In ICDE. IEEE.Google Scholar
Reynolds, J. C. (1972) Definitional interpreters for higher-order programming languages. In Proceedings of the ACM Annual Conference - Volume 2. ACM ‘72.Google Scholar
Reynolds, J. C. (1998) Definitional interpreters for higher-order programming languages. Higherorder Symb. Comput. 11(4). ACM.Google Scholar
Rompf, T. (2012) Lightweight Modular Staging and Embedded Compilers: Abstraction Without Regret for High-Level High-Performance Programming. Ph.D. thesis, EPFL.Google Scholar
Rompf, T. (2016a) The essence of multi-stage evaluation in LMS. A List of Successes That Can Change the World. Lecture Notes in Computer Science, vol. 9600. Springer.CrossRefGoogle Scholar
Rompf, T. (2016b) Reflections on LMS: Exploring front-end alternatives. In Scala Symposium.Google Scholar
Rompf, T. & Amin, N. (2015) Functional pearl: A SQL to C compiler in 500 lines of code. In ICFP. ACM.Google Scholar
Rompf, T. & Odersky, M. (2010) Lightweight modular staging: A pragmatic approach to runtime code generation and compiled DSLs. In GPCE. ACM.Google Scholar
Rompf, T. & Odersky, M. (2012) Lightweight modular staging: A pragmatic approach to runtime code generation and compiled DSLs. Commun. ACM 55(6).CrossRefGoogle Scholar
Rompf, T.Sujeeth, A. K., Lee, H. J., Brown, K. J., Chafi, H., Odersky, M. & Olukotun, K. (2011) Building-blocks for performance oriented DSLs. In IFIP Working Conference on Domain-Specific Languages (DSL). EPTCS, vol. 66. Open Publishing Association.Google Scholar
Rompf, T., Amin, N., Moors, A., Haller, P. & Odersky, M. (2012) Scala-Virtualized: Linguistic reuse for deep embeddings. Higher-order Symb. Comput. 25(1). ACM.CrossRefGoogle Scholar
Rompf, T., Sujeeth, A. K., Amin, N., Brown, K., Jovanovic, V., Lee, H. J., Jonnalagedda, M., Olukotun, K. & Odersky, M. (2013) Optimizing data structures in high-level programs. In POPL. ACM.Google Scholar
Rompf, T., Sujeeth, A. K., Brown, K. J., Lee, H. J., Chafi, H. & Olukotun, K. (2014) Surgical precision JIT compilers. In PLDI. ACM.Google Scholar
Rompf, T., Brown, K. J., Lee, H. J., Sujeeth, A. K., Jonnalagedda, M., Amin, N., Ofenbeck, G., Stojanov, A., Klonatos, Y., Dashti, M., Koch, C., Püschel, M. & Olukotun, K. (2015) Go meta! A case for generative programming and DSLs in performance critical systems. In SNAPL.Google Scholar
Schultz, U. P., Lawall, J. L. & Consel, C. (2003) Automatic program specialization for Java. ACM Trans. Program. Lang. Syst. 25(4).CrossRefGoogle Scholar
Shaikhha, A., Klonatos, I., Parreaux, L. E. V., Brown, L., Dashti Rahmat Abadi, M. & Koch, C. (2016) How to architect a query compiler. In SIGMOD. ACM.Google Scholar
Shali, A. & Cook, W. R. (2011) Hybrid partial evaluation. In OOPSLA. ACM.Google Scholar
Sperber, M. & Thiemann, P. (1996) Realistic compilation by partial evaluation. In PLDI. ACM.Google Scholar
Stonebraker, M. & Çetintemel, U. (2005) “One Size Fits All”: An idea whose time has come and gone (abstract). In ICDE. IEEE.Google Scholar
Stonebraker, M., Madden, S., Abadi, D. J., Harizopoulos, S., Hachem, N. & Helland, P. (2007) The end of an architectural era (it’s time for a complete rewrite). In PVLDB. ACM.Google Scholar
Sujeeth, A. K., Rompf, T., Brown, K. J., Lee, H. J., Chafi, H., Popic, V., Wu, M., Prokopec, A., Jovanovic, V., Odersky, M. & Olukotun, K. (2013a) Composition and reuse with compiled domain-specific languages. In ECOOP. Springer.Google Scholar
Sujeeth, A. K., Gibbons, A., Brown, K. J., Lee, H. J., Rompf, T., Odersky, M. & Olukotun, K. (2013b) Forge: Generating a high performance DSL implementation from a declarative specification. In GPCE. ACM.Google Scholar
Svenningsson, J. & Axelsson, E. (2012) Combining deep and shallow embedding for EDSL. In Trends in Functional Programming (TFP).Google Scholar
Taha, W. & Sheard, T. (2000) MetaML and multi-stage programming with explicit annotations. Theor. Comput. Sci. 248(1–2). Elsevier.CrossRefGoogle Scholar
Tahboub, R. Y. & Rompf, T. (2016) On supporting compilation in spatial query engines (vision paper). In SIGSPATIAL.Google Scholar
Tahboub, R. Y., Essertel, G. M. & Rompf, T. (2018) How to architect a query compiler, revisited. In SIGMOD. ACM. The Transaction Processing Council. (2002) TPC-H Revision 2.Google Scholar
Thiemann, P. (2013) Partially static operations. In PEPM. ACM.Google Scholar
Thiemann, P. & Dussart, D. (1999) Partial evaluation for higher-order languages with state. Technical Report. Germany: Universität Tübingen.Google Scholar
Tobin-Hochstadt, S., St-Amour, V., Culpepper, R., Flatt, M. & Felleisen, M. (2011) Languages as libraries. In PLDI. ACM.Google Scholar
Zukowski, M., Boncz, P. A., Nes, N. & Héman, S. (2005) MonetDB/X100 - a DBMS in the CPU cache. IEEE Data Eng. Bull. 28(2).Google Scholar
Submit a response


No Discussions have been published for this article.
You have Access

Save article to Kindle

To save this article to your Kindle, first ensure is added to your Approved Personal Document E-mail List under your Personal Document Settings on the Manage Your Content and Devices page of your Amazon account. Then enter the ‘name’ part of your Kindle email address below. Find out more about saving to your Kindle.

Note you can select to save to either the or variations. ‘’ emails are free but can only be saved to your device when it is connected to wi-fi. ‘’ emails can be delivered even when you are not connected to wi-fi, but note that service fees apply.

Find out more about the Kindle Personal Document Service.

A SQL to C compiler in 500 lines of code
Available formats

Save article to Dropbox

To save this article to your Dropbox account, please select one or more formats and confirm that you agree to abide by our usage policies. If this is the first time you used this feature, you will be asked to authorise Cambridge Core to connect with your Dropbox account. Find out more about saving content to Dropbox.

A SQL to C compiler in 500 lines of code
Available formats

Save article to Google Drive

To save this article to your Google Drive account, please select one or more formats and confirm that you agree to abide by our usage policies. If this is the first time you used this feature, you will be asked to authorise Cambridge Core to connect with your Google Drive account. Find out more about saving content to Google Drive.

A SQL to C compiler in 500 lines of code
Available formats

Reply to: Submit a response

Please enter your response.

Your details

Please enter a valid email address.

Conflicting interests

Do you have any conflicting interests? *