Obtaining data for the site from 1C: Enterprise (using the example of order statuses Trade Management 11.5)

Formulation of the problem: receive order data from the 1C: Enterprise database in JSON format for display on the website. Requests should be processed as quickly as possible, without interfering with the work of users in the database.

The task, of course, is far-fetched, but it well reflects a whole class of problems from the real world – servicing a large number of small requests for reading data. Processing at maximum speed, because no one likes it when the site slows down. And search robots too. They can lower the site in the results for this. At the same time, local users must also somehow work in the database, enter documents, and generate reports.

A lot has been written about direct access to data on a SQL server. But no one gives practical results of measurements. Let's try to assemble a test stand and measure it.

Host (he will also act as a client):

• Intel(R) Core(TM) i7-7500U CPU @ 2.90 GHz, RAM 16 GB

• Windows 11 Pro 22H2

• Apache JMeter 5.6.3

• Oracle VM VirtualBox 7.0.14

Virtual machine (server):

• 2 processors, 6 GB RAM

• Windows Server 2022 Standard 21H2

• SQL Server 2022

• Platform 1C, 8.3.24.1467

• 1C: Trade Management, 11.5.16.97

• Kraxe 0.3.3

Let's consider 2 solution options – using HTTP services built into the platform with publishing to IIS and directly reading data from SQL. We implement the first option as an extension to the configuration. For the second we use Kraken – a web server for MS SQL.

Configuration extension

First, let's prepare a small extension of the 1C configuration, which implements the HTTP service. The service receives the order number as a parameter. It finds a document by number and returns this number with date and status. In the request, we will take into account that there may be orders with the same number in the database. For simplicity, we will return the data of the latest order.

Extension for 1C configuration

We publish the configuration on IIS with default settings. An important point for performance analysis is that debugging is disabled on the 1C server.

The demo database included in the delivery set contains half a hundred “Customer Order” documents. In most of them, the NumberAccording to Client Data and DateAccording to Client Data fields are empty. Let's fill the number with values ​​from 01 to 58, and the date with the date of the document. During the test, we will request not just one document, but an arbitrary one from this range.

Open the publication address from the browser on the client:
http://192.168.100.10/trade_11/hs/dds/order?Id=33

Everything is fine, the service provides the data. Now you can configure the second option – direct queries in SQL.

Setting up Kraken

Free version Kraken has some limitations – no more than 100 rows as a result of a query, but for tests and many real-life tasks it is quite suitable. Download, unpack, install as a service:

c:\kraxe>kraxe.exe -install

Server settings are stored in the kraxe.conf file. Let's open it and make some changes.

kraxe.conf

First in the section http_server you need to specify the IP address and port on which the server will accept incoming requests. To avoid interfering with IIS and published 1C, we will specify the port as 8080. By default, this port is closed by a firewall. Run firewall.cpl and add a rule that allows incoming requests on this port.

A list of service users is generated in the same file. Let's remove the entry that allows anonymous authentication. Let's leave one entry for the Admin user.

In chapter sql_server Let's set connection parameters to SQL Server. Kraken connects to SQL Server via TCP. Let's make sure that this protocol is enabled in the SQL server settings.

SQL Server Configuration Manager

Let's go back to the Kraken settings. Database name, login, password – everything is obvious here.

Parameter pool_size – the maximum number of simultaneous connections to the SQL server that Kraken can open and support. Looking ahead, I note that increasing this parameter in my test did not affect the speed of request processing in any way. It is likely that if the queries are more complex and the processing speed on the SQL side is slower, then multiple connections will be needed to reduce the queue.

Parameter date_offset – date offset specific to 1C databases. You can read the details Here. The point is that 1C, when recording dates, can add 2000 years to them. This must be taken into account when reading data directly from the database.

In chapter bindings We will describe only one resource – “/order”. This resource will be associated with the ./queries/order.sql file. The request will have one parameter with type number (I16) and a default value of 0. When accessing a resource /order?Id=33the SQL query will set the value of the variable @ID = 33.

Chapter parameters delete from the settings file. We won't need it for now.

Now we need a file with a request that receives customer order data by order number. Let's launch SQL Server Profiler. We make a request from the browser to the HTTP service that we configured earlier and find a request similar to ours:

exec sp_executesql N'SELECT TOP 1
T1._Fld10563,
T1._Fld10564,
T1._Fld10547RRef,
T1._IDRRef
FROM dbo._Document1092 T1
WHERE ((T1._Fld3035 = @P1)) AND ((T1._Fld10563 = @P2))
ORDER BY (T1._IDRRef) DESC',N'@P1 numeric(10),@P2 nvarchar(4000)',0,N'33'

To compare table names from a query with 1C: Enterprise data, you need to obtain a data storage structure in the database. There are many ready-made treatments for 1C. For example, “Data storage structure“

Indeed, table _Document1092 is the Customer Order documents. Field _Fld10563 is NumberAccording to Client Data, _Fld10564 is DateAccording to Client Data, _Fld10547RRef is Status, and _Fld3035 is Data AreaMainData.

Let's rewrite the request. We do not use data division by areas, so one condition can be excluded from the request. If versioning is enabled for documents selected by a query in 1C, then the _Version field must be taken into account in the query. There will be a timestamp by which you can sort versions of one document. In our case this is also not relevant.

Transfers in the 1C database are stored in a special way (more about this is written here). To match enumeration identifiers with their representations, you need to upload the configuration into xml files (from the configurator).

Open the file ..\Enums\Customer Order Statuses.xml

Statuses of Customer Orders.xml

Be careful! The identifiers in the upload have a different byte order.

Now we can write a query and save it to the file ./queries/order.sql

SELECT TOP 1
  T1._Fld10563 as Id,
  T1._Fld10564 as Date,
  CASE T1._Fld10547RRef
    WHEN 0xa1675473ecec326649b4b85516d451ca THEN 'НеСогласован'
    WHEN 0xb58f438fd9fb9fcb4711d9e217f46797 THEN 'КОбеспечению'
    WHEN 0xb2d15805b9177e7845e991dfb027cbd4 THEN 'КОтгрузке'
    WHEN 0xb0e8c70f8e9e2b96440ba5356e7c22c2 THEN 'Закрыт'
  END as Status
FROM dbo._Document1092 as T1
WHERE T1._Fld10563 = @ID
ORDER BY T1._IDRRef DESC

The address of the second service for testing direct requests was as follows:
http://192.168.100.10:8080/order?Id=33

Let's start the Kraxe service and move on to tests.

Performance testing

For testing, I configured Apache JMeter on the client: 100 threads are launched within 100 seconds and each of them sends 10 requests.

Thread Group

Each request randomly selects an order number from 1 to 58 and inserts it into the URL.

Random Variable

For each service we will perform 5 cycles. For analysis, the most important 3 indicators will be: average request processing time, median time, deviation from the average time (scatter of values).

Results:

conclusions

The test is undoubtedly synthetic. The parallel work of users in the database and different load profiles for different databases are not taken into account. Well, except that background jobs remained enabled on the 1C server. But in general, the order of the numbers and the trend are obvious. Using direct queries allows you to increase the speed of retrieving data from the database by 2-3 times and improve the responsiveness of the site.

I would like to point out the serious disadvantages of directly obtaining data:

1. Complexity of development. The HTTP service built into the platform is written in half an hour and fits entirely on one screen. To prepare the text of an SQL query, you need to find out the structure of the database tables and have a good understanding of the features of data storage (for example, enumeration). The resulting solution is nailed to a specific database, because the table names for a similar configuration in another database will be different.

2. Difficulty maintaining the solution in working order. During the process of finalizing and updating the configuration, the names of tables and fields in tables may change. This needs to be taken into account and monitored.

3. 1C Company considers direct access to the database a violation of the license agreement.

The decision to use direct database queries must be made taking these shortcomings into account. If you can do it with standard tools, do it with platform tools.