research-article

Extracting Equivalent SQL from Imperative Code in Database Applications

Authors:

K. Venkatesh Emani,

Karthik Ramachandra,

Subhro Bhattacharya,

S. SudarshanAuthors Info & Claims

SIGMOD '16: Proceedings of the 2016 International Conference on Management of Data

Pages 1781 - 1796

https://doi.org/10.1145/2882903.2882926

Published: 26 June 2016 Publication History

Abstract

Optimizing the performance of database applications is an area of practical importance, and has received significant attention in recent years. In this paper we present an approach to this problem which is based on extracting a concise algebraic representation of (parts of) an application, which may include imperative code as well as SQL queries. The algebraic representation can then be translated into SQL to improve application performance, by reducing the volume of data transferred, as well as reducing latency by minimizing the number of network round trips. Our techniques can be used for performing optimizations of database applications that techniques proposed earlier cannot perform. The algebraic representations can also be used for other purposes such as extracting equivalent queries for keyword search on form results. Our experiments indicate that the techniques we present are widely applicable to real world database applications, in terms of successfully extracting algebraic representations of application behavior, as well as in terms of providing performance benefits when used for optimization.

References

[1]

A. V. Aho, M. S. Lam, R. Sethi, and J. D. Ullman. Compilers: Principles, Techniques, and Tools. Addison-Wesley, 2006.

Digital Library

[2]

M. Chavan, R. Guravannavar, K. Ramachandra, and S. Sudarshan. Dbridge: A program rewrite tool for set-oriented query execution. In ICDE, pages 1284--1287, 2011.

Digital Library

[3]

J. Cheney, S. Lindley, and P. Wadler. Query shredding: Efficient relational evaluation of queries over nested multisets. In SIGMOD, pages 1027--1038, 2014.

Digital Library

[4]

A. Cheung, A. Solar-Lezama, and S. Madden. Optimizing database-backed applications with query synthesis. PLDI, pages 3--14, 2013.

Digital Library

[5]

S. K. Debray, W. Evans, R. Muth, and B. De Sutter. Compiler techniques for code compaction. ACM Trans. Program. Lang. Syst., 22(2):378--415, Mar. 2000.

Digital Library

[6]

C. Duda, G. Frey, D. Kossmann, and C. Zhou. Ajaxsearch: crawling, indexing and searching web 2.0 applications. PVLDB, 1(2):1440--1443, 2008.

Digital Library

[7]

C. A. Galindo-Legaria and M. Joshi. Orthogonal optimization of subqueries and aggregation. In SIGMOD, pages 571--581, 2001.

Digital Library

[8]

G. Giorgidze, T. Grust, T. Schreiber, and J. Weijers. Haskell boards the ferry. In Implementation and Application of Functional Languages, pages 1--18. Springer, 2011.

Digital Library

[9]

G. Graefe. The cascades framework for query optimization. IEEE Data Eng. Bull., 18(3):19--29, 1995.

[10]

G. Graefe and W. J. McKenna. The volcano optimizer generator: Extensibility and efficient search. In Data Engineering, pages 209--218. IEEE, 1993.

Digital Library

[11]

R. Guravannavar and S. Sudarshan. Rewriting Procedures for Batched Bindings. In Procs. VLDB, pages 1107--1123, 2008.

Digital Library

[12]

M. S. Hecht and J. D. Ullman. Flow graph reducibility. STOC, pages 238--250, 1972.

Digital Library

[13]

Hibernate http://www.hibernate.org.

[14]

M.-Y. Iu, E. Cecchet, and W. Zwaenepoel. Jreq: Database queries in imperative languages. In R. Gupta, editor, Compiler Construction, volume 6011 of LNCS, pages 84--103. Springer, 2010.

Digital Library

[15]

MAhjong TOurnament SOftware https://code.google.com/p/matoso/.

[16]

S. S. Muchnick. Advanced Compiler Design and Implementation. Morgan Kaufmann, 1997.

Digital Library

[17]

C. Radoi, S. J. Fink, R. Rabbah, and M. Sridharan. Translating imperative code to mapreduce. In OOPSLA, pages 909--927. ACM, 2014.

Digital Library

[18]

K. Ramachandra, M. Chavan, R. Guravannavar, and S. Sudarshan. Program transformations for asynchronous and batched query submission. TKDE, 27(2):531--544, 2015.

[19]

K. Ramachandra and S. Sudarshan. Holistic optimization by prefetching query results. In SIGMOD, pages 133--144, 2012.

Digital Library

[20]

ObjectWeb Consortium. Rice University bulletin board system http://jmob.objectweb.org/rubbos.html.

[21]

ObjectWeb Consortium. Rice University bidding system http://rubis.objectweb.org/.

[22]

X. Shi, B. Cui, G. Dobbie, and B. C. Ooi. Towards unified ad-hoc data processing. In SIGMOD, pages 1263--1274, 2014.

Digital Library

[23]

V. Simhadri, K. Ramachandra, A. Chaitanya, R. Guravannavar, and S. Sudarshan. Decorrelation of user defined function invocations in queries. In ICDE, pages 532--543, March 2014.

[24]

A. Solar-Lezama, L. Tancau, R. Bodik, S. Seshia, and V. Saraswat. Combinatorial sketching for finite programs, 2006.

Digital Library

[25]

Soot: A Java Optimization Framework http://www.sable.mcgill.ca/soot (Oct 2014).

[26]

F. Tip. A survey of program slicing techniques. Technical report, 1994.

Digital Library

[27]

J. D. Ullman and J. Widom. A First Course in Database Systems. Pearson, 2007.

Digital Library

[28]

B. Wiedermann, A. Ibrahim, and W. R. Cook. Interprocedural query extraction for transparent persistence. In OOPSLA, pages 19--36, 2008.

Digital Library

[29]

Wilos Orchestration Software http://www.ohloh.net/p/6390.

[30]

M. Zhang, H. Elmeleegy, C. M. Procopiuc, and D. Srivastava. Reverse engineering complex join queries. In SIGMOD, pages 809--820, 2013.

Digital Library

Cited By

Zhang CFarouk T(2024)Sharing Queries with Nonequivalent User-defined Aggregate FunctionsACM Transactions on Database Systems10.1145/364913349:2(1-46)Online publication date: 10-Apr-2024
https://dl.acm.org/doi/10.1145/3649133
Hu GWang ZTang CShen JDong ZYao SChen H(2024)WeBridge: Synthesizing Stored Procedures for Large-Scale Real-World Web ApplicationsProceedings of the ACM on Management of Data10.1145/36393192:1(1-29)Online publication date: 26-Mar-2024
https://dl.acm.org/doi/10.1145/3639319
Chasialis KPalaiologou TFoufoulas YSimitsis AIoannidis Y(2024)QFusor: A UDF Optimizer Plugin for SQL Databases2024 IEEE 40th International Conference on Data Engineering (ICDE)10.1109/ICDE60146.2024.00427(5457-5460)Online publication date: 13-May-2024
https://doi.org/10.1109/ICDE60146.2024.00427
Show More Cited By

Index Terms

Extracting Equivalent SQL from Imperative Code in Database Applications
1. Information systems
  1. Data management systems
    1. Database management system engines
      1. Database query processing
        Query optimization
    2. Middleware for databases
      1. Object-relational mapping facilities
2. Software and its engineering
  1. Software notations and tools
    1. Compilers
      1. Preprocessors
      2. Source code generation

Recommendations

Comparing NoSQL MongoDB to an SQL DB
ACMSE '13: Proceedings of the 51st annual ACM Southeast Conference

NoSQL database solutions are becoming more and more prevalent in a world currently dominated by SQL relational databases. NoSQL databases were designed to provide database solutions for large volumes of data that is not structured. However, the ...
SQL: From Traditional Databases to Big Data
SIGCSE '16: Proceedings of the 47th ACM Technical Symposium on Computing Science Education

The Structured Query Language (SQL) is the main programing language designed to manage data stored in database systems. While SQL was initially used only with relational database management systems (RDBMS), its use has been significantly extended with ...
Oracle Database 12c SQL

Comments

Information & Contributors

Information

Published In

cover image ACM Conferences

SIGMOD '16: Proceedings of the 2016 International Conference on Management of Data

June 2016

2300 pages

ISBN:9781450335317

DOI:10.1145/2882903

General Chairs:
Fatma Özcan
IBM Research, USA
,
Georgia Koutrika
HP Labs, USA
,
Program Chair:
Sam Madden
Massachusetts Institute of Technology, USA

Copyright © 2016 ACM.

Permission to make digital or hard copies of all or part of this work for personal or classroom use is granted without fee provided that copies are not made or distributed for profit or commercial advantage and that copies bear this notice and the full citation on the first page. Copyrights for components of this work owned by others than the author(s) must be honored. Abstracting with credit is permitted. To copy otherwise, or republish, to post on servers or to redistribute to lists, requires prior specific permission and/or a fee. Request permissions from [email protected].

Sponsors

SIGMOD: ACM Special Interest Group on Management of Data

Publisher

Association for Computing Machinery

New York, NY, United States

Publication History

Published: 26 June 2016

Permissions

Request permissions for this article.

Request Permissions

Check for updates

Author Tags

Qualifiers

Research-article

Conference

SIGMOD/PODS'16

Sponsor:

SIGMOD

SIGMOD/PODS'16: International Conference on Management of Data

June 26 - July 1, 2016

California, San Francisco, USA

Acceptance Rates

Overall Acceptance Rate 785 of 4,003 submissions, 20%

Contributors

Other Metrics

View Article Metrics

Bibliometrics & Citations

Bibliometrics

Article Metrics

28
Total Citations
View Citations
737
Total Downloads

Downloads (Last 12 months)27
Downloads (Last 6 weeks)6

Reflects downloads up to 25 Feb 2025

Other Metrics

View Author Metrics

Citations

Cited By

Zhang CFarouk T(2024)Sharing Queries with Nonequivalent User-defined Aggregate FunctionsACM Transactions on Database Systems10.1145/364913349:2(1-46)Online publication date: 10-Apr-2024
https://dl.acm.org/doi/10.1145/3649133
Hu GWang ZTang CShen JDong ZYao SChen H(2024)WeBridge: Synthesizing Stored Procedures for Large-Scale Real-World Web ApplicationsProceedings of the ACM on Management of Data10.1145/36393192:1(1-29)Online publication date: 26-Mar-2024
https://dl.acm.org/doi/10.1145/3639319
Chasialis KPalaiologou TFoufoulas YSimitsis AIoannidis Y(2024)QFusor: A UDF Optimizer Plugin for SQL Databases2024 IEEE 40th International Conference on Data Engineering (ICDE)10.1109/ICDE60146.2024.00427(5457-5460)Online publication date: 13-May-2024
https://doi.org/10.1109/ICDE60146.2024.00427
Wang CFan GYao PPan FZhang C(2023)Verifying Data Constraint Equivalence in FinTech Systems2023 IEEE/ACM 45th International Conference on Software Engineering (ICSE)10.1109/ICSE48619.2023.00117(1329-1341)Online publication date: May-2023
https://doi.org/10.1109/ICSE48619.2023.00117
Foufoulas YSimitsis A(2023)User-Defined Functions in Modern Data Engines2023 IEEE 39th International Conference on Data Engineering (ICDE)10.1109/ICDE55515.2023.00276(3593-3598)Online publication date: Apr-2023
https://doi.org/10.1109/ICDE55515.2023.00276
Pratten DMathieson L(2023)Relational Expressions for Data Transformation and ComputationDatabases Theory and Applications10.1007/978-3-031-47843-7_17(241-255)Online publication date: 7-Nov-2023
https://doi.org/10.1007/978-3-031-47843-7_17
Grulich PZeuch SMarkl V(2022)BabelfishProceedings of the VLDB Endowment10.14778/3489496.348950115:2(196-210)Online publication date: 4-Feb-2022
https://dl.acm.org/doi/10.14778/3489496.3489501
Fischer THirn DGrust TIves ZBonifati AEl Abbadi A(2022)Snakes on a Plan: Compiling Python Functions into Plain SQL QueriesProceedings of the 2022 International Conference on Management of Data10.1145/3514221.3520175(2389-2392)Online publication date: 10-Jun-2022
https://dl.acm.org/doi/10.1145/3514221.3520175
Chawla GAman NKomondoor RBokil AKharat NDwyer MDamian DZeller A(2022)Verification of ORM-based controllers by summary inferenceProceedings of the 44th International Conference on Software Engineering10.1145/3510003.3510148(2340-2351)Online publication date: 21-May-2022
https://dl.acm.org/doi/10.1145/3510003.3510148
Burghardt THirn DGrust T(2022)Functional Programming on Top of SQL EnginesPractical Aspects of Declarative Languages10.1007/978-3-030-94479-7_5(59-78)Online publication date: 17-Jan-2022
https://dl.acm.org/doi/10.1007/978-3-030-94479-7_5
Show More Cited By

View Options

Login options

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

Full Access

Get this Publication

View options

PDF

View or Download as a PDF file.

eReader

View online with eReader.

Figures

Tables

Media

View Table of Conten