1C:Enterprise 8.3. Developer Guide. Appendix 7. Operation of Various DBMS

1C:Enterprise 8.3. Developer Guide. Contents


OPERATION OF VARIOUS DBMS

7.1. GENERAL FEATURES

„ Index can contain a maximum of 16 database fields, except file mode version where this value can be up to 256 fields.

„ Strings comparison operations in the query language ignore ending spaces, unlike string comparison in 1C:Enterprise script where ending spaces are used in comparison operations. For example, comparing "bb" and "bb " strings in 1C:Enterprise script returns False (strings are not equal), and returns True in the query language (strings are equal).

7.2. FILE DATABASE

„ Database file (.1CD) is a set of the so-called internal files. Each database table can be mapped to up to 4 internal files:

Table description file. It stores a table description.

Data records file. It contains data of all table records with the exception of data from open-ended fields.

Index file. It stores all indices defined for a table. If no index is defined, this file does not exist.

Open-ended values file. It stores open-ended values from table fields.

„ Each of the files listed above can have a maximum size of 4 GB.

„ Key length cannot exceed 1920 bytes.

7.3. IBM DB2 SERVER

„ Untyped NULL. Type of column resulting from SELECT NULL query is the most common composite type; it is not the simple type compatible with any other type. Therefore, this column cannot be used in operations which cannot use composite type fields. For example, SELECT ISNULL(f1 + 1, 1) FROM (SELECT NULL f1) t1 during work with DB2 results in an error because operation <+> operand cannot be a composite type field.

„ Maximum length of numeric data is 31 characters (not 38).

„ Maximum length of accumulation and accounting registers resources is 25 characters (not 32).

„ Maximum size of open-end data is 1 GB.

„ Maximum number of columns in the operator selection list cannot exceed 1012. When defining number of columns please note that 1C:Enterprise creates several columns in DBMS table for composite type fields.

„ Some rules that define result precision for arithmetic operations differ from rules in other DBMS.

„ Only literal (parameter) or expression on literals can be a right operand of LIKE comparison operation. Only "_" (any character) and "%" ( sequence of any characters) can be template characters.

„ Use of subquery written in the query language in the BY section can slow down query execution. If multiple subqueries are used in the BY section (e.g., multiple tables are joined on conditions that contain subqueries), the query could fail.

„ If a query contains a concatenation operation for 10 and more string values that include fields and literals and concatenation result is used for comparison, the following error could occur: DBMS error: SQL0401N. Operands specified for '...' operation have incompatible data types.

„ Joining is not available in a query if two tables are connected with a condition including tabular section fields comparison.

„ Case sensitivity for string comparison. Implicit string comparison performed by query language DISTINCT, GROUP BY and UNION statements is case-sensitive. Therefore, execution of queries is characterized by the following features:

     When DISTINCT and UNION statements are used (without ALL), query result field values of the String type are treated as different if they have differing casing (in other DBMS they are treated as identical).

When ORDER BY statement is used, values of the String type that have different.

Query contains UNION, UNION ALL or DISTINCT statements.

Query selection list contains CASE statement with nested queries.

Current user has restricted access to data and query contains no ALLOWED keyword.

In other cases comparison of string data is not case-sensitive (String values with different casing are treated as identical):

□ when String type fields are compare explicitly.

□ when GROUP BY statement is used.

□ in ORDER BY statement with the exception of the above listed cases.

The features listed above are relevant in the following cases:

□ IBM DB2 9.1 is used.

□ The compatibility mode for version 8.1 is enabled (the Compatibility mode configuration property is set to Version 8.1);

□ The database has been generated with 1C:Enterprise version 8.1.12 or with later versions.

„ The background database configuration update is not supported when IBM DB2 9.1 is used.

„ If you encounter one of the following situations:

     Long query execution time;

A large number of drive operations during query execution;

Presence of the HASH JOIN syntactic construction in the query plan;

Significant speed improvement after current query optimization from 5 to 0,

You are recommended to execute the following command: db2set DB2_ OVERRIDE_BPF=5000, and restart IBM DB2 DBMS. This recommendation does not apply to DBMS version IBM DB2 9.7 FixPack 5 and later.

7.4. MICROSOFT SQL SERVER

„ Up to 256 tables can be used in a query (for Microsoft SQL Server 2000 and Microsoft SQL Server 2005).

„ The database management system error may occur when Microsoft SQL Server 2000 is used, grouping by the expression (instead of the field) is executed, or the aggregate function in the HAVING section not included in the SELECT section is used. It is recommended that you add expressions from the HAVING section to the SELECT section when developing solutions for Microsoft SQL Server 2000. This problem does not occur in Microsoft SQL Server 2005 and later versions of the database management system.

„ If the application solution is designed for Microsoft SQL Server 2000, using functions intended to work with dates in the GROUP BY query section is not recommended, as this can lead to false results. Rather than expressions with functions, you can use grouping by all table fields which influenced the expression.

7.5. ORACLE DATABASE SERVER

„ In the automatic mode Oracle Database uses tabular locks. It means that the transaction that captured one record in the table locks the whole table, which could prevent competitive transactions to work with this table data. It is recommended to develop applications in the managed lock mode to get the most of your DBMS. Application automatic mode is used for compatibility with previous application versions and it is not recommended for production operation.

„ Nested query with TOP modifier and ORDER BY section cannot be used in the IN operator if the nested query contains calls to external query fields.

„ When sorting in ascending order, NULL fields are the last in the selection.

„ Use of subquery written in the query language in the BY section can slow down query execution. If multiple subqueries are used in the BY section (e.g., multiple tables are joined on conditions that contain subqueries), the query could fail.

„ The first ValueStorage attribute (by order in the table) is optimized; if no attribute of this type exists, the first Open End String attribute (by order in the table) is optimized.

„ Please note that in Oracle Database performance is affected by whether statistical data are current. It is recommended to update DBMS statistics regularly (DBMS does it by default, but the user can gather statistics using the dbms_stats.gather_schema_stats procedure). It also makes sense to collect statistical data after infobase has been restored from infobase dump file (*.dt). After database server is restarted collection of statistics can also affect performance.

„ If registers have more than three string type dimensions, when database configuration is updated and when an infobase is loaded tabular space can be required for indexes with a large key. 1C:Enterprise does not create this tabular space, but it can use a space called V81C_INDEX_BIG.

Tabular space should be created by a database administrator using the CREATE TABLESPACE command by specifying block length in the BLOCKSIZE parameter. To create a tabular space with a block length exceeding the database block length an additional configuration of the database instance can be required. For example, you can create a tabular space with 16384 byte block length by specifying the DB_16K_CACHE_SIZE parameter using the ALTER SYSTEM command.

„ In queries with the LIKE comparison operation, square brackets are considered to be special characters only when they are used in the text literal, but not in the expression.

In the example below the square brackets have a special meaning – they represent a set of characters:

SELECT * Catalog.Products WHERE Article LIKE "123[AB]%"

In the following example the square brackets, which can be in the Template variable, have no special meaning.

SELECT * Catalog.Products WHERE Article LIKE Template+"%"

7.6. POSTGRESQL SERVER

„ In the automatic mode PostgreSQL uses tabular locks. It means that the transaction that captured one record in the table locks the whole table, which could prevent competitive transactions to work with this table data. It is recommended to develop applications in the managed lock mode to get the most of your DBMS. Application automatic mode is used for compatibility with previous application versions and it is not recommended for production operation.

„ When sorting in ascending order, the NULL fields are the last in the selection; when sorting in descending order, they come first.

„ PostgreSQL uses table locks in the automatic lock control mode. It means that a transaction reading data from a table prevents another transaction from writing data to the same table. File mode version uses the same lock granularity (table being one granule).

„ Operations that remove/add a lot of records from/to database tables (e.g., when documents are re-posted) can also affect performance adversely. To restore initial performance it is recommended to run REINDEX (or VACUUM) operation regularly for database tables with high volumes of modification. Frequency of these operations depends on how intensively the tables are used.

„ PostgreSQL performance is greatly affected by the disk system performance since fsync parameter is enabled by default. It means that when the COMMIT operation runs data are immediately written from OS cache to disk, thus ensuring consistency in case of hardware failure. The downside is that write operations in a disk drive slow down as delayed write of OS data is not used.

„ To boost performance use multi-disk RAID arrays based on caching RAID controllers with non-volatile cache memory or uninterruptible power supply (UPS). In case of hardware error data consistency is ensured by the abovementioned devices; therefore, fsync parameter can be disabled and performance of write operations in a disk drive can be increased. Please note that increasing the number of disks in the RAID array and cache size of the RAID controller is enough to compensate for slow performance caused by the enabled fsync parameter.

„ There are certain differences in how date/time functions operate. Thus, in Microsoft SQL select datediff(hour, datetime(2006, 10, 29, 0, 0, 0), datetime(2006, 10, 30, 0, 0, 0)) returns 24, while in PostgreSQL it returns 25 because transition to standard time has occurred between these two dates. The same applies to the DATEADD function regarding the daylight savings time.

„ It is not recommended to use FULL OUTER JOIN in queries since this construct does not function efficiently enough in DBMS. In most cases source query can be re-written without using this statement.

„ You can't simultaneously use FULL OUTER JOIN and access tabular sections in the selection field list.

„ In queries with the LIKE comparison operation, square brackets are considered to be special characters only when they are used in the text literal, but not in the expression.

In the example below the square brackets have a special meaning – they represent a set of characters:

SELECT * Catalog.Products WHERE Article LIKE "123[AB]%"

In the following example the square brackets, which can be in the Template variable, have no special meaning.

SELECT * Catalog.Products WHERE Article LIKE Template+"%"

Leave a Reply

Your email address will not be published. Required fields are marked *

1C:Enterprise Developer's Community