Today we will talk about MS SQL DBMS indexes and their internal structure. I will try to talk about indexes both from the point of view of the DBMS and from the point of view of 1C 8.3.
Indexes are a set of links ordered by certain columns, created to optimize the performance of the MS SQL DBMS.
Creating indexes implicitly:
The platform creates indexes itself using data keys known in advance for each metadata object (link, code, name, measurements, etc.)
Creating Indexes Explicitly possible in three ways:
- Setting the “Index” flag for a field (attribute/dimension). Option "Index with additional" ordering" adds the "Code" or "Name" field to the index (primarily for ).
- Adding a field to " ".
- Specifying the indexed field in the query using the “ “ construction.
Get 267 video lessons on 1C for free:
Indexes in Microsoft SQL DBMS
Indexes in the MS SQL DBMS are pages with data of 8 KB each. Despite the fact that indexes are designed to improve the performance of the DBMS, they have certain disadvantages - they take up disk space and slow down the DBMS's work on writing rows.
Types of indexes in MS SQL DBMS:
- Non-clustered indexes— such indexes do not rebuild tables, but only organize links.
- Clustered Indexes are needed to build a table in accordance with the index. The data is organized, for example, alphabetically. Invalid for frequently changing columns, because The DBMS constantly physically rebuilds the table using this index.
- Unique indexes- a kind of “superstructure” for clustered and non-clustered indexes. Such an index is unique based on key fields.
Types of keys in a DBMS:
- Primary key— a set of columns that uniquely characterize a row.
- Foreign key (foreign)— a table field that stores the primary key value for the purpose of organizing relationships between tables. 1C does not use this type of keys.
Important nuances of using indexes
Proper use of indexes can speed up queries not just by times, but by hundreds, sometimes even thousands of times.
This kind of acceleration simply cannot be achieved with hardware. Therefore, this topic needs to be given close attention.
Often, in order to speed up a query, you need to create your own index, and there are several different ways to do this.
In the video tutorials we will look at several ways to create an index. We will also consider a situation where an index of the required composition cannot be created using standard platform tools and will have to be created in the DBMS.
Setting up indexes using standard platform tools
The lesson shows what indexes are actually created for objects at the DBMS level.
Not everything in this topic is as obvious as it might seem at first glance. After all, for a number of objects there are features of creating indexes.
We'll look at all the details in this video.
Indexing with additional ordering
The video shows the difference between the index construction option Index from Index with additional ordering.
The example shows what kind of index the platform will build when using additional ordering.
Creating an Index for Register Dimensions
Indexing the first dimension of registers has several nuances.
The video shows what indexes are created for register measurements. The situation of indexing the first register dimension is also considered.
or
Why does a 1C developer need to “index” register dimensions and details?
- Well, you have requests! - the database said and hung...
The short answer to the title question is that this will allow queries to run quickly and reduce the negative impact of locks on .
What is an index?
Optimizing Index Placement
When the volume of tables does not allow them to “fit” in the server’s RAM, the speed of the disk subsystem (I/O) comes first. And here you can pay attention to the ability to place indexes in separate files located on different hard drives.
Detailed description of actions http://technet.microsoft.com/ru-ru/library/ms175905.aspx
Using an index from a different filegroup improves the performance of non-clustered indexes due to the concurrency of I/O processes and work on the index itself.
To determine the dimensions, you can use the above mentioned processing.
Impact of indexes on locks
The absence of the necessary index for a query means iterating through all the table records, which in turn leads to redundant locks, i.e. unnecessary records are blocked. Additionally, the longer a query takes to complete due to missing indexes, the longer the lock holding time will be.
Another reason for locks is a small number of records in tables. In this regard, SQL Server, when choosing a query execution plan, does not use indexes, but crawls the entire table (Table Scan), blocking the entire table. In order to avoid such blocking, it is necessary to increase the number of records in the tables to 1500-2000. In this case, scanning the table becomes more expensive and SQL Server starts using indexes. Of course, this cannot always be done; a number of directories such as “Organizations”, “Warehouses”, “Divisions”, etc. usually have few entries. In these cases, indexing will not improve performance.
Index performance
We have already noted in the title of the article that we are interested in the effect of indexes on query performance. So, indexes are most suitable for the following type of tasks:
- Queries that specify “narrow” search criteria. Such queries must read only a small number of rows that meet certain criteria.
- Queries that specify a range of values. These queries also need to read a small number of rows.
- A search that is used in linking operations. Columns, which are often used as bind keys, are great for indexes.
- A search in which data is read in a specific order. If the result set is to be sorted in clustered index order, then no sorting is needed because the result set is already pre-sorted. For example, if a clustered index is created on the columns lastname, firstname, and the application requires sorting by last name and then by first name, then there is no need to add an ORDER BY clause.
True, with all the usefulness of indexes, there is one very important BUT - the index must be “efficiently used” and must allow data to be found using fewer I/O operations and the amount of system resources. Conversely, unused (rarely used) indexes are more likely to degrade data write performance (since every operation that modifies the data must also update the index pages) and create excess database space.
Covering(for a given request) is called an index that contains all the necessary fields for this request. For example, if an index is created on columns a, b, and c, and the SELECT statement queries data from only those columns, then only access to the index is required.
In order to determine the effectiveness of an index, we can roughly estimate using a free online service that shows the “query execution plan” and the indexes used.
When describing the details of various objects in metadata, it is possible to set the Index property. This property allows the configuration developer to indicate to the system the need to build a separate index in the database for the corresponding details.
In addition to the "Index" option in this property, for most objects you can set the "Index with additional ordering" option. This option is intended primarily for use in dynamic lists.
In the "Index" option, an index is built directly based on the details. The index is also supplemented with a link to ensure a certain order of records in the index when the attribute values are repeated.
In the "Index with additional ordering" option, the index is built by attribute, as well as by some field that is usually used to order objects of this type. For a directory, the index, depending on the main representation, is supplemented with a code or name. And for a document, the index is supplemented with a date. This index is also supplemented by a link.
In the "Index" option, a dynamic list can provide efficient viewing of large volumes of information with ordering by this attribute, since the created index will be used for this.
The "Index with additional ordering" option in a dynamic list can provide effective viewing of large amounts of information with selection by the value of a given attribute and with ordering corresponding to the main ordering for a given object. In this case, the presence of an index that includes the attribute by which the selection is performed and the main ordering field will allow the system to use the index when viewing the list.
Of course, indexes also affect other ways of retrieving information (obtaining data using methods of object managers or queries).
Thus, when determining an option for the Index property, you should proceed from which options for retrieving information need to be optimized first. For example, if you need to view a list with selection by details, then it makes sense to use the “Index with additional ordering” option. And if the index is needed, for example, only for searching by querying objects by given details without ordering, then it is better to use the “Index” option so that the created index requires less system resources.
The composition of indexes generated by the system for various combinations of metadata properties is detailed in the article “Indexes of Database Tables”.
