Database Performance Tuning and Query Optimization Sadhana J. Kamatkar 1(&) , Ajit Kamble 1 , Amelec Viloria 2 , Lissette Hernández-Fernandez 2 , and Ernesto García Cali 2 1 University of Mumbai, Mumbai, India sjkamatkar@mu.ac.in, ajit@ucc.mu.ac.in 2 Universidad de la Costa, Barranquilla, Colombia {aviloria7,lhernand31,egarcia29}@cuc.edu.co Abstract. Today, IT professionals are challenged with the task of ongoing improvements to achieve goals of businesses. Unfortunately, some factor/Resources, skill environment does not dynamically grow as fast as business needs. That sequence of events creates major obstacles for DB infrastructure, deployment, administration and maintenance. This paper dis- cusses the performance issues, different bottlenecks such as CPU bottlenecks, Memory structures, Input output capacity issue, Database Design issues and Indexing issues. Also this paper address Tuning stages and how SQL queries can be optimized for better performance. In this paper we are focusing on query tuning tips & tricks which can be applied to gain immediate performance gain by creating Query Execution Flow Chart. We demonstrate the application of this technique in an Employee Biometric Attendance Management System. Keywords: Database management system Á RDBMS Á Database tuning Query optimization Á Database performance Á Optimization techniques 1 Introduction The goal of database performance tuning is to minimize the response time of queries by making the best use of system resources. The best use of these resources involves minimizing network traf fic, disk I/O, and CPU time. This goal can only be achieved by understanding the logical and physical structure of data, the applications used on system, and how the conflicting uses of database might affect performance. Although newer relational databases and faster hardware run most SQL queries with a signifi- cantly small response time, there is always room for improvement. SQL performance tuning can be an incredibly dif ficult task, particularly when working with large- scale data where even the most minor change can have a dramatic - positive or negative impact on performance. Since most relational databases share same design concepts under their hood, this paper is not specific to any particular vendor. Although in our example we talk about five databases, these tips apply to a wider range of RDBMS. Database Management System (DBMS) is the main source of any Organization, Institute or Company to run their business. One of the core value of any organization is customer centricity, performance tuning is highly desirable and Customers’ needs to be © Springer International Publishing AG, part of Springer Nature 2018 Y. Tan et al. (Eds.): DMBD 2018, LNCS 10943, pp. 3–11, 2018. https://doi.org/10.1007/978-3-319-93803-5_1