Databases and subd. Databases and Subds Main Functions of a Relational Subd

The financial aspect of the implementation of 1C on ORACLE

The first question that I would like to consider is the financial aspect of implementing 1C on ORACLE. The majority has the opinion that the implementation of 1C on ORACLE is very expensive, and that, in principle, if the project uses the ORACLE DBMS, then the cost of the project will increase by an order of magnitude. This is not an entirely unfounded opinion, however, I would still like to understand in more detail what the cost of the project will be, if the ORACLE DBMS is chosen for its implementation.

The first is, of course, licenses. Since I am not a licensing expert, I just typed ORACLE and MSSQLServer on the Internet, looked up the cost of licenses for one person (not by SOCKET, not by memory, not by servers - but in the most simplified version) and got approximately the same amounts. As we see, one licenseORACLE is even cheaper than a similar licenseMSSQL. For comparison, I use the StandardEditionONEORACLE and StandartEditionMSSQLServer editions, because these are the initial editions, and 1C gives us most of the "features" that are in Enterprise versions, prohibits to use.

OC per server forORACLE is completely free(shareware, of course) - itLinux. The fact is that ORACLE is a product that was originally developed under LINUX, and it was originally "Linux" - look at the directory structure, at a bunch of configuration files, at the JAVA interface - you will immediately notice it. For Microsoft, this is, respectively, WindowsServer (we have no other option), but even in the StandardEdition version, it still costs 1000 rubles.

The server itself (hardware) - of course, will make up a large share of the project cost, but to compare the cost of implementing a DBMS, we will not worry about its cost.

But last paragraph(presence of DBA - ORACLEDBA or MSSQLDBA) - it is the most interesting. If we are talking about implementation on MSSQL, then most of them had implementations in which it was possible to do without a DBA, since MSSQLServer is a product that is not so complicated, it has good usability, good Microsoft methodology, with its administration it is more or less possible figure it out yourself. ForORACLE- on small projects, of course, there is a chance - for big projects(from 100 users), of course, already you need a separate person who will monitor the work of the DBMS who will administer its work, will manage it. Graphic tools DBMS administrationORACLE is not as powerful, not as beautiful and not as wonderful as inMS SQL Server.

The first concept is schema.ORACLEand database.

Very often, when discussing the work of 1C on ORACLE, such a simplification is taken as a rule that for ORACLE the database is a schema. This is the most popular misconception. It is the root of all problems (so many problems of working 1C with ORACLE DBMS).

A schema is a logical entity. This is a group of tables. The database is a physical entity. This is a group of files. Putting an equal sign between them is by no means correct!

Therefore, ORACLE has problems with backups, logging, moving databases to a separate disk, and so on.

1C, to simplify her life, chose the scheme as a tool for creating a cluster in the console. To be able to create a database from 1C, they chose a schema instead. The fact is that creating a database in ORACLE is a non-trivial process. Each database in Oracle is a separate service, a separate instance. 1C has simplified all this, and uses a separate schema for a separate database. It seems to me that 1C has chosen the wrong policy. This is some kind of user deception. There is no equals sign between Schema and Database. A large ORACLE solution can function normally only if you have only one 1C base on one server, one Instance. There is no other option.

Versioning.

I won't go into detail. I will only say that there are versioned DBMS and blocking DBMS.

In blocking DBMS, if one transaction has already begun to change data, then another transaction must wait at that moment. In versioned DBMS, another transaction can read the data.

A versioned DBMS is good, a blocking DBMS is bad. However, a versioned DBMS is not a panacea for all troubles, because if you get a previous version of the leftovers as a result of using it, you will not be very happy. You still have to impose controlled locks, you still can’t write anything in parallel - there are no miracles!

Locking DBMSs are IBMDB2 and MSSQLServer (it must be admitted that MSSQLServer has a Read_Commited_Snapshot mode - a kind of parody of versioning - it is used in the 1C 8.3 platform version, it is also used in Microsoft Dynamics AX). Versioned DBMS are ORACLE and PostgreSQL. I don’t want to say anything bad about Postgre, it’s a free DBMS, an enthusiast project. Personally, I do not consider it as a DBMS for serious projects. It seems to me that among the versioned DBMS supported by the 1C platform, ORACLE is the only full-fledged option.

Why do they loveORACLE?

I must say right away that the article will contain a lot of bad things about this DBMS, but there are some positive points that are purely characteristic of ORACLE.

Usually, ORACLE DBMS is associated with the qualities of high performance, unbreakable, etc.

In my opinion, the point here is a little different. Oracle uses two progressive technologies - RAC and ASM.

RAC(cluster type "active" - ​​"active") is a complete cluster. It is full-fledged, not as we are used to in MSSQLServer. I doubt that anyone could implement query parallelization in MSSQLServer on different servers (or that it will appear in the near future). In ORACLE, this appeared a long time ago. This is a technology that has already been tested on large systems (it is necessary for really large systems). RAC clustering is not fully supported in Oraclestandardeditionone.

ASM is usually used in conjunction with RAC. This is a mini-instance of Oracle that is separately installed on each node of the cluster, providing services for working with disks and avoiding disk access (allowing you to work on RAWdevices - disks without file system- all data caching work is done by Oracle itself)

ASM improves performance by automatically dispersing database objects across a large number of devices, increases database availability by allowing new ones to be added to the database disk devices without stopping her.

ASM automatically, with minimal intervention in the work, aligns the distribution of files across devices.

In fact, disk and file system management is automated and outsourced to the DBA. In the case of using a SAN and a large number of disks, this is very important.

Logging

With logging, everything is quite complicated. The only thing I would like to point out is that if we are workingArchiveLog - we can make full-fledgedBackups, and if we work in the modeNoArchiveLog - we are full-fledgedWe cannot make backups (only by means ofimpdp andexpdp).

In ArchiveLog mode, if you have more than 1 1C database on the server and want to use full-featured backups, you will need a second database created in a special way to restore the backup, so that later it can be transferred to the main server using datapump. In general, in 90% of cases for 1C there will be NoArchiveLog. It all depends on the chosen backup strategy and SLA (if any). In fact, ArchiveLog is a banal rotation, but without it online backup impossible. If the place for ArchiveLog comes to an end, 1С will stupidly fall.

Tablespaces inOracle

Interesting topic. In MSSQLServer, tablespaces are just a group of files. In ORACLE, this concept has been greatly expanded, since in ORACLE, by tradition, for files, you need to increase the initial size and increment, i.e. for TP, you can set the block size, bigfile, logging. If not bigfile, then the limit is 32 GB.

Tablespaces 1C:

• Data - The tables themselves
• Index - indices
• Index_Big - block size 16KB. If an index cannot be created on Index, the framework attempts to create one on Index_big. You also need to set the cache size for 16 KB blocks. Actually, the block size can be varied. The smaller, the faster the recording. The more, the faster the reading of large volumes
• LOB - value stores and strings of unlimited length. It's good that they shared it. Now you can, without violating the license agreement, take out all the garbage on a separate disk
• temp - tempdb. You need a very fast disk array.

I would like to draw attention to the 1C V81C_LOB tablespace. ORACLE on this moment time is the only DBMS in which there is a full-fledged storage of files and strings of unlimited length. VORACLE, we can transfer files and lines of unlimited length to a separate disk. What does it mean? We can, for example, implement 1C: Document Management in large companies, on large amounts of data, and at the same time not check this box that everyone loves - “storing a file in external storage”. ORACLE allows us to store all our files directly in a database (this database will be spread across multiple disks). The most interesting thing is that not only ORACLE - any DBMS allows us to do this, just the 1C license agreement imposes restrictions - for any other DBMS we do not have standard means of transferring files and strings of unlimited length to a separate server disk space. And in the case ofORACLE 1C guessed to allocate a separate tablespace for thisV81C_LOB. Wonderful "feature".

A few more basic concepts:

• REDOLOG (current log) - You need to watch the size free space. Can be turned off. Oracle "doesn't forgive mistakes". If the space for the logs runs out, it simply “falls”. If there are no backups, then logs are not needed
• ALERTLOG(technology log) - / u01/ app/ oracle/ diag/ rbms/ main/ OID/ alert you have to look into it even if there is a dba
• LISTENER (organization of network access) - when working with MSSQLServer, we are not accustomed to network access to the database is a separate application
• SYSDBA (base mode) - root for oracle - normal actions are not available in this mode. Administrator-only mode.

Understood the basic concepts. Now I will move on to the "main article" - I will talk specifically about the work of ORACLE with 1C.

Problems of developing 1C onORACLE

The first and most important - specific linguistic sorting. If we are talking about working in ORACLE with text strings, then this is probably the main problem.

The 1C platform uses the same mechanisms for working with all variants of the DBMS (including the file version). Accordingly, the 1C platform implements sorting of string values ​​in database tables according to its own rules. In particular, if a dot or a comma is present in the line, then for 1C this will affect sorting. In ORACLE, which is standards oriented, the dot or comma has no effect on sorting. Because of such an elementary problem, 1C had to fence a whole "garden" - use the NLSSORT function in order to have its own sorting. And already the use of this function led to significant modifications.

Any index and any sort by row you have will use the functionNLSSORT (implicitly call it). The use of this function also requires the installation of a specific Lbuilder application for ORACLE to work with 1C (this is the only thing that distinguishes the installation of ORACLE for 1C from a simple installation of ORACLE).

What does this mean for developers? And for the developer, this threatens that you (by default) will not work with registers that have more than 3 string dimensions. And the fact that the size of the string index will be very large. In short, any long line in the information register, either in the accounting register or in the accumulation register, is very bad. Any index by row is also bad, and sorting by row is also bad. However, in general, the functional index is fast. The fact that such string indexes in the V81C_INDEX_BIG table space take up large volumes is, of course, not very good, but not critical. Just need to know what's in general an accumulation register with a dimension of type "String" is an architectural error. In particular, ORACLE just reminds you of this.

Further - one more very unpleasant news. ORACLE does not use clustered indexes. That is, ORACLE, of course, uses clustered indexes - they are called IOT there - this is more correct name for clustered indexes in ORACLE. Just 1C onORACLE does not use clustered indexes, but creates regular indexes.

What does this threaten us with? P ri work 1C on ORACLE write speed increases, unlike other DBMS - it seems that this is a plus. On the other hand, reading speed is reduced. When working with other 1C DBMS, it builds a clustered index for any reference types by reference - this is the most fast way data selection. And when implementing the engine for working with ORACLE 1C, I had to abandon clustered indexes. And sometimes it's interesting for me to watch tests on the Internet, where it is beautifully presented that when a solution works on ORACLE, it is written so quickly, but read a little slower. In fact, this is not really an ORACLE problem - it's just the logic of 1C. If you know about this logic, then there is nothing surprising in this.

Two more embarrassing moments

• typeNULL for all DBMS except MSSQLServer, reverse sort order.
• Temporary tables - we are all used to them. All developers work with them, but in the case of using ORACLE - temporary tables become not quite temporary. I believe that this was a methodological mistake for 1C developers - since temporary tables in ORACLE are designed for something completely different. In general, ORACLE does not recommend using temporary tables to store intermediate results. There, the intermediate result is stored in views. 1C stores this intermediate result in temporary tables, and these temporary tables are created in the database like regular tables and are no different from them. Created, then used .... Are cleared. But remain in the dictionary. In addition, temporary tables in ORACLE are oriented to a rigid structure, except that the data from them is used only within the session. Separated for each session, so even with dynamic_sampling enabled no one promises a correct query execution plan. This does not mean that temporary tables should not be used, they should be used. It's just that if, for example, I myself wrote a query in which temporary tables were generated programmatically when collecting a query, then this is when working onORACLE will cause significant problems: if you have 200 temporary tables in your query, then the query at the first execution on ORACLE is good if it is executed, but it can be executed for a very long time.

I do not want to pronounce the words "bugs", but still have to. The slide partly duplicates the previous ones.

• If you have developed configurations for a managed application at least once, you certainly know what a BSP is. BSP onORACLE won't even start... The problem is cheap - it is quickly solved, one could just rewrite the request a little or make a small modification to the platform, but - this problem has not been solved so far ( the last three releases of BSP this problem exists). This problem is caused by the fact that in enumerations, access to the “order” attribute leads to an error. Let me remind you that BSP is the basis of all the latest 1C solutions. 1C positions BSP as "the main developer tool" and "flagship product", ignoring this mistake. This means that even primary performance testing on ORACLE is not carried out.
• I already said about the problem with the database (with its backup and maintenance due to the use of the schema as a database) . Backup toORACLE - either you have one database onInstance- Production, or you are backing up only by means of import (no differential, no differential backup).
• Everything is bad with temporary tables and reading data from 1C in oracle.
• Technological magazine 1C fromORACLE not getting query plan- so far this feature does not work.
• Normal Profiler, as in MSSQL, No- you won't find it. There are a bunch of different LogAnalizers. Incl. can Toad and Spotlight, which will be discussed below. But Online, a graphical query plan, full-fledged filtering cannot be found. Of course, professional ORACLEDBAs can analyze workload - they launch console tools, generate html files ... But this is no longer “two clicks” - therefore, if the project is about performance analysis, then it is necessaryORACLEDBA.
• The Oracle optimizer is not focused on extensive use of nested queries and tends to choose a fairly simple execution plan for joins (NASTEDLOOPS).

On this slide, I have collected everything that violates the 1C license agreement (1C forbids us to use these features). There are some important points here:

• Partitioning (in ORACLE there are 6 types, the table can be divided into 2 disks) - 1С the use of sectioning does not provide
• Storedoutline - "tips" to the optimizer. As far as I know, in MSSQLServer we can only influence the query plan indirectly (that is, we used to add 2000 records to the register so that there were no locks in the MSSQLServer database) - in ORACLE everything is much simpler. Oracle allows you to manage query plans. 1С the use of this feature does not provide
• Mat. View - indexed views that can be used instead of tables. 1C also does not use this feature.
• Compression
• Bit indices - in short - index by organization. All who tracked the history of formation applied solutions 1C firms could pay attention: at first the “Organization” prop in all documents was indexed. Then - the development of the design thought of the architects of applied solutions 1C led to the fact that the attribute "Organization" ceased to be indexed. Logically. There are usually 3-4 organizations, the selectivity is low, the index is not used, it is superfluous. Then again there were recommendations that this attribute should be added to the index. This is, as they say, "fish for lack of fish and cancer." The "Organization" prop is a typical case of a bit index. When you have low selectivity, but - at the same time, it is used everywhere, there are selections for it everywhere ... Unfortunately, this opportunity We can't use Oracle either... We can, of course, BUT ...

Parameters

By default, Oracle is only suitable for a test environment. Be sure to set the following parameters during the initial setup:

• Sessions>230 and Processes>200. Sessions and Processes are almost never enough. In production, it is not criminal to increase it to 200. There may be a little more sessions. Basically, a process is a connection, but there are a bunch of internal processes
• Trace_enabledFALSE(extended technology log) not SQLTrace of course, but still not needed all the time ... and it won’t help us either
• RecyclebinOFF(Basket) - you can only smile. By default, a lot of things are included in ORACLE. The Recycle Bin is also enabled by default - which is correct - because if you delete a table, then it is placed in the Recycle Bin, not deleted. I am very glad that it does not work for deleting rows - only for deleting tables. And in 1C, any restructuring - deleting tables ... 1C generally works with the database in an original way in case of restructuring. If you restructure the database, then your table is deleted and recreated. We added a selection criterion or general props and the volume of the database increased by 2 times J. Therefore - of course, the basketnecessaryturn off
• Mail Alerts- notify about problems, running out of space, etc., if you respond in time, you can prevent the "fall" of Oracle. Be sure to enable!
• Cursor_sharing- manages the search engine for a query in the query cache. To reduce the time for parsing requests, you must immediately set exact. It is impossible to change - functional indexes will cease to be used. Those. all…
  EXACT- searches for a query that exactly matches yours. There is no rewriting of your request (using bind variables) for possible use by other sessions. On the one hand, a bunch of small requests with complex structures is a typical situation for 1C: a lot of time is spent compiling them
  FORCE- searches for a query that matches your query up to connected variables. Rewriting is done: all literals are replaced with bind variables, a plan is created for the "improved" query
  SIMILAR(introduced in 9i) - performs the same actions as with FORCE, but also checks whether it is possible to pick up a similar already parsed query that should not change the plan of your query. That is, if the optimizer decides that a different plan is needed to execute your query than the one already parsed, then your query will be fully parsed
• Statistics very important for CBO. But in version 10 of Job, statistics are already collected by the system, moreover, it collects statistics only for those tables that are needed. However, statistics collection can also be started manually.

Backup options

Further - parameters of Backup-ov. In ORACLE, if, God forbid, there is no database administrator, you must enable automatic memory management (AMM), otherwise after some time Oracle will stop working, and also in the case of using the impdp and expdp tools - the usual import / export tools, disable ArchiveLog and restrict RedoLog.

Fine tuning

• Here is another interesting setting - optimizer_index_cost_adj- essential setting. If we set it to 1, then ORACLE will use all the indexes it can. The less, the lower index usage threshold. That is, if we have only 3 values ​​in the directory, then with the value of this parameter 1, we will still use all indexes. If we leave the default value (100), then we will only use indexes if we select one record out of a million. It is very good that we can vary this - in SQLServer, for example, it is impossible. Best set to 30, because we only have regular indexes
• Fileststemio_options=SETALL - cancels the use of the file system(you can use disk devices without a file system: performance is significantly increased, direct disk access is performed - Oracle itself does all the data caching work).
• Redo log group members > 2 Redo log groups > 1 - reduce the number of switches

Administration ToolsORACLE

If the serviced ORACLE base does not have ORACLEdba, then you cannot do without administrative tools (unless, of course, you are not fans of the console and do not burn with the desire to write a lot on the black screen).

EnterpriseManager

One of the most beloved ORACLE administration tools is EnterpriseManager. Free, web interface etc. It has a lot of functionality and this tool successfully covers 80% of administration tasks. The only problem is that the interface language is English.

SQL Developer

Another ORACLE DBMS administration tool is SQLDeveloper. This tool is most similar to ManagementStudioMSSQL. But in reality, this tool can only be used to build queries and create tables manually.

In addition, in my subjective opinion, all graphic applications, written in Java, have major interface flaws. Just like EM, it's free.

There are also paid products for administering the ORACLE DBMS - for example, TOAD. It can cost more than Oracle itself. A lot of functionality (not always in demand). BestPractice mode sets the settings to the most optimal. It is worth at least looking at the operation of this product in order to understand what settings it will offer (though some of the settings set in this mode are not suitable for Oracle to work with 1C, you need to pay attention to this. I indicated the necessary values ​​\u200b\u200bof critical parameters in my previous slides) .

Spotlight

Another handy tool for monitoring the operation of the Oracle DBMS is Spotlight (manufactured by the same company as TOAD). Nice. Not very expensive (about 37 tr). Convenient.

Very nice, correctly and quickly reveals all current problems, even offers a solution. Shows all aspects of performance on one screen.

Technical support

Technical support: with the purchase of ORACLE, a year of support is free.

As you can see, the variety of supported languages ​​is amazing. Based on the supported languages, we can conclude in which countries the bulk of oracle specialists are located.

Actually, it is difficult to expect high-level specialists there. They can just dig well into the internal database and internal resources.

Usually the answer comes within a day.

In addition to the call service, there is also access to the technical support database and access to downloading updates.

But the updates themselves are a whole story. Updates include schema recompile, recreate some tables. Updates are done in the console only. This is not MSSQLServer and not WindowsUpdate, where "they pressed a button and everything was updated." It's a whole day of dba work.

The last question, which each of you probably already answered for yourself, is the question of when do we still need ORACLE, when will there be any advantages from using it on a project?

If you have ORACLEDBA, then all the problems I have mentioned are all small, they are all solvable, and ORACLEDBA is the kind of person who can make your solution work on Oracle nice and correct. Especially if you manage to agree on something with 1C or 1C will allow us to use some of the features that I have listed. A competent DBA can speed up the launch and correct operation of your solution by a factor of two. Because the amount of funds that ORACLE provides us is really amazing.

The cluster in ORACLE appeared a long time ago - RAC is a very productive technology, time-tested. It is used in large organizations. If you have a project in which several thousand (several tens of thousands) connections are planned - even through a separator, then RAC is the only option that will allow you to fully organize this from the point of view of the DBMS. In particular, if 1C is now focused on "clouds", and you already have your own "cloud" or you plan to make it, then in this case, ORACLE is probably the most complete choice.

However, an ORACLE solution is a specific solution. If you want to use partitioning, you'll want to use math. view, if you want to use some other "features" of ORACLE, then of course, you will have to try to "negotiate with 1C", since at the moment 1C does not allow us to use these "features". However, there are solutions that use such "features", and these solutions even received "1C: Compatible" - for example, a solution that uses direct posting recording.

And in other cases it is better to manage MSSQL.

*******

This article is based on a paper delivered at the IE Conference 2012 (November 15-16, 2012). It was also published in the Infostart magazine No. 1

We invite you to a new conference.

1C:Enterprise 8- a software product of the 1C company, designed for the rapid development of application solutions. The 1C:Enterprise technology platform is not a software product for use by end users who usually work with one of many application solutions (configurations) using a single technology platform. The platform and application solutions developed on its basis form the 1C:Enterprise software system, which is designed to automate various types of activities, including solving problems of automation of accounting and management in an enterprise (CIS).

Web- server-Apache2.2.10.

Apache is the number one server on the Internet. More than half of all Internet sites run under Apache or its derivatives. The advantages of Apache are modular organization, stability and speed. With the right hardware and setup, it can handle the heaviest loads. A consequence of the server's modular architecture is the flexibility, ease of change and configuration of the Web server.

Using the Apache web server, you can manage hundreds of sites with different names and access levels. The monetary cost and time spent on adding each site is minimal. The Apache web server, popular due to its transparency for programmers and administrators, and last but not least due to its freeness, is implemented for more or less popular operating systems. Its settings are the same for all operating systems, only the paths where the configuration files are stored differ. Multiple instances of Apache can be running on the same server, listening on different addresses and/or ports. One instance can serve multiple sites. The project is so popular that it constantly acquires new functionality.

Antivirus server - Avast Professional Edition 4.8. Program features:

Resident antivirus scanner.

Scan your computer for viruses while the screen saver is running.

Checking the computer for a virus during startup, before the operating system is fully loaded.

Heuristic analysis.

Blocking malicious scripts.

Automatic update anti-virus databases as well as the program itself.

Built into the program lightweight firewall (IDS - Intrusion Detection System (intrusion detection system)).

Residential protection modularity: Web screen; instant messages; Firewall; standard screen; P2P screen; E-mail, as well as modules for checking mail databases of Microsoft Outlook, Outlook Express and a plug-in for The Bat!.

SMTP/POP3/IMAP4 scanner and plug-in for Outlook

Remove spyware software(spyware) from a computer.

Ability to set a password to change the program settings.

Multilingual interface.

Command line antivirus scanner.

Maintenance of VRDB - Virus Recover Database - databases for recovering infected files.

Support for design themes (3 are already included in the basic package).

The product is ICSA certified.

proxy-server - Extra Systems Proxy Server (ES Proxy)

Extra Systems Proxy Server fourth generation is a free proxy server for Windows. The proposed version of the proxy server is designed to work on the Win32 platform: Windows 95, 98, ME, NT, 2000, 2003, XP. However, server platforms are recommended. It is also recommended that the server on which this program is running be dedicated (that is, not used as a workstation). The purpose of this program is to provide simultaneous access to the Internet from multiple computers on the client's local network through one available communication channel with the provider. The software product in question is already built into the operating system and only needs to be run as a service. This program can be used by any person or organization for any purposes that do not contradict the law, including commercial, without any payment to the authors.

print- server- Print Server 2.4.

The program does not require installation and additional settings. The printer is operated via the TCP IP protocol. Using this program, any local printer can be made a network printer. To do this, just run the program on the file server to which the printer is connected. On the working machine, you need to go to the printer settings and install support for the appropriate printer, indicating that it is a network one with the IP address of the file server.

Mail- server- Courier Mail Server 1.56 is a free mail server (e-mail server) under Windows for office and home networks. It will help you quickly organize the exchange email on the local network and on the Internet.

Advantages of Courier Mail Server:

ease of installation and removal

compactness

ease of administration

low consumption of system resources

multithreading

convenient graphic interface

Russian-language interface and documentation

support for an unlimited number of mailboxes

Composition of Courier Mail Server:

SMTP Server, POP3 Server, IP Filter, SMTP Client, POP3 Client, Scheduler, Mail Sorter, Remote Access.

A programmer and a true 1C implementation expert must know the nuances of 1C Enterprise operation on different database management systems.

This article will discuss the main features of the operation of all DBMS compatible with 1C 8.3.

1C enterprise supports 5 types of DBMS:

• file version of 1C itself;
• IBM DB2;
• MS SQL;
• Oracle BD;

V file mode of operation DBMS 1C has the following features. In the system, each table is represented by 4 files:

• table description file;
• record file;
• a file of values ​​of unlimited length (they are taken out from the main file of records for optimization);
• index file.

Key features:

1. Size limit one of the above files 4 GB of disk space.
2. such a database cannot exceed 1920 bytes.
3. The number of fields for indexing is limited to 256 fields (in other DBMS there are only 16 fields).

Get 267 1C video lessons for free:

Microsoft SQL

The key feature of MS SQL DBMS with 1C 8.2 is maximum amount tables in query 256. It may seem like a lot, but when accessing the fields of a composite type point by point, you can instantly get all 256 tables.

1. NULL just like Oracle, when sorting in ascending order, it becomes at the end of the result.
2. In the mode of automatic locks when reading the table completely blocked.
3. In the PostgreSQL DBMS, performance is greatly reduced in intensive mode, the system is required as often as possible reindexing(method REINDEX).
4. Speed ​​has a big impact on PostgreSQL performance. work hard disk - data after the COMMIT command (committing a transaction) from the cache is placed on disk. This significantly increases reliability, but reduces the performance of the DBMS when writing information ( fsync function). Therefore, this DBMS is considered to be very slow in terms of writing and deleting information, but this can be fixed.
5. Improved reliability can be achieved implementation of RAID arrays combined with blocks uninterruptible power supply. Thus, we will increase the reliability of the system. After increasing the reliability, you can neglect the inclusion of the function fsync and thereby significantly increase the speed of the system.
6. When calculating the difference between dates, if there are transitions from winter time to summer time (and vice versa) in the date interval, PostgreSQL DBMS takes into account the difference. Example - the difference between 10/29/2013-00:00:00 and 10/30/2013-00:00:00 will not be 24 hours, but 25.
7. The DBMS inefficiently fulfills the type of join - "FULL OUTER JOIN".

IBM DB2

1. The DB2 DBMS has an untyped NULL value.
2. DB2 is case sensitive when comparing string values. It concerns not only direct comparison, but also the operations GROUP, JOIN, DIFFERENT.
3. Maximum length numeric value - 31 characters(in other DBMS - 38).
4. Maximum length resource of accounting and accumulation registers - 25 characters(everywhere 32).
5. Maximum amount columns in the selection list - 1012.
6. Maximum field size of unlimited length − 1 GB.
7. An argument for the design "LIKE" can only be a literal, the use of basic templates is prohibited.
8. The performance of the IBM DB2 DBMS is significantly reduced when using subqueries in the join condition.

OracleDB

1. You cannot use the "FIRST" and "ORDER" constructs inside the subquery of the "In (..subquery..)" condition.
2. NULL when sorted in ascending order becomes at the end of the result.
3. Oracle DB has a very strong influence on the work of the Oracle DBMS query plan statistics 1C.

Unfortunately, we are not physically able to advise everyone for free, but our team will be happy to provide services for the implementation and maintenance of 1C. More details about our services can be found on the page

To successfully use this software, it is necessary to correctly select the appropriate mode of operation. Knowledge of the compatibility of 1C with different databases will also come in handy in practice. The materials in this article will help you fine-tune the functioning of applications, taking into account the requirements of a particular enterprise.

Operating modes and client applications

In the simplest system configuration, it is recommended to use special file: "1Cv8.1CD". It stores new user information, registers changes in registers, made individual settings. This method is easy to use. Its functionality is available at no additional cost. The only significant drawback is the limited number of users, no more than 10.

Important! A separate computer is allocated to store the 1Cv8.1CD file itself. Subsequently, access to it will be organized for all users by local network, which get the ability to work with disk space. In this case, the "client-server" mode is simulated.

Here is information that will allow us to more accurately assess the suitability of such a choice for solving certain problems:

• The structure of the file mentioned above is tabular. The size of each individual block is limited to 4 GB;
• If "younger" versions of 1C are used, lower than 8.3, then the correct execution of some tasks in automatic mode will not be possible. The limitation is the need to connect individual users;
• In this option, you cannot post multiple documents at the same time;
• It does not provide a high level of security. If desired, any user is able to make a copy of the main file that stores the enterprise database.

For larger projects, a full-fledged organization of work in the “client-server” mode is better suited. We list its features:

• This option is designed for the simultaneous work of a large number of users. To obtain sufficient performance, different database management systems and servers are used, united in clusters;
• Its speed parameters are better compared to the "file" version, which allows you to process large amounts of data faster;
• In this case, it is easier to install and operate modern information protection;
• To increase computing power, it is enough to add the required number of servers to a specific cluster.

In the "client-server" mode, a standard architecture of three levels is used. The bottom one is user programs. They send calls to the cluster servers. If necessary, data caching is enabled. The request processing algorithm provides for the possibility of immediate responses. To obtain information under the control of the process manager, the server forms a call to the DBMS. The data is sent to the client in a reverse chain.

Advice! If peak loads are expected, it is recommended to install the required number of additional working servers. User requests will be automatically redirected to them.

To switch from a file version to a more productive "client-server" mode, it is enough to load the old data into a special archive. They are further stored on the server. In the "Configuration" section of the 1C program, you can see which mode is activated.

Client Applications

1C provides for work using several types of software. Let's note the features of these three clients:

• Thin - data reception / transmission is carried out on the basis of its own protocol. If https is used, then appropriate server configuration is required.
• Thick is used only if the performance of the communication lines is sufficient. With its help, perform debugging and computational operations, access the database.
• The Web uses programs that run in a browser.

Important! The application is not suitable for the developer to solve practical issues.

If "file" mode is used, then data in 1Cv8.1CD can be accessed directly, while the Web can only be accessed through the server. When working with thin client both ways are allowed. In the "client-server" version, similar schemes are used, but one more link is added, united in a server cluster. It is from him that some answers are received promptly. If necessary, the request is addressed to a higher level, in the DBMS.

Application of different database management systems

• File DBMS:
  • Representation of any of the tables with the following files: descriptions, records, indexes and values;
  • Each of the files takes up no more than 4 GB of disk space;
  • The key length is limited to 1920 bytes;
  • A maximum of 256 fields can be used for indexing.
• PostgreSQL:
  • If sorting mode is used in ascending order, NULL values ​​are placed at the end of the list;
  • The speed of data processing in this DBMS decreases with a significant increase in the intensity of user requests;
  • Performance indicators are highly dependent on the respective technical parameters drives;
  • A special algorithm for fixing each transaction increases the level of reliability;
  • The integrated use of uninterruptible power supplies and RAID arrays will help prevent errors.
• Microsoft SQL - the largest number in one query of tables should not exceed 256 units;
• In Oracle DB, like PostgreSQL, after sorting in ascending order, NULL is set at the end of the list. In this DBMS, it is forbidden to place "Order", or "First" inside the "To "subquery" construct. When using it, you should carefully work with query plan statistics. They have a noticeable effect on the stability of 1C;
• IBM DB2:
  • NULL is not a typed exponent;
  • The numeric value must not exceed 31 characters;
  • One field is limited to 1 GB;
  • With an increase in the number of subqueries (in the join condition), some performance degradation is not ruled out.

The correct implementation of 1C in the enterprise is carried out taking into account the information presented in this article.