SQL UŽKLAUSŲ OPTIMIZAVIMO TYRIMAS

SQL query optimization research

Abstract

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 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.

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.