The Gain of SQL Optimization in Databases - Research Paper Example

The Gain of SQL Optimization in Databases 1. Executive summary In spite of becoming a de-facto standard, the main strengths of Relational Database Management System lie in its user friendliness and querying capabilities, in lieu of its efficiency related to the hardware or system overhead. The performance issues in databases are becoming more frequent due to the consistently rising amount of information or data being gathered and processed in the information systems of organizations. Besides this, the delay in the response time of a query and the increase in the demands and expectations of the user have significantly affected the operations of the organizations. Therefore, the Database Management Systems are tuned or optimized so as to improve their performance by making the system run faster. Significant amount of database performance optimization is attained through the SQL optimization that endeavors to optimize the queries at the application level. The first step towards database tuning is to identify the reasons behind the issues and determine the current bottlenecks in the database system. This is an intricate task since the factors that influence the overall performance of a database system are quite diversified. The first classification of factors can be categorized under ‘hardware’ that comprises of processor, disk, memory, and network performance. The other factors include indexing, database design, partitioning or locking that are directly associated with the database system. Also, the issues can occur at the application level of the system. Extensive literature is available about these factors, discussion of which would go beyond the scope of this study. This infers that database optimization requires very explicit set of expertise and includes the fields of database administration, system administration and application development. Usually, the hardware related database performance issues are easier to identify and fix than other factors. Many database performance issues also occur due to sub-optimal or wrong indexing and inappropriate use of locking mechanisms. Although the role of database design is also very important in database performance however the re-designing of a running database system is not at all feasible as it is complex and requires vast resources. The primary focus of this study is SQL optimization as it offers great potential to improve database performance. 2. Thesis Statement This study is aimed towards determining the gain of optimizing SQL in database systems. The paper will discuss the significance of fine tuning SQL queries or code in improving the performance of Database Management Systems in organizations. 3. Background 3.1. Performance Monitoring The database performance is jointly monitored by the database administrators, system administrators, and application developers. However, the database administrator plays the key role in finding out the major bottlenecks in the database system. A wide range of tools can be easily found to enhance the monitoring of performance in a database. The database performance monitoring can be classified in to the following three types: Regular, day-to-day monitoring, Ad-hoc monitoring, and Alert monitoring. The purpose of Regular, day-to-day monitoring is to gather and document information related to the day-to-day performance of the database system. This information history or log enables the administrator or the system to predict and avoid the common problems in future. Even then unexpected delays could occur, therefore, ad-hoc monitoring is done. Due to regular monitoring, the database administrator can quickly identify the problems such as the I/O bottleneck, long sorts deadlock situation, etc. There are various SQL scripts that can provide a quick overview about the basic characteristics of the database system (Darmawan 459-472). Moreover, alert monitoring enables the database to assign threshold values for different variables due to which a warning is triggered whenever a threshold is achieved or surpassed. When a performance issue occurs then the DBA follows the steps given below as a rule of thumb ( Dunham 40-41): If the problem has occurred at only one application then probably the SQL is the cause so check the SQL code. If all the applications are affected then there is a tuning problem in the system so check the memory, I/O usage and sorting. If all the applications related to a particular table experience similar problems then probably the bottleneck has occurred at this point so check out indexing, locking and space. 3.2. Solving Performance Issues The following are the areas in database optimization that have been determined as to have considerable impact over database performance: SQL Tuning Hardware Upgrades Indexing Caching (memory) Locking Table Partitioning The emphasis of this research is over SQL Tuning and Indexing since they are used most frequently in solving problems related to database performance. Therefore, these two areas are discussed further. 4. SQL Tuning SQL Tuning is considered to have great influence over database performance that is above 50 percent of performance issues in databases are fixed through SQL tuning. SQL is basically a declarative language in which the user only specifies the required data. Then the query optimizer of the DBMS determines the access path to be followed (Markl). This task is very difficult as there are numerous different paths available for processing a query correctly. Thus, the best access path selected by the query optimizer is known as the query execution plan (QEP). The important step is to write the query so that just the desired records (rows and columns) are retrieved. Usually, this is not an issue although the application programmers might yet be attracted towards using a ‘select *’ statement inappropriately. Despite the fact that the Database Management Systems’ query optimizers can determine the best query execution plan, however, their decisions are based upon the mathematical models that involve numerous parameters and assumptions. This actually infers that the processing time of query execution can be drastically affected by slightly changing the SQL query. The efficiency of a SQL query depends upon a large number of factors that include indexing, DBMS, table size, table design, memory, etc. Indexing is a method in which the records are searched and sorted in a table. The functionality of indexing improves the speed of the process of record retrieval from the table. Indexing can be referred as a two dimensional matrix that does not depend upon the table for which the index has been formed. Although access time could be significantly reduced thorough indexing but still indexes can raise serious performance issues. Therefore, it is important to consider the impact of multiple indexing over processing time and disk space before creating the indexes. Every index creates an alternate access path for a SQL query due to which the query compilation time is increased as the optimizer will now have to check an additional access path. Thus, one has to be very careful in selecting indexes for addressing the needs of the database application program. Wrong indexing has been found to have adverse effects on the database performance. As for instance, if the data and the requirements are altered after the initial design whereas the indexing is not modified in accordance to that change then delay or deadlock can occur. It has been found that the number of indexes created should not be greater than 10. Also, it has been found that the table should be scanned rather than using the index option when more than 20 percent of the table needs to be processed. Therefore, it is evident that indexing is a very useful tool used to fine tune the databases. Nevertheless, it should be done very judiciously or else it can also backfire. Also, other factors such as the I/O, disk space, etc. should be checked importantly prior to making any changes since indexing can increase the load on the system. According to Rob and Coronel (8-35), the following are the key players that are involved in the database management system environment: • Database System Administrators are those professionals who look after the general operations in database system. • Data Architects or Database Designers are those professionals who create the overall design of the database structure. • Database Application Administrators are those professionals who manage the overall function of the database management system and ascertain appropriate use of the database. • Database System Analysts and Programmers are those experts who create the application programs and employ them in to the database system. • End-Users are those persons who actually use the applications that retrieve information from the database. Most of the organizations follow the above mentioned structure of database experts. Generally, the database designers are responsible for the database logical model that includes table relationships, table structures, etc. whereas the physical implementation of the logical model is in the hands of the database administrator, commonly called DBA. System DBA’s are responsible for the general features of the database environment such as the hardware upgrades, physical implementation, partitioning, memory, etc. whereas the application DBA’s are much closer to the database application developers. Further in this study, we will highlight the role of the DBA’s in SQL optimization. Every database project should have at least one DBA formally assigned to it. Application Database Administrators work closely with the application developers. Application Database Administrators also possess good functional knowledge about the database applications. Key responsibilities of the Application Database Administrator include review of SQL code, guidance and testing. Application Database Administrators are also involved in determining the database performance requirements such as the acceptable response times and the desired response times of SQL queries. During production, the Application Database Administrator monitor database performance and support in tuning the database parameters as a result of change in user patterns such as the change in query pattern, the increased number of users, etc. Despite the above mentioned guidelines, it is still important to enforce tough performance requirements during the development stage and the testing stage of the database system. In spite of the fact that reviewing all SQL code prior to the production phase requires a lot of time and resources, however, it is recommended since it avoids bigger trouble of re-work later on. Therefore, it is important to ensure that re-work should not be required after the application gets into the production phase. According to Hayden, writing efficient SQL queries requires a lot of training and experience in the field. The following points have been identified as potential threats or problems in database systems: Application Database Administrators might not have sufficient amount of training, experience or functional knowledge required to create efficient SQL query during the development stage of the database system. Application Database Administrators should have the resources and the authority required to completely review all of the SQL queries. It is not advisable for them to fully trust the more experienced application programmers or just check the portions of SQL queries. Doing so, will definitely take more time of the DBA however it will also provide greater benefits in terms of avoiding troubleshooting performance issues in the long run. It is indeed quite hard to accurately test response time and performance in the development stage of the system since there is a significant gap between the real production environment and the development environment. 5. Conclusion: It is anticipated that the SQL optimization tools in the near future will become even more powerful, and the vendors of Database Management Systems will also include more advanced components in to their future releases. Meanwhile, the application developers should endeavor to make sure that they write efficient SQL queries during the development stage of the database management system. There are numerous resources available and the application developers of the databases should be exposed to and trained in the concepts of SQL optimization. According to Koopman, the database administrators should also provide their participation in the support line. Therefore, the database administrators should invest more time with the application developers, the users and the management rather than just fixing the after-the-fact issues in the SQL queries. Usually, if the problem occurs because of the poor SQL code then the following majors are to be adapted: Identify and monitor the database performance problems. Determine the area of the problem. Execute ‘explain’ or any other DBMS analyzer tool for the internal access path, and/or any third party SQL query analyzer tool and SQL optimizer tools. Re-write better SQL queries to fix the problem. 6. List of References DARMAWAN, B., Groenewald G., Irving A., Monteiro S., and Snedeker, A. “Database Performance Tuning on AIX”, IBM Redbook series, January 2003 251-286. Web. May 2012. . DUNHAM, J. “A guide to large-database tuning”, UNIX Review’s Performance Computing, San Francisco, May 1999, 35-41. KOOPMAN, J., “DBA Call to Action: Make an Impact”, Database Journal, 15 May 2003. Web. May 2012. . MARKL, V., Lohmann G. M., and Raman V. “LEO: An automatic query optimizer for DB2”, IBM Systems Journal, Armonk, Vol. 42, No. 1, 2003. ROB, P. and C. Coronel. “Database Systems: Design, Implementation and Management ”, Course Technology, 2002, 8-35. Read More