Abstract
Query optimization is an important part of operation of any application interacting with databases. The most effective method to accelerate the search queries is to use index structures of different kinds. It is known that queries, which use the indexes (clustered and not clustered), are effective, in this case, when columns contain low percentage of repeating values. If indexable data are not selective, use of the majority of types of indexes is not effective. The main objective of the performed work is to extend the opportunities of technologies in the environment of SQL Server MS for creation of indexes and to increase the productivity of search queries. To achieve this purpose, the research group developed a Net-application in a programming language of the high level C# using an algorithm of formation of bit scales for processing of columns of relational tables with a large count of the duplicated values. The article offers a review of the main existing methods increasing the efficiency of execution of requests and also the types of the index structures used in different database management systems. The article contains some examples of application actions in selection of values with bit indexes. Testing of the created software product on tables with different cardinality makes it possible to draw a conclusion about the considerable saving of time of data handling in case of using bit indexes in comparison with other search algorithms.
Keywords
SQL query optimization, index of data, binary index, dictionary of data.
1. Stain D.А., Hasovskih V.P. Methods of optimization and increase in access efficiency to data in management information systems of organization. Fundamentalnyie issledovaniya [Fundamental research], 2014, no. 12-10, pp. 2114-2119. (In Russian)
2. Optimization of productivity of execution of queries (SQL Server Compact). Microsoft. Developer Network [Digital resource]. Access mode: https://msdn.microsoft.com/ru-ru/library/ms172984. aspx. The title from the screen. (Date of the address: 10.10.2017).
3. Phrolov К.М., Knjazev V.N. Query tuning to databases on the basis of the index mechanism. Sbornik nauchnykh statey XII mezhdunarodnoy nauchno-tekhnicheskoy konferentsii «Novye informatsionnye tekhnologii i sistemy» [Collection of scientific articles of the XII international scientific and technical conference "New Information Technologies and Systems"], 2015, pp. 201-204. (In Russian)
4. Typical reasons of no optimal operation of queries and methods of optimization // Information and technological attending of users 1C: Enterprise [Digital resource]. Access mode: https://its.1c.ru/db/metod8dev#content:5842:hdoc. The title from the screen. (Date of the address: 10.10.2017).
5. Optimizing Database Structure [Digital resource]. Access mode: https://dev.mysql.com/doc/refman/5.7/en/optimizing-database-structure.html. The title from the screen. (Date of the address: 10.09.2017).
6. Petuhov I.S. Algorithm of determination of necessary indexes for query tuning with connection of two tables in MYSQL (INNODB) DBMS. Nauchnyiy vestnik GosNII GA. [Scientific bulletin of the state scientific research institute of civil aviation (GosNII GA)], 2017, no. 16, pp. 98-107. (In Russian)
7. Riggs S., Krossing X. PostgreSQL 9: Administration Cookbook. BIRMINGHAM – MUMBAI, Packt Publ., 2013, 368 p.
8. Tarasov С.V. SUBD dlya programmista. Bazy dannykh iznutri [The DBMS for the programmer. Databases from within.] Moscow, Solomon-Press Publ., 2015, 320 p. (In Russian)
9. Tumanov V.Е. Proektirovanie khranilisch dannykh dlya prilosheniy system delovoy osvedomlennosty [Design of data stores for applications of systems of business awareness] (Business Intelligence Systems). Moscow, Intuit Publ., 2016, 958 p. (In Russian)
10. SQL Server 2000[Digital resource]. Access mode: http://www.intuit.ru/studies/courses/68/68/lecture/2016?page=3. The title from the screen. (Date of the address: 10.10.2017).
11. Microsoft. Developer NetWork [Digital resource]. Access mode: https://msdn.microsoft.com/ru-ru/library/ms175049 (v=sql.120).aspx. The title from the screen. (Date of the address: 10.09.2017).
12. Barrie X. The Firebird Book: A Reference for Database Developers. Apress Publ., 2004, 1128 p.
13. Henderson K. The Guru's Guide to SQL Server Architecture and Internals. Addison – Wesley Publ., 2003, 685 p.
14. Microsoft academy: The distributed bases and data stores [Digital resource]. Access mode: http://www.intuit.ru/studies/courses/ 1145/214/lecture/5523?page=2 intuit. The title from the screen. (Date of the address: 10.10.2017).
15. Skit J. C# for professionals: programming subtleties. Moscow, Williams Publ., 2014, 610 p.