Question: Rebuilding the Index for 1C

Good afternoon, I have a SCP database in SQL 2014, there are routine tasks:
1)
2)
Everything worked perfectly at first, nothing slowed down, a month and a half passed and the brakes started. I reinstalled 1C, SQL, 1C server in the hope that I had configured something incorrectly, but the problem remained. I looked at the report

I see fragmentation, it does not go away with some rebuilds and reorganization of the index. Are there any ways to restore normal operation and get rid of fragmentation?
(Maybe this is just bullshit that doesn’t affect the brakes?)

Answer: ()

Question: SQL and index fragmentation

When collecting statistics, it shows that more than a hundred indexes require rebuilding, their fragmentation is over 50%, but after rebuilding the indexes, the fragmentation remains the same, what can be done?

Answer: Okay thanks I'll take that into account

Question: Consequences of deleting an index

Good time days. There is a 1C database in SQL format, on which you need to do TII, which is not done, producing the following errors:

I cannot determine what data in 1C the above tables are associated with. What could be bad if I delete the above indexes using drop index ?

I'm not the author of the topic.
All commands are sent by 1c to the sql server, indexes are created and deleted.
foreign - if we do not create indexes through the configurator, but on the sql server ourselves.
the index created using the standard 1c procedure will not differ from the index created by the sql server

Question: How to correctly configure the regulatory “Updating the PPD Index” and “Merging the PPD Index” (Read 19

How to correctly configure the regulatory “Updating the PPD Index” and “Merging the PPD Index”?
Database size 160 GB. We are now set up. Both every hour. Is it appropriate to set "Update PPD Index" once an hour, and "Merge PPD Index" once at night?
Will it have time to merge it at night?

Answer:

The question has been removed. The regulations updated the index in portions. 10,000 records each. It did its job 10,000 times updated. But it turned out to be more than 10,000, so it turned out that the index was not relevant after all.

Question: Questions about indexes in 1s

In what cases is it better to use them? How much can the base swell with frequent use? what is the difference: indexes and indexes with additional. ordering.
You can also link to normal examples.
Thank you.

Answer:() Indexes can be larger size data :)
() It seems to me that the size of indexes is not their main disadvantage; what is more important here is that with “extra” indexes there are “extra” writing costs. IMHO.
But with large (hundreds-thousands of GB) databases, of course you need to look at the size :)

The general principle is that if there are selections for this field, we will index them; if there are no selections, we will not index them. If selection by field is needed, but the number of rows is small, an index is not needed. If selection by field is needed, but almost the entire table fits it, an index is not needed. IMHO.

Question: Value table indexes

Good afternoon.
There is a form with a table of values. Click the button to import data from Excel into this table.
When you first opened the form and clicked on the button, for example, 10 lines were loaded. For each row, indexes from 0 to 9 were automatically assigned. When loading again (without closing the form), I clear the table and add, for example, 5 more rows to it. The indices continue numbering and will be equal to 10-14. How can I reset the index counter and start it again?
Thank you

Answer:

You were right. That's not what I was looking for. I was sure that indexes and identifiers are one and the same. The question is closed. Thank you very much for your help

Question: Accessing the PM element by index

We were given a training task to sort the PM of a document not using a built-in function, but manually using some algorithm.
I'm wondering if it's possible to access a PM element by index?

Answer: The table part has the same meaning.
Copy-paste from SP:

Tabular section
Collection items:

Table row
For an object, it is possible to traverse the collection using the operator For each... From... Loop. The traversal selects the elements of the collection.
It is possible to access a collection element using the [...] operator. The row index (numbered from 0) is passed as an argument.

Question: after updating the platform, the reg task for updating the search index was disabled

installed 12.1616 on some servers everything is fine, but the server with consolidation began to glitch horribly, hosts fall out after 2...3 minutes...

I found out that the reg task “updating the full-text search index” was to blame for everything, while I disabled it...

At the same time, on standard logs the problem does not seem to appear

questions
1. who else has this problem?
2. how to look for a detailed reason (you don’t want to enable task reg on a production product, but you can’t on a test one)
3. what to do

Answer: I'm not a real welder, but on a test base I would turn off all task registries, except for "updating the full-text search index" and monitor it through the technical log.

Question: For SQL specialists, how do general details affect statistics on indexes?

Question: How labor-intensive will it be to cut out general data sharing details from the configuration? Is it even worth doing this?
BSP looked, in general it looks like there are not very many places there, but there was no time for in-depth study.
And now about the reasons why this interests me.
BP 3.0 database, size approximately 1 TB, of which 200 GB are accounting register tables, records from 50 to 250 million. In the tables on the server, the Data AreaMainData field is of type Numeric(7.0), so I won’t save space globally (about 9 GB now falls on this field).
The main goal is to speed up work with the database a little, due to:
*reducing the size of tables and indexes; * faster retrieval by reducing the size of the data row (and indexes) and more accurate query plans; *speed up the 'update statistics' command; *reducing the cost of maintaining column statistics (it may not be necessary to maintain them separately for some fields at all, since this field will be the leftmost one in the index).
A small speedup of a single row operation over millions of rows results in significant time savings. And in terms of a year - even more, so it seems to me that the goal is justified.

And a little theory on which I am based.
It is a well-known fact that MSSQL creates statistics only for the leftmost column of the index and stores 200 value distribution intervals there.
In standard accounting 3.0 indexes, the left column is “Data AreaMainData”, type Number. If separation is not used, then this field has zeros everywhere. Below is an example of such an index (accounting register). 49 million records, all collected in one line of the histogram.

The server creates column statistics for all fields that participate in requests

For example, according to the registrar

– Loading the bank classifier
This routine task downloads the classifier of Russian banks from the RBC website. Its regular work keeps this classifier up to date. And when we add another current account, there is a greater chance that the bank in which it is opened will be found by us in the BIC classifier.

This routine task loads exchange rates for the current date. If the program carries out currency transactions, then it makes sense to leave this task enabled so that you do not need to start loading currency rates manually each time.

– Filling in data to restrict access

This routine task performs sequential filling and updating of the data necessary for the operation of the “Access Control” subsystem in the mode of restricting access at the record level.

When record-level access restriction mode is enabled, fills sets
access values. Filling is performed in parts at each start until everything is
access value sets will not be populated.

When you disable record-level access restriction mode, sets of access values ​​(previously populated) are deleted when objects are overwritten, rather than all at once.
Regardless of the access restriction mode at the record level, it updates the cache details. After completing all updates and fillings, disables the use of the scheduled task.

The task is official. No need to enable it manually.

– Text extraction

Used to quickly search for data in attached files attached to a database. If you use search in attached files, then it makes sense.

– Messaging across workflow accounts

Regulatory task for automatic exchange with regulatory authorities. Used when submitting regulated reporting directly from 1C.

– Updating units

The scheduled task updates aggregates. What kind of animal is this?

Using aggregates, you can significantly speed up the generation of reports on accumulation registers in cases where the number of records in the register is hundreds of thousands, millions or more.

The key phrase here is “the number of entries in the register is hundreds of thousands, millions or more,” that is, for small registers, including aggregates does not make sense.

Aggregates allow you to create pre-calculated data for generating reports similar to the totals of accumulation registers. The latter are calculated automatically by the platform (provided that the use of totals for the register is enabled) in contrast to aggregates. But why are aggregates needed if the results perform a similar task?

Firstly, totals are calculated by month and this cannot be changed, while aggregates can be calculated by day, month, quarter, half-year and year.

Secondly, the sections of aggregates can be arbitrary (any composition of measurements of the accumulation register), in contrast to the totals, which are calculated using to the full staff register.

– Updating supervisor monitor data

The routine task causes the data in the information register “Executive Monitor Data” to be updated for all organizations. If the manager's monitor is actually used, the task makes sense.

– Update accountant tasks

The routine task updates and fills out the accountant’s tasks (dates for submitting various declarations, reports, etc.).

– PPD index update

Updates the full-text search index. If you use full-text search, the task makes sense. It is turned on automatically if full-text search is enabled in the database settings.

– Updating information on reporting directions

We are talking about directions: to the Social Insurance Fund, to the Federal Tax Service, to the Pension Fund. In short, something related, again, to the submission of electronic reporting from 1C.

– Processing subscriber applications for connection electronic signature in the service model

Some kind of service task that processes your application for connecting an electronic signature, if you use 1C in the service model. In general, you should definitely not enable it yourself.

– Delayed information security update

The job manages the execution of deferred update handlers. Do not enable it yourself.

– Sending subscriber reports

Sending regulated reports from service subscribers to regulatory authorities through the SOS “Kaluga-Astral” reporting service. Do not enable it yourself.

– Cleaning outdated versions objects

For official use only.

– Rebuilding of units

Rebuilding units for circulating accumulation registers. Do not enable it yourself.

– Recalculation of current values ​​of relative dates of prohibition of changes

Recalculates and updates current relative values
ban dates as of the current session date. Do not enable it yourself.

– Planning text extraction in the service model

Defines a list of data areas in which text extraction is required and schedules its execution for them using a job queue. Official.

– Receiving the results of sending reports

Receiving the results of sending reports from service subscribers to regulatory authorities from the SOS “Kaluga-Astral” reporting service. Official.

– Checking counterparties

For the service model, it updates the status of counterparties (is everything OK with their details). For local mode, updates states and records missing tax identification number and checkpoint.

– Merger of the PPD index

Performs a merge of full-text search indexes. The work of the task is again related to full-text search (where is search without an index).

– Delete irrelevant information synchronization

Performs deletion of synchronization information that was not deleted due to program failures. Files with a posting date of more than 24 hours are subject to deletion.

– Deleting marked program objects

Removes marked objects from a scheduled task.

– Setting the period of calculated totals

A service task establishing the period of calculated results. The results were written above.

Unfortunately, there is no normal documentation for routine tasks in 1C: Accounting. Therefore, I present the information that I managed to collect from open sources or configuration source code.

This routine task downloads the classifier of Russian banks from the RBC website. Its regular work keeps this classifier up to date. And when we add another current account, there is a greater chance that the bank in which it is opened will be found by us in the BIC classifier.

This routine task loads exchange rates for the current date. If the program carries out currency transactions, then it makes sense to leave this task enabled so that you do not need to start loading currency rates manually each time.

– Filling in data to restrict access

This routine task performs sequential filling and updating of the data necessary for the operation of the “Access Control” subsystem in the mode of restricting access at the record level.

When record-level access restriction mode is enabled, fills sets
access values. Filling is performed in parts at each start until everything is
access value sets will not be populated.

When you disable record-level access restriction mode, sets of access values ​​(previously populated) are deleted when objects are overwritten, rather than all at once.
Regardless of the access restriction mode at the record level, it updates the cache details. After completing all updates and fillings, disables the use of the scheduled task.

The task is official. No need to enable it manually.

– Text extraction

Used to quickly search for data in attached files attached to a database. If you use search in attached files, then it makes sense.

– Messaging across workflow accounts

Regulatory task for automatic exchange with regulatory authorities. Used when submitting regulated reporting directly from 1C.

– Updating units

The scheduled task updates aggregates. What kind of animal is this?

Using aggregates, you can significantly speed up the generation of reports on accumulation registers in cases where the number of records in the register is hundreds of thousands, millions or more.

The key phrase here is “the number of entries in the register is hundreds of thousands, millions or more,” that is, for small registers, including aggregates does not make sense.

Aggregates allow you to create pre-calculated data for generating reports similar to the totals of accumulation registers. The latter are calculated automatically by the platform (provided that the use of totals for the register is enabled) in contrast to aggregates. But why are aggregates needed if the results perform a similar task?

Firstly, totals are calculated by month and this cannot be changed, while aggregates can be calculated by day, month, quarter, half-year and year.

Secondly, the sections of aggregates can be arbitrary (any composition of measurements of the accumulation register), in contrast to the totals, which are calculated based on the full composition of the register.

– Updating supervisor monitor data

The routine task causes the data in the information register “Executive Monitor Data” to be updated for all organizations. If the manager's monitor is actually used, the task makes sense.

– Update accountant tasks

The routine task updates and fills out the accountant’s tasks (dates for submitting various declarations, reports, etc.).

– PPD index update

Updates the full-text search index. If you use full-text search, the task makes sense. It is turned on automatically if full-text search is enabled in the database settings.

– Updating information on reporting directions

We are talking about directions: to the Social Insurance Fund, to the Federal Tax Service, to the Pension Fund. In short, something related, again, to the submission of electronic reporting from 1C.

– Processing subscriber applications for connecting an electronic signature in the service model

Some kind of service task that processes your application for connecting an electronic signature, if you use 1C in the service model. In general, you should definitely not enable it yourself.

– Delayed information security update

The job manages the execution of deferred update handlers. Do not enable it yourself.

– Sending subscriber reports

Sending regulated reports from service subscribers to regulatory authorities through the SOS “Kaluga-Astral” reporting service. Do not enable it yourself.

– Cleaning up obsolete versions of objects

For official use only.

– Rebuilding of units

Rebuilding units for circulating accumulation registers. Do not enable it yourself.

– Recalculation of current values ​​of relative dates of prohibition of changes

Recalculates and updates current relative values
ban dates as of the current session date. Do not enable it yourself.

– Planning text extraction in the service model

Defines a list of data areas in which text extraction is required and schedules its execution for them using a job queue. Official.

– Receiving the results of sending reports

Receiving the results of sending reports from service subscribers to regulatory authorities from the SOS “Kaluga-Astral” reporting service. Official.

– Checking counterparties

For the service model, it updates the status of counterparties (is everything OK with their details). For local mode, updates states and records missing tax identification number and checkpoint.

– Merger of the PPD index

Performs a merge of full-text search indexes. The work of the task is again related to full-text search (where is search without an index).

– Removing irrelevant synchronization information

Performs deletion of synchronization information that was not deleted due to program failures. Files with a posting date of more than 24 hours are subject to deletion.

– Deleting marked program objects

Removes marked objects from a scheduled task.

– Setting the period of calculated totals

A service task establishing the period of calculated results. The results were written above.

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 it will allow queries to run quickly and reduce negative impact locks on .

What is an index?

Optimizing Index Placement

When the size of the tables does not allow them to “fit” in RAM servers, speed comes first disk subsystem(I/O). And here you can pay attention to the ability to place indexes in separate files located on different hard drives.

Detailed Description 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 a necessary index for a query means iterating through all the records in the table, which in turn leads to excessive 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 should also read small quantity lines.
• 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 of this 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 the index, we can roughly estimate using a free online service that shows the “query execution plan” and the indexes used.

Full text search- will allow you to find text information located almost anywhere in the configuration used. In this case, you can search for the necessary data either throughout the entire configuration as a whole, or by narrowing the search area to several objects (for example, certain types of documents or directories). The search criteria themselves can vary over a fairly wide range. That is, you can find the necessary data without even remembering exactly where it is stored in the configuration and how exactly it was recorded.

Full text search provides the following options:

• There is support for transliteration (writing Russian words using Latin characters in accordance with GOST 7.79-2000). Example: "Russian phrase" = "russkaya fraza".
• There is support for substitution (writing part of the characters in Russian words with single-key Latin characters). Example: “russrfz frapf” (the endings of each word are typed in Latin letters, for example, as a result of an operator error).
• There is a possibility of fuzzy search (the letters in the found words may differ) with indication of the fuzzy threshold. Example: by specifying the word “hello” in the search bar and a vagueness of 17%, we will find all similar words with and without errors: “hello”, “prevet”, “prived”.
• It is possible to specify the scope of the search for selected metadata objects.
• Full-text indexing of standard field names ("Code", "Name", etc.) is carried out in all configuration languages.
• The search is performed taking into account synonyms of Russian, English and Ukrainian languages.
• The morphological dictionary of the Russian language contains a number of specific words related to areas of activity that are automated using the 1C:Enterprise program system.
• As a standard, the supplied dictionaries include dictionary databases and dictionaries of thesaurus and synonyms of Russian, Ukrainian and English languages, which are provided by the Informatic company.
• The search can be carried out using wildcard characters ("*"), as well as specifying search operators ("AND", "OR", "NOT", "NEXT") and special characters.

Full-text search can be carried out in any configuration on the 1C:Enterprise 8 platform

To open the full-text search control window, do the following:

Regular application- menu item Operations - Managing Full Text Search.

Managed Application- menu item Main menu - All functions - Standard -Full text search management.

• Update index– Index creation/Index update;
• Clear index– resetting the index (recommended after updating all data);
• item Allow index merging– is responsible for merging the main and additional index.

Full-text search is carried out using a full-text index. Without an index, full-text search as such is not possible. For a search to be effective, all relevant data must be included in the full-text index. If the user enters new data into the database, it must be included in the index in question, otherwise it will not participate in the search. To avoid this, you need to update the full-text index. When updating, the system analyzes only certain types data: String, Reference type data (links to documents, directories), Number, Date, Value Storage. If the user does not have access rights to certain information, then he will not be able to see it in the search results. You should also remember that in the properties of the objects for which the search will take place the value must be set Full Text Search – Use, which is set by default.

How can you notice the property Use set for the entire directory Counterparties, but this can be done for each of its attributes of the corresponding type.

Let's take a closer look at the full-text index, which consists of two parts (indexes): the main index and the additional one. High speed Data retrieval is provided by the main index, but updating it is relatively slow, depending on the volume of data. The additional index is the opposite of it. Data is added to it much faster, but searching is slower. The system searches simultaneously in both indexes. Most of the data is in the main index, and data added to the system goes into the additional index. While the amount of data in the additional index is small, searching through it is relatively fast. At a time when the system load is light, an index merge operation occurs, as a result of which the additional index is cleared and all data is placed in the main index. It is preferable to perform index merging at a time when the system load is minimal. For this purpose, you can create regulated tasks and scheduled tasks.

Special operators allowed when specifying a search expression

The full-text search mechanism allows writing part of the characters of a Russian word using single-key Latin characters. The search result will not change.

Two operators NEXT

• simplified. 8 words apart
• NEAR/[+/-]n – search for data in one attribute at a distance of n-1 words between them.

The sign indicates in which direction from the first word the second word will be searched. (+ - after, - before)

The wildcard "*" can only be used as a substitute for the end of a word

Fuzziness operator "#". If the exact spelling of the name, name is unknown.

Software and 1C tools: programming.

Synonym operator "!" Allows you to find a word and its synonyms

How to programmatically update a full text search index?

Code 1C v 8.x Procedure UpdateIndices() Export
FulltextSearch.UpdateIndex();
End of Procedure

Full text data search example

Defining the SearchList Variable

Code 1C v 8.x Variable Search List;

In addition, in the procedure for processing the When Form Opens event, we will determine that this variable will contain a full-text search list, with the help of which we will search the data

Code 1C v 8.x Procedure OnOpen()
SearchList = FullTextSearch.CreateList();
End of Procedure

Now, for the event of clicking on the Find button, we will write code that will allow us to perform a search in accordance with the expression specified in the SearchExpression field

Code 1C v 8.x Procedure FindClick(Element)
SearchList.SearchString = SearchExpression;
Attempt
SearchList.FirstPart();
Exception
Warning(ErrorDescription());
EndAttempt;
If SearchList.FullQuantity() = 0 Then
Form Elements.MessageOfResult.Value = "Not found";
Form Elements.SearchResult.SetText("");
Otherwise
OutputSearchResult();
endIf;
End of Procedure

First in this procedure, we set the search term entered by the user as the search string for full-text search. Then we execute the FirstPart() method, which actually launches a full-text search and returns the first portion of results. By default, a chunk contains 20 elements. After that, we analyze the number of elements in the search list. If it does not contain a single element, then we display a corresponding message in the form. Otherwise, the DisplaySearchResult() procedure is called, which displays the results to the user.

Let's create a procedure with the same name in the form module and write the code in it:

Code 1C v 8.x Procedure OutputSearchResult()
Form Elements.ResultMessage.Value = "Displayed" + Row(SearchList.InitialPosition() + 1) + " - " + Row(SearchList.InitialPosition() +SearchList.Quantity()) + " from " + SearchList.FullQuantity();
Result = SearchList.GetDisplay(FullTextSearchDisplayType.HTMLText);
FormElements.SearchResult.SetText(Result);
ButtonAvailability();
End of Procedure

The steps in this procedure are simple. First, we generate a message about which elements are displayed and how many total elements were found. Then we get the result of the full-text search in the form of HTML text and display this text in the HTML document field located in the form.

Finally, we transfer control to the Button Availability() procedure in order to make available or, conversely, deny access to the Previous Portion and Next Portion buttons (depending on which portion of the results obtained is displayed). The text of this procedure is presented in the Code

Code 1C v 8.x Procedure Button Availability ()
Form Elements.NextPosition.Availability = (SearchList.FullQuantity() - SearchList.StartPosition()) > SearchList.Quantity();
Form Elements.PreviousPosition.Availability = (SearchList.StartPosition() > 0);
End of Procedure

Now you need to create event handlers for clicking on the PreviousPart() and NextPart() buttons.

Code 1C v 8.x Procedure Previous Portion Pressing (Element)
SearchList.PreviousPart();
OutputSearchResult();
End of Procedure
Procedure Next Portion Pressing (Element)
SearchList.NextPart();
OutputSearchResult();
End of Procedure

The final “touch” is to create an onclick event handler for the HTML document field located in the form. The fact is that the result of a full-text search, presented in the form of HTML text, contains hyperlinks to the numbers of elements of the search list. And we would like that when the user clicks on this link, the system would open the form of the object that is contained in this list element. To do this, we will intercept the onclick event of the HTML document contained in the HTML document field, get the list item number from the hyperlink and open the form of the corresponding object. The text of the onclick event handler of an HTML document field is presented in code

Code 1C v 8.x Procedure Search Resultonclick(Element, pEvtObj)
htmlElement = pEvtObj.srcElement;
// Check element id
If (htmlElement.id = "FullTextSearchListItem") Then
// Get the file name (search list line number),
// contained in the hyperlink
NumberInList = Number(htmlElement.nameProp);
// Get the search list string by number
SelectedRow = SearchList[NumberInList];
// Open the form of the found object
OpenValue(SelectedRow.Value);
pEvtObj.returnValue = False;
endIf;
End of Procedure