The forums are constantly asking the same question: why 1C+MSSQL handles the requests slower than the file variant?
The usually the «flood» goes in a few dozen pages.
There are two popular «trends» in such forums — the first say that for the client-server variant this is normal, the file variant should always be faster, others say that 1С operates badly with DBMS.
Let’s try to find out.
We propose to divide a question into several ones:
1. Does the file variant work faster in the operations of «monopolistic type», when its activity does not depend on the other base users?
Under the «monopolistic type» we mean one active (operating) user in the information base.
2. Does the file variant work faster in the multi-user mode, when the users actively compete for resources (for example, when conducting the sales of goods, they access massively to the remains on the warehouse)?
3. How significant is the difference in speed between the file and client-server variant in terms of business?
What is in reality
Comparison of file and client-server variant of 1С
| Criterion | File database 1C | Client-server database 1C |
| Maximal size of one table | 4 gb | ~hundreds of terabytes |
| Database size in practice, when the problems of 1C performance arise | ~16 Gb | ~500-1500 Gb |
| Number of users with convenient 1C operation | 3-10 (the table locks prevent to make more) | 300-700 people (it is required to buy more powerful hardware and optimize the code again to make more) |
| Functions that consumes the resources that could be spent for better performance | no | transactional data integrity, operation logging for the further analysis, functions to improve the parallelism of user work |
| Additional advantages | simplicity (since there are a few functions) | data servicing (for example, backup) without interrupting the user work |
| Minimal area of locks | At the table level (less resources required) | At the record level (more resources required) |
| Cost of ownership (conditional) | Little | Significantly more than the file one |
| presence of intermediate layer between 1С client and DBMS | No | 1С Server |
The answer to the first question:
Does the file variant work faster in the operations of «monopolistic type», when its activity does not depend on the other base users — with probability of 99% the file variant works faster (subject to the fact that its capabilities are not limited by the poor hardware and the maximal capabilities of file variant are not achieved)!.
Do not take our word for it — check yourself. Create a single-thread test and make sure yourself (check firstly in the file variant, then in the client-server).
if you do not believe the test, then test the operation suitable for testing in you opinion both in file and client-server variant. We recommend to take as a basis any long and hard process.
Another intermediate question arises:
How much faster is the file variant than the client-server in numbers?
The answer to this question is more interesting and practical. Our practice shows:
- on the average operations with comparable data volumes almost 2 times faster
- on the average operations, when the data volumes are beginning to exceed the amount of available RAM, and increasing the intensity of swapping — up to 3-4 times faster
However, it is important to understand what is the «average» operation. It turns out that the operations that manipulate the data in memory in the client-server variant does not lose, but sometimes even outperform the file version! However, such operations are few and are not significant. Most of the load are the operations that access in fact the disk subsystem for reading and, most importantly, — for data writing.
And even innocuous report during preparation may also write data, for example, in the service database tempdb when using MS SQL Server.
When performing the query in the file variant, there is no data dealer like 1С Server, i.e. it is one segment of query passage less. It is logical that if to make, for example, an «operation without the dealers», it is always faster than the «operation with dealers». In addition, the significant part of functional in the DBMS side in fact is a «dealer» — it is required, for example, not only to execute the queries, but also to provide better parallelism for operation of other queries — for example, lock the used data in order not to lock the «extra» one like the file variant makes. Lock the whole table is easier, because this is a single record with the lock information, but the locks for thousands of rows — this is significantly more additional records and, more important, it consumes much more resources (processor, memory, and sometimes a disk space).
in other words, the client-server variant requires more resources that the file one for the same amount of work.
The conclusion — it is possible to do on the same computer more work IN MONOPOLISTIC MODE in file variant than in the client-server one (even in the monopolistic mode).
As a result, it looks like the client-server variant can do less work, required more resources, but where is the «profit», why is it used almost everywhere?
The second question of our article will help us to answer: does the file variant work faster in the multi-user mode, when the users actively compete for the resources (for example, when conducting the sales of goods, they access massively the remains on the warehouse)?
In the table №1 we see such significant drawbacks of file variant as a small size of databases — on the most of enterprises 1С databases take from tens to hundreds of gigabytes. But it is more important that the file variant make the redundant locks (unnecessary) that significantly decreases the possibility of parallel user work.
So, for example, 100 users of 1С work on the enterprise. Assume that each user enters per day evenly 10 documents and each table part contains 10 rows.
We get a simple arithmetic — 100 х 10 х 10 =10 000 rows are entered in the information system in the course of day.
For simplicity of understanding, we agree that each user works with unique data and other users do not intersect with each other neither in the table part of document nor in the composition of attributes.
It works in the client-server variant. The documents will be conducted in parallel.
Knowing the redundancy of locks for the file variant, let’s think about what would happen if 100 users in the file variant will enter at the same time in the system the first document in the day, but will click the conduction button simultaneously.
We know that the default duration of the lock timeout is 20 seconds. Theoretically, we can assume that, in addition to the first user, all other users will wait for each other 20 seconds and then conduct their documents. The total wait would be 100 users х 1 document х 20 seconds = 2000 seconds to wait. It’s half an hour of user inactivity.
in practice, everything is even sadder, the people are not the robots, they do not see when the system is blocked or the probability to conduct the document is high, so they are simply stating that it is impossible to enter data in the system because of the constant locks. Or more simply, in practice, the work of company in the system in the file variant will stop.
But if even we imagine that a startling programmer has come to the enterprise and wrote the program so that the attempts will be executed always automatically, this half-hour of idle time will not go away.
Moreover, with the attempt number 2,3 the documents will worsen the situation and during the day even with the ideal code the file variant will «accumulate» 100 users х 10 documents х 20 seconds = 20000 seconds ~ 5 with the half hour of idle time.
5 hours are the advantage of client-server variant. And it does not matter with which speed in each thread in the client-server variant will they be entered. it is more important that they are entered, and in the file variant at this time there are the idles on the redundant locks.
Since among the redundant locks there are the required locks, we formulate the concept of performance again.
In terms of business the performance is the amount of work done for the day by all 100 users, instead of a one user exclusively. Therefore, it is more important for the business how many data will be entered in the total in the system by all the users. If to evaluate the performance of collective work — the file variant loses in the tens to hundreds times a client-server variant.
And we urge you once again take our word for it. Develop your own multi-user test and make sure yourself the reliability of our statements.
Now the answer to the third question:
How significant is the difference in speed between the file and client-server variant in terms of business?
The file variant slightly advances the client-server variant in the monopolistic mode and very significantly loses in the multi-user mode.
But we must understand that the business has its own tasks that almost always have the higher priority, namely, fault tolerance, uninterruptible work, reliability and stability. The server operation in the fail-tolerant cluster required the additional charge of data mirroring. Thus, it must be always the balance between different tasks: performance, reliability, security, etc.
The file variant does not have the mechanisms of data integrity control. For example, if a failure occurs in the network when transferring the data or the light will be turned off, the something will have time in the file variant to be saved and something not. The data integrity will be destroyed. In the client-server variant in such cases the rollback of incomplete transaction will happen, and the incomplete data will not fall into the system, the data integrity will be preserved.
That is, not only the higher number of users in the system, the greater the file variant will lose the client-server one, but also the procedures of data recovery in case of failure transform the file variant to the absolutely losing variant.
And now we have to ask a «correct question»:
Why the question arose to evaluate the difference in speed of file and client-server variant?
The same correspondence and flood on the forums begin with the fact that the questioner has the problems with performance in the client-server variant.
But instead of studying the reasons that cause the problem in the client-server variant, he discovers that in the file variant there is no such problem. he is not worried that the problem can be in the «dealer» which is not available in the file variant.
The correct answer is that no matter how much faster is the file or client-server variant, but it is important what exactly causes the slowdown in each SPECIFIC case.
Investigating the performance problems, you should decompose the hard operation in the form of a list of smaller operations in the system which together form this performance. And the solution of problem should be started with the one that creates the greatest contribution in the slowdown.