Abstract
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.
This is a preview of subscription content, log in via an institution.
Buying options
Tax calculation will be finalised at checkout
Purchases are for personal use only
Learn about institutional subscriptionsReferences
Abadi, D.J., Madden, S.R., Hachem, N.: Column-stores vs. row-stores: how different are they really? In: Proceedings of the 2008 ACM SIGMOD International Conference on Management of Data, pp. 967–980 (2008)
Balkesen, Ç., Teubner, J., Alonso, G., Özsu, M.T.: Main-memory hash joins on modern processor architectures. IEEE Trans. Knowl. Data Eng. 27(7), 1754–1766 (2015)
Binnig, C., May, N., Mindnich, T.: SQLScript: efficiently analyzing big enterprise data in SAP HANA. In: Database Systems for Business, Technology, and Web, pp. 363–382 (2013)
Books online for SQL server 2016. https://msdn.microsoft.com/en-us/library/ms191007.aspx
Chaudhuri, S., Shim, K.: Optimization of queries with user-defined predicates. ACM Trans. Database Syst. 24(2), 177–228 (1999)
Cormen, T.H., Leiserson, C.E., Rivest, R.L., Stein, C.: Introduction to Algorithms, 3rd edn. The MIT Press, Cambridge (2009)
Courtois, P.J., Heymans, F., Parnas, D.L.: Concurrent control with “readers” and “writers”. Commun. ACM 14(10), 667–668 (1971)
Färber, F., Cha, S.K., Primsch, J., Bornhövd, C., Sigg, S., Lehner, W.: SAP HANA database: data management for modern business applications. SIGMOD Rec. 40(4), 45–51 (2012)
Färber, F., May, N., Lehner, W., Große, P., Müller, I., Rauhe, H., Dees, J.: The SAP HANA database-an architecture overview. IEEE Data Eng. Bull. 35(1), 423–434 (2012)
Friedman, E., Pawlowski, P., Cieslewicz, J.: SQL/Mapreduce: a practical approach to self-describing, polymorphic, and parallelizable user-defined functions. Proc. VLDB Endow. 2(2), 1402–1413 (2009)
Gan, Q., Suel, T.: Improved techniques for result caching in web search engines. In: Proceedings of the 18th International Conference on WWW, pp. 431–440 (2009)
Garrod, C., Manjhi, A., Ailamaki, A., Maggs, B., Mowry, T., Olston, C., Tomasic, A.: Scalable query result caching for web applications. Proc. VLDB Endow. 1(1), 550–561 (2008)
Google sparsehash. http://goog-sparsehash.sourceforge.net/
Hash table benchmarks. http://incise.org/hash-table-benchmarks.html
Hellerstein, J.M., Naughton, J.F.: Query execution techniques for caching expensive methods. SIGMOD Rec. 25(2), 423–434 (1996)
Hellerstein, J.M., Stonebraker, M.: Predicate migration: optimizing queries with expensive predicates. SIGMOD Rec. 22(2), 267–276 (1993)
Heydon, A., Levin, R., Yu, Y.: Caching function calls using precise dependencies. SIGPLAN Not. 35(5), 311–320 (2000)
IBM i version 7.2, database SQL programming. https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/sqlp/rbafypdf.pdf
Jaedicke, M., Mitschang, B.: On parallel processing of aggregate and scalar functions in object-relational DBMS. SIGMOD Rec. 27(2), 379–389 (1998)
Jarke, M.: Common subexpression isolation in multiple query optimization. In: Query Processing in Database Systems, pp. 191–205 (1985)
Knuth, D.E.: The Art of Computer Programming, vol. 3: Sorting and Searching, 2nd edn. Addison Wesley Longman Publishing Co., Inc, Boston (1998)
Mistry, H., Roy, P., Sudarshan, S., Ramamritham, K.: Materialized view selection and maintenance using multi-query optimization. SIGMOD Rec. 30(2), 307–318 (2001)
Oracle database performance tuning guide, 12c release 1. https://docs.oracle.com/database/121/TGDBA/toc.htm
Performance notes. http://goog-sparsehash.sourceforge.net/doc/performance.html
Richardson, S.E.: Caching function results: faster arithmetic by avoiding unnecessary computation. Technical report, Mountain View, CA, USA (1992)
Ross, K.A., Srivastava, D., Sudarshan, S.: Materialized view maintenance and integrity constraint checking: trading space for time. SIGMOD Rec. 25(2), 447–458 (1996)
Sap, ERP 6.0 enhancement package 8. http://help.sap.com/erp2005_ehp_08/helpdata/en/59/cdc8109ce34bca896115f8ae660a69/content.htm
Sellis, T.K.: Multiple-query optimization. ACM Trans. Database Syst. 13(1), 23–52 (1988)
Sikka, V., Färber, F., Lehner, W., Cha, S.K., Peh, T., Bornhövd, C.: Efficient transaction processing in SAP HANA database: The end of a column store myth. In: Proceedings of the 2012 ACM SIGMOD International Conference on Management of Data, pp. 731–742 (2012)
Acknowledgments
The work of Ryu, Lee and Park was supported in part by the National Research Foundation of Korea (NRF) funded by the Ministry of Science, ICT & Future Planning (No. 2012M3A9D1054622).
Author information
Authors and Affiliations
Corresponding author
Editor information
Editors and Affiliations
Rights and permissions
Copyright information
© 2017 Springer International Publishing AG
About this paper
Cite this paper
Ryu, C. et al. (2017). Optimizing Scalar User-Defined Functions in In-Memory Column-Store Database Systems. In: Candan, S., Chen, L., Pedersen, T., Chang, L., Hua, W. (eds) Database Systems for Advanced Applications. DASFAA 2017. Lecture Notes in Computer Science(), vol 10178. Springer, Cham. https://doi.org/10.1007/978-3-319-55699-4_35
Download citation
DOI: https://doi.org/10.1007/978-3-319-55699-4_35
Published:
Publisher Name: Springer, Cham
Print ISBN: 978-3-319-55698-7
Online ISBN: 978-3-319-55699-4
eBook Packages: Computer ScienceComputer Science (R0)