Optimizing Scalar User-Defined Functions in In-Memory Column-Store Database Systems

User-defined functions such as currency conversion and factory calendar are important ingredients in many business applications. Since currency conversion and factory calendar are expensive user-defined functions, optimizing these functions is essential to high performance business applications. We optimize scalar user-defined functions by caching function call results. In this paper we investigate which method for function result caching is best in the context of in-memory column-store database systems. Experiments show that our method, which implements a function result cache as an array, combined with SAP HANA in-memory column store provides the high performance required by real-time global business applications.

[1]  Wolfgang Lehner,et al.  SAP HANA database: data management for modern business applications , 2012, SGMD.

[2]  Daniel J. Abadi,et al.  Column-stores vs. row-stores: how different are they really? , 2008, SIGMOD Conference.

[3]  Surajit Chaudhuri,et al.  Optimization of queries with user-defined predicates , 1996, TODS.

[4]  Timos K. Sellis,et al.  Multiple-query optimization , 1988, TODS.

[5]  S. Richardson.  Caching Function Results: Faster Arithmetic by Avoiding Unnecessary Computation , 1992 .

[6]  Gustavo Alonso,et al.  Main-Memory Hash Joins on Modern Processor Architectures , 2015, IEEE Transactions on Knowledge and Data Engineering.

[7]  John Cieslewicz,et al.  SQL/MapReduce: A practical approach to self-describing, polymorphic, and parallelizable user-defined functions , 2009, Proc. VLDB Endow..

[8]  Ronald L. Rivest,et al.  Introduction to Algorithms , 1990 .

[9]  Krithi Ramamritham,et al.  Materialized view selection and maintenance using multi-query optimization , 2000, SIGMOD '01.

[10]  Donald Ervin Knuth,et al.  The Art of Computer Programming , 1968 .

[11]  Bernhard Mitschang,et al.  On parallel processing of aggregate and scalar functions in object-relational DBMS , 1998, SIGMOD '98.

[12]  David Lorge Parnas,et al.  Concurrent control with “readers” and “writers” , 1971, CACM.

[13]  Wolfgang Lehner,et al.  Efficient transaction processing in SAP HANA database: the end of a column store myth , 2012, SIGMOD Conference.

[14]  Michael Stonebraker,et al.  Predicate migration: optimizing queries with expensive predicates , 1992, SIGMOD Conference.

[15]  Norman May,et al.  SQLScript: Efficiently Analyzing Big Enterprise Data in SAP HANA , 2013, BTW.

[16]  Torsten Suel,et al.  Improved techniques for result caching in web search engines , 2009, WWW '09.

[17]  Matthias Jarke,et al.  Common Subexpression Isolation in Multiple Query Optimization , 1984, Query Processing in Database Systems.

[18]  Bruce M. Maggs,et al.  Scalable query result caching for web applications , 2008, Proc. VLDB Endow..

[19]  Allan Heydon,et al.  Caching function calls using precise dependencies , 2000, PLDI '00.

[20]  Norman May,et al.  The SAP HANA Database -- An Architecture Overview , 2012, IEEE Data Eng. Bull..

[21]  Kenneth A. Ross,et al.  Materialized view maintenance and integrity constraint checking: trading space for time , 1996, SIGMOD '96.

[22]  Jeffrey F. Naughton,et al.  Query execution techniques for caching expensive methods , 1996, SIGMOD '96.