skip to main content
research-article

Optimizing database-backed applications with query synthesis

Published:16 June 2013Publication History
Skip Abstract Section

Abstract

Object-relational mapping libraries are a popular way for applications to interact with databases because they provide transparent access to the database using the same language as the application. Unfortunately, using such frameworks often leads to poor performance, as modularity concerns encourage developers to implement relational operations in application code. Such application code does not take advantage of the optimized relational implementations that database systems provide, such as efficient implementations of joins or push down of selection predicates. In this paper we present QBS, a system that automatically transforms fragments of application logic into SQL queries. QBS differs from traditional compiler optimizations as it relies on synthesis technology to generate invariants and postconditions for a code fragment. The postconditions and invariants are expressed using a new theory of ordered relations that allows us to reason precisely about both the contents and order of the records produced complex code fragments that compute joins and aggregates. The theory is close in expressiveness to SQL, so the synthesized postconditions can be readily translated to SQL queries. Using 75 code fragments automatically extracted from over 120k lines of open-source code written using the Java Hibernate ORM, we demonstrate that our approach can convert a variety of imperative constructs into relational specifications and significantly improve application performance asymptotically by orders of magnitude.

References

  1. itracker Issue Management System. http://itracker.sourceforge.net/index.html.Google ScholarGoogle Scholar
  2. Wilos Orchestration Software. http://www.ohloh.net/p/6390.Google ScholarGoogle Scholar
  3. z3 Theorem Prover. http://research.microsoft.com/en-us/um/redmond/projects/z3.Google ScholarGoogle Scholar
  4. S. Abiteboul, R. Hull, and V. Vianu. Foundations of Databases. Addison-Wesley, 1995. Google ScholarGoogle ScholarDigital LibraryDigital Library
  5. J. R. Allen and K. Kennedy. Automatic loop interchange. In Proc. CC, pages 233--246, 1984. Google ScholarGoogle ScholarDigital LibraryDigital Library
  6. A. Blass and Y. Gurevich. Inadequacy of computable loop invariants. ACM Trans. Comput. Log., 2(1):1--11, 2001. Google ScholarGoogle ScholarDigital LibraryDigital Library
  7. A. Cheung, O. Arden, S. Madden, and A. C. Myers. Automatic partitioning of database applications. PVLDB, 5, 2011. Google ScholarGoogle ScholarDigital LibraryDigital Library
  8. A. Cheung, A. Solar-Lezama, and S. Madden. Using program synthesis for social recommendations. In Proc. CIKM, pages 1732--1736, 2012. Google ScholarGoogle ScholarDigital LibraryDigital Library
  9. E. Cooper. The script-writer's dream: How to write great sql in your own language, and be sure it will succeed. In Proc. DBPL Workshop, pages 36--51, 2009. Google ScholarGoogle ScholarDigital LibraryDigital Library
  10. E. Cooper, S. Lindley, P. Wadler, and J. Yallop. Links: Web programming without tiers. In Proc. Int.l Symp. on Formal Methods for Components and Objects, pages 266--296, 2006. Google ScholarGoogle ScholarDigital LibraryDigital Library
  11. D. Déharbe, P. Fontaine, S. Merz, and B. W. Paleo. Exploiting symmetry in smt problems. In Proc. Int.l Conf. on Automated Deduction, pages 222--236, 2011. Google ScholarGoogle ScholarDigital LibraryDigital Library
  12. E. W. Dijkstra. Guarded commands, nondeterminacy and formal derivation of programs. Commun. ACM, 18(8):453--457, 1975. Google ScholarGoogle ScholarDigital LibraryDigital Library
  13. M. D. Ernst, J. Cockrell,W. G. Griswold, and D. Notkin. Dynamically discovering likely program invariants to support program evolution. In Proc. ICSE, pages 213--224, 1999. Google ScholarGoogle ScholarDigital LibraryDigital Library
  14. C. Flanagan and S. Qadeer. Predicate abstraction for software verification. In Proc. POPL, pages 191--202, 2002. Google ScholarGoogle ScholarDigital LibraryDigital Library
  15. P. Godefroid and A. Taly. Automated synthesis of symbolic instruction encodings from I/O samples. In Proc. PLDI, pages 441--452, 2012. Google ScholarGoogle ScholarDigital LibraryDigital Library
  16. D. Gries. The Science of Programming. Springer, 1987. Google ScholarGoogle ScholarDigital LibraryDigital Library
  17. T. Grust, M. Mayr, J. Rittinger, and T. Schreiber. FERRY: databasesupported program execution. In Proc. SIGMOD, pages 1063--1066, 2009. Google ScholarGoogle ScholarDigital LibraryDigital Library
  18. A. Gupta and A. Rybalchenko. Invgen: An efficient invariant generator. In Proc. CAV, pages 634--640, 2009. Google ScholarGoogle ScholarDigital LibraryDigital Library
  19. C. A. R. Hoare. An axiomatic basis for computer programming. Commun. ACM, 12(10):576--580, 1969. Google ScholarGoogle ScholarDigital LibraryDigital Library
  20. M.-Y. Iu, E. Cecchet, and W. Zwaenepoel. Jreq: Database queries in imperative languages. In Proc. CC, pages 84--103, 2010. Google ScholarGoogle ScholarDigital LibraryDigital Library
  21. M.-Y. Iu and W. Zwaenepoel. HadoopToSQL: a mapreduce query optimizer. In Proc. EuroSys, pages 251--264, 2010. Google ScholarGoogle ScholarDigital LibraryDigital Library
  22. D. Jackson. Alloy: a lightweight object modelling notation. ACM Trans. Softw. Eng. Methodol., 11(2):256--290, 2002. Google ScholarGoogle ScholarDigital LibraryDigital Library
  23. E. Meijer, B. Beckman, and G. Bierman. LINQ: Reconciling objects, relations and XML in the .NET framework. In Proc. SIGMOD, pages 706--706, 2006. Google ScholarGoogle ScholarDigital LibraryDigital Library
  24. W. Ng. An extension of the relational data model to incorporate ordered domains. Trans. Database Syst., 26(3):344--383, Sept. 2001. Google ScholarGoogle ScholarDigital LibraryDigital Library
  25. N. Nystrom, M. R. Clarkson, and A. C. Myers. Polyglot: An extensible compiler framework for Java. In Proc. CC, pages 138--152, 2003. Google ScholarGoogle ScholarDigital LibraryDigital Library
  26. K. Ramachandra, R. Guravannavar, and S. Sudarshan. Program analysis and transformation for holistic optimization of database applications. In Proc. SOAP Workshop, pages 39--44, 2012. Google ScholarGoogle ScholarDigital LibraryDigital Library
  27. M. Sagiv, T. Reps, and R. Wilhelm. Parametric shape analysis via 3-valued logic. In Proc. POPL, pages 105--118, 1999. Google ScholarGoogle ScholarDigital LibraryDigital Library
  28. J. W. Schmidt and F. Matthes. The DBPL project: Advances in modular database programming. Inf. Syst., 19(2):121--140, 1994. Google ScholarGoogle ScholarDigital LibraryDigital Library
  29. R. Singh and A. Solar-Lezama. Synthesizing data structure manipulations from storyboards. In Proc. FSE, pages 289--299, 2011 Google ScholarGoogle ScholarDigital LibraryDigital Library
  30. A. Solar-Lezama, L. Tancau, R. Bodík, S. Seshia, and V. Saraswat. Combinatorial sketching for finite programs. In Proc. ASPLOS, pages 404--415, 2006. Google ScholarGoogle ScholarDigital LibraryDigital Library
  31. S. Srivastava and S. Gulwani. Program verification using templates over predicate abstraction. In Proc. PLDI, pages 223--234, 2009. Google ScholarGoogle ScholarDigital LibraryDigital Library
  32. S. Srivastava, S. Gulwani, S. Chaudhuri, and J. S. Foster. Path-based inductive synthesis for program inversion. In Proc. PLDI, pages 492--503, 2011. Google ScholarGoogle ScholarDigital LibraryDigital Library
  33. Z. Tatlock and S. Lerner. Bringing extensibility to verified compilers. In Proc. PLDI, pages 111--121, 2010. Google ScholarGoogle ScholarDigital LibraryDigital Library
  34. E. Torlak and D. Jackson. Kodkod: a relational model finder. In Proc. TACAS, pages 632--647, 2007. Google ScholarGoogle ScholarDigital LibraryDigital Library
  35. O. Tripp, M. Pistoia, S. J. Fink, M. Sridharan, and O. Weisman. Taj: effective taint analysis of web applications. In Proc. PLDI, pages 87--97, 2009. Google ScholarGoogle ScholarDigital LibraryDigital Library
  36. J. Whaley and M. Rinard. Compositional pointer and escape analysis for java programs. In Proc. OOPSLA, pages 187--206, 1999. Google ScholarGoogle ScholarDigital LibraryDigital Library
  37. B. Wiedermann and W. R. Cook. Extracting queries by static analysis of transparent persistence. In Proc. POPL, pages 199--210, 2007. Google ScholarGoogle ScholarDigital LibraryDigital Library
  38. B. Wiedermann, A. Ibrahim, and W. R. Cook. Interprocedural query extraction for transparent persistence. In Proc. OOPSLA, pages 19--36, 2008. Google ScholarGoogle ScholarDigital LibraryDigital Library
  39. L. Wong. Kleisli, a functional query system. J. Funct. Program., 10(1):19--56, 2000. Google ScholarGoogle ScholarDigital LibraryDigital Library
  40. Y. Xie and A. Aiken. Scalable error detection using boolean satisfiability. In Proc. POPL, pages 351--363, 2005. Google ScholarGoogle ScholarDigital LibraryDigital Library

Index Terms

  1. Optimizing database-backed applications with query synthesis

    Recommendations

    Comments

    Login options

    Check if you have access through your login credentials or your institution to get full access on this article.

    Sign in

    Full Access

    • Published in

      cover image ACM SIGPLAN Notices
      ACM SIGPLAN Notices  Volume 48, Issue 6
      PLDI '13
      June 2013
      515 pages
      ISSN:0362-1340
      EISSN:1558-1160
      DOI:10.1145/2499370
      Issue’s Table of Contents
      • cover image ACM Conferences
        PLDI '13: Proceedings of the 34th ACM SIGPLAN Conference on Programming Language Design and Implementation
        June 2013
        546 pages
        ISBN:9781450320146
        DOI:10.1145/2491956

      Copyright © 2013 ACM

      Publisher

      Association for Computing Machinery

      New York, NY, United States

      Publication History

      • Published: 16 June 2013

      Check for updates

      Qualifiers

      • research-article

    PDF Format

    View or Download as a PDF file.

    PDF

    eReader

    View online with eReader.

    eReader
    About Cookies On This Site

    We use cookies to ensure that we give you the best experience on our website.

    Learn more

    Got it!