SQL UŽKLAUSŲ OPTIMIZAVIMO TYRIMAS
SQL QUERY OPTIMIZATION RESEARCH

Summary/Abstract: Fast improvement of information systems causes the increase of data quantity processed by them. Databases are an integral part of information systems that store billions of information system records. Given such quantity of data and taking into account its future growth it can be said that the time of information system operations increases not linearly but exponentially, whereas one of the most important information system indexes – speed – decreases. As users of information systems are eager to get the results of their queries as quickly as possible the programmers who create and upgrade information systems should assess all the parameters that have influence on information system speed, including the effectiveness of used SQL queries. Scientific literature studies different ways of SQL query optimization. Habimana (2015) analyzed SQL query optimisation optimising SQL codeCostel, Luca, and Teodor (2014) studied SQL optimization techniques offered by Microsoft SQL Server. Mithani, Machchhar, and Jasdanwala (2016) summarised SQL code optimization rules and suggested the model of SQL query converting into optimized query that ensures shorter time of SQL query execution. Bhajipale, et al. (2016) analyzed SQL query optimization designing more effective structure of data base, optimizing data base indexes and SQL code, analyzing query execution plans. Oktavia and Sujarwo (2014) explored the methods of internal SQL queries se and data base indexing strategy which makes positive impact on the duration of internal SQL queries. Besides, scientific literature provides a wide range of researches that study the effectiveness of NoSQL data base queries. Scientists who analyze the effectiveness of SQL queries emphasise that although the same results can be received making several different queries, a modern user needs only those queries that allow obtaining results in record-breaking time. The price of unoptimized query execution is very high (Gupta & Chandra, 2011). Ineffective SQL queries have a negative effect on the speed of business systems and reduce business effectiveness (Oktavia & Sujarwo, 2014).Research topicality and novelty. Although scientific literature studies different aspects of SQL query optimization the major part of researches is limited to the theoretical analysis of problems. There is a lack of researches that ground the effectiveness of SQL query optimization methods on the results of experimental testing. In light of this the aim of the current research is to assess the importance of SQL code optimization, data base indexes and structure in the creation of optimized SQL queries by means of experimental testing. The results of this research are important for the programmers of information systems, databases and other IT specialist who create information systems and analyze the data of databases. The results of experimental research can be used in high schools teaching databases. Research object is SQL query optimization.Research objective is to perform the research of SQL query optimization optimizing SQL code, using data base indexes and denormalizing data base structure.Research tasks: 1. To assess the impact of SQL code on the effectiveness of SQL query. 2. To ascertain how data base indexes influence the duration of SQL query execution.3. To assess the impact of data base denormalization on the effectiveness of SQL queries.Research methods: the impact of SQL code, data base indexes and data base denormalization on the effectiveness of SQL queries was assessed my means of experimental testing. The experimental testing was implemented in Oracle XE data base management system conducting tests in various in size databases, indexing them and denormalizing.The conducted research is important both in theoretical and practical aspects. Research results are important for data base programmers and other IT specialists who execute SQL queries. The results of the conducted research can be integrated into the data base subject teaching in higher schools grounding the importance of SQL query optimization and defining good practice of SQL code writing. The presented research method can be also used assessing the effectiveness of queries in NoSQL databases.Conclusions:1. During the assessment of the impact of SQL code on the effectiveness of query it was established that properly written SQL code reduces the duration SQL query execution by 85,0 percent. The duration of query execution is negatively influenced by internal queries, phrase HAVING and use of operator Distinct. The designation of query field in SELECT phrase can reduce the duration of query execution by 27,0 percent. 2. The conducted experimental research showed that the indexing of query fields used in calculations has negative impact on the duration of query execution. The indexing of query fields that form selection criteria is effective only when selection query returns less than 10,0 percent of data base records.3. The conducted experimental research showed that the denormalization of data base has positive impact on the effectiveness of SQL queries. Denormalizing data base on purpose to avoid connections in SQL query use it is possible to reduce the time of query execution by 89,0 percent.

• Details
• Contents