How to increase the speed of 1C 100 times by direct access to MSSQL
There was a task to mark the documents for deletion for 1 year. This operation is performed before the traceless deletion and includes setting a mark and deleting the movement on the registers. Trial removal by regular means of one month took 4 hours. This meant that 12 months would be removed 48 hours (2 days). Looking ahead, I will say that direct access to 1C documents are removed in 30-40 minutes. The call to MSSQL was done through the .Net framework and the .Net Bridge component.
The structure of the 1C database is very complicated and consists of names that are of little importance to a person. 1C contains the function of determining the storage structure by object name. The development is based on this function GetStructureDataBase storage, which, according to the Russian name, returns the structure description. In this structure, there are 2 important fields for the Assignment, which must be equal to “Main”, and the name of the table TableTableStore.
')
The _YearOffset table contains a number indicating the year offset of the dates. It takes the value 0 or 2000. So with an offset of 2000, the date 01/01/2014 will be stored in the database as 01/01/04014. Accordingly, when selecting by date (removal occurs over a period of time), the offset must be taken into account. The offset can be obtained with the following code 1C:
Having the names of the document tables and knowing that the _Date_Time, _Marked and _Posted fields are responsible for the date, deletion mark and marking, respectively, you can mark all of them for deletion using a single SQL query. This is done like this:
In spite of the installation of a mark for deletion of documents, duplicate marks of deletion of each document are stored in document journals. The list of journals where the document participates can be obtained from the metadata of the document as follows: Metadata. JournalsDocuments
The mark for deletion through the _Marked and _Posted fields occurs similarly through the command:
When deleting documents, 1C removes the movements of the document in registers. In the case of direct access, these movements need to be removed independently. The list of registers can be obtained through the metadata DocumentMetadata. Movements.
The command that removes the movements is as follows:
As it turned out, it is not so difficult to achieve 1C speeding up work by about 2 orders; it is enough to execute 3 types of commands. In the final processing, the logic is expanded by selecting documents by type, adding a timeout, adding a transaction, batch execution of commands.
Ps. List of problems and solutions:
1. Processing ignores documents where it is forbidden to conduct, for example, updating registry entries. In the adjustment of register entries, the removal of a document is connected with the removal of the activity of register entries.
2. The result of the deletion is not reflected in the exchange plans. It is solved by simultaneously launching processing in the associated databases.
3. Does not affect table totals. It is solved by recalculation of totals through Configurator-Testing and Correction-Recalculation of totals.
The processing itself can be downloaded here:
TagDelectionsPrimeInquiry.epf (13,77 kb)
Defining MSSQL Table Names
The structure of the 1C database is very complicated and consists of names that are of little importance to a person. 1C contains the function of determining the storage structure by object name. The development is based on this function GetStructureDataBase storage, which, according to the Russian name, returns the structure description. In this structure, there are 2 important fields for the Assignment, which must be equal to “Main”, and the name of the table TableTableStore.
')
Determination of date offset
The _YearOffset table contains a number indicating the year offset of the dates. It takes the value 0 or 2000. So with an offset of 2000, the date 01/01/2014 will be stored in the database as 01/01/04014. Accordingly, when selecting by date (removal occurs over a period of time), the offset must be taken into account. The offset can be obtained with the following code 1C:
= sqlConnection.CreateCommand(); .CommandText = "select top 1 Offset from _YearOffset"; .CommandTimeout = timeout; = .ExecuteReader(); .Read() = .GetInt32(0); ; .Dispose(); Setting marks on the removal of documents
Having the names of the document tables and knowing that the _Date_Time, _Marked and _Posted fields are responsible for the date, deletion mark and marking, respectively, you can mark all of them for deletion using a single SQL query. This is done like this:
= sqlConnection.CreateCommand(); .CommandText = "UPDATE " + . + " SET _Marked = 0x01, _Posted = 0x00 WHERE _Date_Time BETWEEN @StartDate AND @EndDate"; .Parameters.AddWithValue("@StartDate", StartDate); .Parameters.AddWithValue("@EndDate", EndDate); .ExecuteNonQuery(); .Dispose(); Setting marks for deletion in document logs
In spite of the installation of a mark for deletion of documents, duplicate marks of deletion of each document are stored in document journals. The list of journals where the document participates can be obtained from the metadata of the document as follows: Metadata. JournalsDocuments
The mark for deletion through the _Marked and _Posted fields occurs similarly through the command:
.CommandText = .CommandText + "UPDATE " + + " SET _Marked = 0x01, _Posted = 0x00 WHERE _DocumentRRef IN (SELECT _IDRRef FROM " + . + " WHERE _Date_Time BETWEEN @StartDate AND @EndDate);" Deleting register movements
When deleting documents, 1C removes the movements of the document in registers. In the case of direct access, these movements need to be removed independently. The list of registers can be obtained through the metadata DocumentMetadata. Movements.
The command that removes the movements is as follows:
.CommandText = .CommandText + "DELETE FROM " + + " WHERE _RecorderRRef IN (SELECT _IDRRef FROM " + . + " WHERE _Date_Time BETWEEN @StartDate AND @EndDate);"; Conclusion
As it turned out, it is not so difficult to achieve 1C speeding up work by about 2 orders; it is enough to execute 3 types of commands. In the final processing, the logic is expanded by selecting documents by type, adding a timeout, adding a transaction, batch execution of commands.
Ps. List of problems and solutions:
1. Processing ignores documents where it is forbidden to conduct, for example, updating registry entries. In the adjustment of register entries, the removal of a document is connected with the removal of the activity of register entries.
2. The result of the deletion is not reflected in the exchange plans. It is solved by simultaneously launching processing in the associated databases.
3. Does not affect table totals. It is solved by recalculation of totals through Configurator-Testing and Correction-Recalculation of totals.
The processing itself can be downloaded here:
TagDelectionsPrimeInquiry.epf (13,77 kb)
Source: https://habr.com/ru/post/264687/
All Articles