Query optimization is always relevant for database developers, and programmers who work with them. In the current database contains a component called the optimizer. It is necessary to consider his work and to choose possible ways to change or query the database so the optimizer to spend less time and resources to build a query execution plan and its implementation.
There are several different approaches and query optimization methods, but the priority is the optimization of the lexical level of the SQL language to analyze the text of the query to identify redundancy.
Organization of educational process is a global challenge in the university, which consists of a group of components and requires a lot of resources and time. It is very important task to automate this process.
It is also an important issue in the framework of the existing ACS is the optimization of the development phase of queries.
The scientific novelty of this work is to improve the effectiveness of existing query optimization algorithms.
The practical part consists in the development a program of Automated load of department throughout the University.
The main objective in this project is to develop a computer program for the current ACS of university realizing the formation of the total load in the department.
The list of objectives that must be done within the framework of the master's work:
1) Develop a plan of automation process of formation of the load of department;
2) Development of software structure;
3) The development of the database structure;
There are many types, and query optimization techniques in research which involved a number of users and programmers working with databases.
3.1 Overview of international sources
In the world there are many works and publications on query optimization. Publications on the subject of automated department load has not been found.
Publication Ionnidis Y. E. Query Optimization // The Computer Science and Engineering Handbook [2].
Publication Jarke M., Koch J. Query Optimization in Database Systems [5].
Publication Chaudhari S. An Overview of Query Optimization in Relational Systems [6].
3.2 Overview of national sources
In Russia and Ukraine many scientists engaged in research on query optimization.
In the development of automated systems forming the load of the department has several research publications and Russian authors. As an example of mathematical modeling and laboratory information systems (MMIS). This laboratory has been developing software for the automation of the educational process of higher educational institutions. It was developed the automated system "Load university", which provides a comprehensive approach to the formation and distribution of the teaching load [9].
3.3 Review of local sources
Students who are part of Donetsk National Technical University has been developing in the field of automation of the process of formation of department load has not been found, but there are students who are engaged in research on query optimization.
Master's DonNTU Work Zaslavsky V.A. Система оптимизации клиентских запросов к серверам распределённой базы данных [12].
A fundamental element in the formation of the educational process in the following semester curriculum development is the formation of the load on the department (Figure 1).
Figure 1. Schematic of the formation of the educational process in the semester.
4.1 Study of the formation currently load
Currently, each department forms a load for a semester only within the framework of the faculty on the basis of the information of its own curriculum in a local database (Fig. 2). The planned program of work of the department is to minimize the formation of a consolidated load and automate this process.
Figure 2 - The process of forming the load of department at the moment
(animation: 6 frames, 5 cycles repeat, 51 KB)
4.2 Set the necessary functions of your application
Was found that the program should include the following functions:
1) Authorization and identification software user's role and associated permissions and a set of functions;
2) Selection of necessary disciplines for the university as a whole in each department of the curriculum of the collection stored on a central server according to specified parameters (the department code academic year, semester, etc.);
3) Implementation of the groups combining functions into streams according to the established procedures at the university;
4) The calculation of the planned number of bets in the department, depending on the load generated;
5) Saving the results of a database;
6) Load Visualization in the department in the form of web-based applications;
7) Formation of output reports the load of the department;
4.3 The choice of programming language, development environment and the database for the current project
Because the development will be part of the existing ACS, then as the database will be used - Microsoft SQL Server 2012. Desktop version of the software should be developed in the C # programming language, because Microsoft provides a handy tool and environment for developing applications in this language (Fig. 4). Also, C # language used is easy to associate with the database, without having to connect third-party products or libraries.
5.1 Research work on the standard optimizer example MS SQL Server
Optimizer [1] — a subcomponent of the database management system, which takes the input argument as the text of the query request, and considering the possible execution of strategy, and then chooses the most effective. The strategy and the plan will comply with the request. The primary goal of the optimizer is a selection of the most efficient query execution plan. This task is divided into four main stages, which are presented in Figure 3.
The primary goal of the optimizer is a selection of the most efficient query execution plan. This task is divided into four main stages, which are presented in Figure 3.
1) Parsing;
2) Query compilation;
3) Query optimization;
4) Query execution.
Figure 3. The main stages of query processing.
5.2 The study of the main methods of lexical query optimization
To perform lexical optimization [2, 3] queries, the only source of information is the query text as lexical structure, and other details about the database and its structure in the analysis are not used. The process of optimization of the lexical analysis includes query restrictions, comparing it contains conditions to identify redundancy.
Lexical optimization can have 3 different ways [4]:
1) Reduction of the request;
2) Improvement;
3) Conversion of the request;
5.2.1. Lexical optimization of queries by reducing
Reducing query is the most common and involves an increase in conciseness and reducing sampling conditions while maintaining semantic integrity. If we consider that each condition in the query requires a certain overhead of processing, any semantic repetition in limiting the sample lead to increased delays in processing a request. Similarly, the performance of identically false or truthful conditions increases the cost of the system to process and execute the query. Accordingly, the removal of such cases of redundancy at the stage of writing the request to its compilation and execution can significantly optimize the query.
5.2.2. Lexical optimization by improving query
Query enhancements technology is based on the increasing complexity of the query structure, the inclusion of a new table expressions, the use of which reduces the costs of processing the initial request. Most of the algorithms that solve this problem, refer to the technology "magic sets".
Overwrite the original query using these filters comprises the following basic steps:
— Analysis and search for non-optimal connections within the framework of the request;
— The creation of additional (magic) tables;
5.2.3. Lexical optimization by converting queries
For conversion technologies should be attributed algorithms queries standardization, bringing them to a universal standard form for the purpose of further processing. The most common type of query transformation is the transfer of logical conditions down the tree request. An important aspect is to move the selective operations less meaningful operations such as union and Cartesian product to perform a selective operation as soon as possible, as it takes less resources.
When you multiply the sample data of a large volume of relational databases, query optimization question is very relevant, since a large load runtime server requests and resource costs may play a greater role.
In the study on the example of a standard optimizer Microsoft SQL Server identified the main features and how it works.
1. Microsoft SQL Server 2012. Руководство для начинающих: Пер. с англ. – СПб.:БХВ-Петербург, 2013, - 816 с.: ил.
2. Ionnidis Y. E. Query Optimization // The Computer Science and Engineering Handbook. Boca Raton: CRC Press, 1996. Pp. 1038-1054.
3. Зверев Д. Л. Оптимизация потоков простых SQL-запросов: Диссертация кандидата технических наук: 05.13.11. Санкт-Петербург: Санкт-Петербургский Государственный Университет Аэрокосмического Приборостроения, 2005. 169 сс.
4. Обзор развития методов лексической оптимизации запросов. Труды Института системного программирования РАН Том 23. 2012 г. Стр. 195-214.
5. Jarke M., Koch J. Query Optimization in Database Systems // ACM Computing Surveys (CSUR), 1984. March, Volume 16, Issue 2. Pp. 111-152.
6. Chaudhari S. An Overview of Query Optimization in Relational Systems // Proceedings of the seventeenth ACM SIGACT-SIGMOD-SIGART symposium on Principles of database systems. New York: SIGMOD, 1998. Pp. 34-43.
7. Graefe G. Query Evaluation Techniques for Large Databases // ACM Computing Surveys, 1993. Volume 25, Issue 2. P. 73-169.
8. Ozsu M. N., Valduriez P. Principles of Distributed Database Systems. Second Edition. New Jersey: Prentice Hall International, 1999. 666 pp.
9. Автоматизированная система «Нагрузка ВУЗа» [Электронный ресурс] // URL: http://www.mmis.ru/Default.aspx?tabid=170 (дата обращения: 03.05.2015).
10. Гаврилец Е.З., Медведева О.А. АВТОМАТИЗИРОВАННАЯ СИСТЕМА ФОРМИРОВАНИЯ УЧЕБНЫХ ПЛАНОВ И РАСПРЕДЕЛЕНИЯ УЧЕБНОЙ НАГРУЗКИ ПРЕПОДАВАТЕЛЕЙ КАФЕДРЫ ВУЗА // Современные наукоемкие технологии. – 2007. – № 2. – С. 40-41;
11. Каюгина С.М. Автоматизированная система расчета и распределения учебной нагрузки преподавателей кафедры вуза на базе платформы «1С» [Электронный ресурс] // Современные научные исследования и инновации. 2015. № 11, URL: http://web.snauka.ru/issues/2015/11/58943 (дата обращения: 03.05.2015).
12. Заславский В.А. Система оптимизации клиентских запросов к серверам распределённой базы данных. [Электронный ресурс] // Портал Магистров ДонНТУ, 2011г. URL: http://masters.donntu.ru/2011/fknt/zaslavskiy/ (дата обращения: 08.05.2015).
13. Стародубцев М.В. Исследование методов доступа к данным MS SQL Server. [Электронный ресурс] // Портал Магистров ДонНТУ, 2008г. URL: http://masters.donntu.ru/2008/fvti/starodubtsev/masterwork/index.htm (дата обращения: 08.05.2015).
14. Бабич К.К. Оптимизации для высоконагруженных реляционных БД и альтернативные решения. [Электронный ресурс] // Портал Магистров ДонНТУ, 2013г. URL: http://masters.donntu.ru/2013/fknt/babich/index.htm (дата обращения: 08.05.2015).
Русский
Українська