Automated Software Engineering

, Volume 23, Issue 4, pp 535–568 | Cite as

Automatic query rewriting schemes for multitenant SaaS applications

  • Chun-Feng LiaoEmail author
  • Kung Chen
  • Deik Hoong Tan
  • Jiu-Jye Chen


In Software as a Service (SaaS) environments, designing and realizing multitenant schema-mapping that supports a shared database with custom extensions is a non-trivial task. Universal Table is one promising schema-mapping technique that is commonly used. However, there has been little research devoted to the design and realization of a query rewriting scheme for Universal Table. In this paper, we present a collection of general query rewriting schemes for Universal Table that can transparently transform tenant-specific logical queries into corresponding physical queries. Based on the design, we have developed a Java-based schema-mapping and query rewriting middleware for Universal Table and a sample online shopping SaaS application to verify its feasibility. Additionally, analytical results that can be used to predict the overhead of our schemes are also reported. Finally, we conduct a series of experiments and find that the results not only agree well with our analytical predictions but also show that our schemes are scalable to the number of tenants and the number of concurrent database connections.


Schema-mapping SaaS Query rewriting Multitenant 



This work is partially sponsored by Ministry of Science and Technology, Taiwan, under Grant 103-2221-E-004-005, 103-2221-E-004-018, 103-2218-E-004-003 and 103-2218-E-004-001.


  1. Aulbach, S., Grust, T., Jacobs, D., Kemper, A., Rittinger, J.: Multi-Tenant databases for software as a service: schema-mapping techniques. In: Proceedings of the ACM International Conference on Management of Data. ACM, New York (2008)Google Scholar
  2. Chong, F., Carraro, G.: Architecture strategies for catching the long tail. (2006). Accessed 26 May, July 2013
  3. Chong, F., Carraro, G., Wolter, R.: Multi-tenant data architecture. (2006). Accessed 26 May 2013
  4. Clinton, B.: iBATIS JPetStore 4.0.5 (2004). Accessed 26 May 2013
  5. Codd, E. F.: Relational completeness of database sublanguages. In: Database Systems. Prentice-Hall, Upper Saddle River (1972)Google Scholar
  6. Copeland, G. P., Khoshafian, S. N.: A decomposition storage model. In: Proceedings of the ACM International Conference on Management of data (SIGMOD), pp. 268–279. ACM, New York (1985)Google Scholar
  7. Du, J., Wen, H. Y., Yang, Z. J.: Research on data layer sturcture of multi-tenant E-commerce system. In: Proceedings of the IEEE 17th International Conference on Industrial Engineering and Engineering Management (IE&EM), pp. 362–365. IEEE (2010)Google Scholar
  8. Elmasri, R., Navathe, S.B.: Database Systems: Models, Languages, Design, and Application Programming. Pearson Education, Upper Saddle River (2011)zbMATHGoogle Scholar
  9. Halili, E.H.: Apache Jmeter: A Practical Beginner’s Guide to Automated Testing and Performance Measurement for Your Websites. Packt Publishing, Birmingham (2008)Google Scholar
  10. Jarke, M., Koch, J.: Query optimization in database systems. ACM Comput. Surv. 16(4), 111–152 (1984)MathSciNetCrossRefzbMATHGoogle Scholar
  11. Koziolek, H.: The SPOSAD architectural style for multi-tenant software applications. In: Proceedings of the 9th Working IEEE/IFIP Conferences on Software Architecture, pp. 320–327. IEEE (2011)Google Scholar
  12. Krebs, R., Momm, C., Konev, S.: Architectural concerns in multi-tenant SaaS applications. In: Proceedings of the International Conference on Cloud Computing and Service Science (CLOSER12) (2012)Google Scholar
  13. Li, C.: Transforming relational database into HBase: a case study. In: Proceedings of the IEEE International Conference on Software Engineering and Service Sciences (ICSESS). IEEE (2010)Google Scholar
  14. Li, H., Yang, D., Zhang, X.H.: Survey on multi-tenant data architecture for SaaS. Int. J. Comput. Sci. Issues 9(6–3), 198–204 (2012)Google Scholar
  15. Liao, C. F., Chen, K., Chen, J. J.: Toward a tenant-aware query rewriting engine for universal table schema-mapping. In: Proceedings of the IEEE International Conference on Cloud Computing Technology and Science (IEEE CloudCom’12), Presented in 2012 International Workshop on SaaS (Software-as-a-Service) Architecture and Engineering, pp. 833–838. IEEE (2012)Google Scholar
  16. Lynch, N.A.: Distributed Algorithms. Morgan Kaufmann, Burlington (1996)Google Scholar
  17. Maier, D., Ullman, J.D.: Maximal objects and the semantics of universal relation databases. ACM Trans. Database Syst. 8(11), 1–14 (1983)CrossRefzbMATHGoogle Scholar
  18. OSGi Core Release 5, The OSGi Alliance (2012)Google Scholar
  19. Pereira, J., Chiueh, T.C.: SQL Rewriting Engine and its Applications. Technical Report. Stony Brook University (2007)Google Scholar
  20. Russell, C.: Java Data Objects 2.0. JSR 243 Specification (2010)Google Scholar
  21. Weissman, C. D., Bobrowski, S.: The design of the multitenant internet application development platform. In: Proceedings of the ACM SIGMOD International Conference on Management of Data. ACM, New York (2009)
  22. Yaish, H., Goyal, M., Feuerlicht, G.: An elastic multi-tenant database schema for software as a service. In: Proceedings of the IEEE International Conference on Dependable, Autonomic and Secure Computing, pp. 737–743. IEEE (2011)Google Scholar

Copyright information

© Springer Science+Business Media New York 2015

Authors and Affiliations

  • Chun-Feng Liao
    • 1
    Email author
  • Kung Chen
    • 2
  • Deik Hoong Tan
    • 3
  • Jiu-Jye Chen
    • 2
  1. 1.CS Department and Program in Digital Content and TechnologiesNational Chengchi UniversityTaipeiTaiwan
  2. 2.CS DepartmentNational Chengchi UniversityTaipeiTaiwan
  3. 3.IECS DepartmentFeng Chia UniversityTaichungTaiwan

Personalised recommendations