Proper use indexes can speed up queries not just by times, but by hundreds, sometimes even thousands of times.

This kind of acceleration simply cannot be achieved with hardware. Therefore, this topic needs to be given close attention.

Often, in order to speed up a query, you need to create your own index, and there are several different ways to do this.

In the video tutorials we will look at several ways to create an index. We will also consider a situation where it is impossible to create an index of the required composition regular means platform and it will have to be created in a DBMS.

Setting up indexes using standard platform tools

The lesson shows what indexes are actually created for objects at the DBMS level.
Not everything in this topic is as obvious as it might seem at first glance. After all, for a number of objects there are features of creating indexes.
We'll look at all the details in this video.

Indexing with additional ordering

The video shows the difference between the index construction option Index from Index with additional ordering.
The example shows what kind of index the platform will build when using additional ordering.

Creating an Index for Register Dimensions

Indexing the first dimension of registers has several nuances.
The video shows what indexes are created for register measurements. The situation of indexing the first register dimension is also considered.

or

Why does a 1C developer need to “index” register dimensions and details?

- Well, you have requests! - the database said and hung...

The short answer to the title question is that it will allow queries to run quickly and reduce Negative influence locks on .

What is an index?

Optimizing Index Placement

When the size of the tables does not allow them to “fit” in random access memory server, the speed of the disk subsystem (I/O) comes first. And here you can pay attention to the ability to place indexes in separate files located on different hard drives.

Detailed description actions http://technet.microsoft.com/ru-ru/library/ms175905.aspx
Using an index from a different filegroup improves the performance of non-clustered indexes due to the concurrency of I/O processes and work on the index itself.
The above mentioned processing can be used to determine the dimensions.

Impact of indexes on locks

The absence of the necessary index for a query means iterating through all the table records, which in turn leads to redundant locks, i.e. unnecessary records are blocked. Additionally, the longer a query takes to complete due to missing indexes, the longer the lock holding time will be.
Another reason for locks is a small number of records in tables. Due to this SQL Server, when choosing a query execution plan, does not use indexes, but crawls the entire table (Table Scan), blocking the entire table. In order to avoid such blocking, it is necessary to increase the number of records in the tables to 1500-2000. In this case, scanning the table becomes more expensive and SQL Server starts using indexes. Of course, this cannot always be done; a number of directories such as “Organizations”, “Warehouses”, “Divisions”, etc. usually have few entries. In these cases, indexing will not improve performance.

Index performance

We have already noted in the title of the article that we are interested in the effect of indexes on query performance. So, indexes are most suitable for the following type of tasks:

• Queries that specify “narrow” search criteria. Such queries must read only a small number of rows that meet certain criteria.
• Queries that specify a range of values. These queries also need to read a small number of rows.
• A search that is used in linking operations. Columns, which are often used as bind keys, are great for indexes.
• A search in which data is read in a specific order. If the result set is to be sorted in clustered index order, then no sorting is needed because the result set is already pre-sorted. For example, if a clustered index is created on the columns lastname, firstname, and the application requires sorting by last name and then by first name, then there is no need to add an ORDER BY clause.

True, with all the usefulness of indexes, there is one very important BUT - the index must be “efficiently used” and must allow data to be found using fewer I/O operations and the amount of system resources. Conversely, unused (rarely used) indexes are more likely to degrade data write performance (since every operation that modifies the data must also update the index pages) and create excess database space.

Covering(For of this request), is called an index that contains all the necessary fields for this request. For example, if an index is created on columns a, b, and c, and the SELECT statement queries data from only those columns, then only access to the index is required.

In order to determine the effectiveness of an index, we can roughly estimate using a free online service that shows the “query execution plan” and the indexes used.

Vandalsvq 1114 08.11.14 17:00 Currently on topic

() CheBurator, there is a grouping of conditions, in version 5+. But it is only in 1C++ - http://www.1cpp.ru/forum/YaBB.pl?num=1273512019.
There were plans to make a conditional design, as well as grouping, but alas. There is only one reason for this - I do not work with 7 on a commercial basis and therefore do not modify these tools. There is nothing even basic to test on. Previously, SQL databases were also file-based and of different configurations in bulk. Now it seems like there is only one on which there was development and that one was opened a very long time ago.

In general, here are the latest changes:
5.0.1 dated 04/15/12
- The selection system in dynamic extensions (Directory, Document, Journal, Register, Operations, Postings) has been changed. Added the ability to create arbitrary conditions with a hierarchy.
- In connection with the change in the selection system, changes have been made to the extension objects “Data Provider.Journal”, “Data Provider.Transactions”, “Data Provider.Documents”, “Data Provider.Directory”, “Data Provider.Operations”, “Data Provider.Register”.
- Fixed the behavior of the “Select by current column” button. When pressed, the remaining selections are not disabled. Also, if selection is turned on, it is turned off.
- When a column is activated, the display of the “Select by current column” button changes.
- The construction of selection conditions based on details has been optimized.
- Fixed the error of losing the current line when changing the hierarchical view of the “Data Provider.Directory” extension object.
- The use of the “Display Selection Icon” parameter is disabled. This parameter is left for backward compatibility.
- The object “Data Provider.Description of Types” and “Data Provider.Types of Comparison” have been removed from the data provider. Instead, the common objects “Description of Types” and “Types of Comparison” are used, respectively. For more information, see the documentation for the "Objects_v8" classes.
- Fixed 1C crash when changing the value type of the “Data Provider” object.

5.0.2 dated 04/24/12
- Fixed a bug when laying lists with string values ​​for selection.
- Fixed an error in generating a request for the “Data Provider.Register” extension object if the register has the “Fast movement processing” attribute
- Identified errors have been corrected.

5.0.3 dated 05.22.13
- The “Data Provider.Direct Request” object has been updated to version 1.8.6 dated May 22, 2013.
- all classes are collected together in one archive + examples

5.0.4 dated 10/25/13
- Fixed identified errors

5.0.5 from 05.23.14
- Fixed identified errors when adding data columns
- Added the ability to build tree structures in available fields when adding data columns (in fact, you can now use the “Selection” object to build selections like SKD in 8, but you must have a file)

NewColumn

Syntax: NewColumn(strColumnName)

Options:

• StreamNameColumns- type: String. ID of the column to be created. You can use any symbols in the column identifier, but you should remember that the symbols "-", "*", "#", "^" and "&" are used as modifiers when constructing index expressions, and an index will be built on columns with such symbols impossible.

Returns: type: Number. The number of the created column.

Description: Creates a column in a table.

RenameColumn / RenameColumn

Syntax: RenameColumn(Column, strNewColumnName)

Options:

• Column
• pageNewNameColumns- type: String. New column identifier.

Description: Renames a table column.

Number of Columns / ColumnCount

Syntax: NumberofColumns()

Returns: type: Number. Number of columns in the table.

Description: Returns the number of columns in the table.

ColumnName

Syntax: ColumnName(Column)

Options:

• Column- type: String, Number. Column identifier or number.

Returns: type: String. Column ID. If there is no such column, an exception is thrown.

Description: Returns the identifier of the specified column, as it was specified in the NewColumn() method.

ColumnNumber

Syntax: Column Number(Column)

Options:

• Column- type: String, Number. Column identifier or number.

Returns: type: Number. Column number.

Description: Returns the number of the specified column.

NewRow / NewRow

Syntax: New line()

Returns: type: Number. The number of the line created.

Description: Creates new line at the end of the table.

RemoveRows

Syntax: DeleteRows([Index])

Options:

• Index If the parameter is specified, and a filter is set at this index, then rows that meet the filter conditions will be deleted.

Description: Removes rows from a table. If an index is specified, then the applied filter is taken into account when deleting.

Number of Rows / RowCount

Syntax: Number of Rows([Index = ""], [flOnlyUnique = 0])

Options:

• Index- type: String, Number. Identifier or index number. If the parameter is specified and a filter is set at this index, then only rows that satisfy the filter conditions are considered.
• flOnlyUnique- type: Number. Flag to take into account only unique index values.

Returns: type: Number. number of rows in the table.

Description: Returns the number of rows in the table.

RowNumber / RowNumber

Syntax: RowNumber(Index)

Options:

• Index- type: String, Number. identifier or index number.

Returns: type: Number. The current line number at the specified index. If the row is not selected, then 0.

Description: Returns the current row number in the selection at the specified index.

Cleanup

Syntax: Clear()

Description: Complete table clearing. All indexes, rows and columns are deleted.

AddIndex / AddIndex

Syntax: AddIndex(strIdentifier, strExpression, [hOnlyUniqueValues ​​= 0])

Options:

• strID- type: String. ID of the created index;
• pageExpression- type: String. Index expression. An index expression consists of a comma-separated list of column identifiers. If the column name is preceded by a "-" symbol, then the sorting is performed in reverse order. If the column name is preceded by the “*” symbol, then sorting is carried out according to the internal representation of the object. If a column name is preceded by a "#" character, then the spaces on the left and right are trimmed before comparing strings. If a column name is preceded by a "^" character, then strings are compared in a case-insensitive manner. Sorting modifiers ("-", "*", "#", "^") can be used in any combination. The index expression can be an empty string - this is equivalent to sorting by row number, or no sorting (a filter cannot be installed on such an index).
• withOnlyUniqueValues- type: Number. If 1, then only rows containing unique index values ​​will be included in the index. Rows with duplicate indexes will be ignored, and will not be included in the row iteration, summation, folding, unloading, loading operations.

Returns: type: Number. The number of the created index.

Description: Adds an index to the table.

Number of Indexes / IndexCount

Syntax: NumberIndex()

Returns: type: Number. Number of indexes in the table.

Description: Returns the number of indexes in the table.

IndexName / IndexName

Syntax: IndexName(Index)

Options:

• Index- type: String, Number. Identifier or index number.

Returns: type: String. Index name. If the index is not present on the table, an exception is thrown.

Description: Returns the name of the index.

IndexExpr / IndexExpr

Syntax: IndexExpression(Index)

Options:

• Index- type: String, Number. Identifier or index number.

Returns: type: String. Index expression. If the index is not present on the table, an exception is thrown.

Description: Returns the index expression for the given index.

IndexNumber

Syntax: IndexNumber(Index)

Options:

• Index- type: String, Number. Identifier or index number.

Returns: type: Number. Index number. If the index is not in the table, the function returns 0.

Description: Returns the index number.

Sort

Syntax: Sort(strExpression)

Options:

• pageExpression- type: String. Index expression. An index expression consists of a comma-separated list of column identifiers. If the column name is preceded by a "-" symbol, then the sorting is performed in reverse order. If the column name is preceded by the “*” symbol, then sorting is carried out according to the internal representation of the object. If the index expression is empty line, then sorting is disabled (a filter cannot be installed on such an index).

Description: Change the sorting in the main index.

IndexIsUnique

Syntax: IndexUnique([Index = ""])

Options:

• Index- type: String, Number. Identifier or index number.

Returns: type: Number. 1 - there are no rows in the table with duplicate key values; 0 - the table contains rows with the same key values.

Description: checks if the index is unique.

UniqueKeyCount

Syntax: Number ofUniqueKeys([Index = ""])

Options:

• Index- type: String, Number. Identifier or index number.

Returns: type: Number. Number of unique keys.

Description: returns the number of unique keys at the specified index.

KeyValueCount

Syntax: NumberofKeyValues([Index = ""], Key)

Options:

• Index- type: String, Number. Identifier or index number.
• Key

Returns: type: Number. The number of rows with the specified key.

Description: returns the number of rows for which the key value is equal to the Key parameter.

FindRow / FindRow

Syntax: FindRow(Index, Key, [hFindLast = 0], [hPosition = 0])

Options:

• Index- type: String, Number. Identifier or index number.
• Key- type: any. If the index is built on one column, then the search value. If the index is on several columns, then this should be a Value List containing the values ​​of the key columns specified in the AddIndex() method.
• hFindLast- type: Number. If 1, then the last row with a matching key value will be found.
• hPosition

Returns:

Description: Find the string that exactly matches the key.

FindNearestMore / FindNearestGE

Syntax: FindNearestMore(Key, [Index = ""], [hPosition = 0])

Options:

• Key- type: any. If the index is built on one column, then the search value. If the index is on several columns, then this should be a Value List containing the values ​​of the key columns specified in the AddIndex() method.
• Index
• hPosition- type: Number. If 1, then if the search is successful, the index will be positioned on the found string. If a selection was opened before executing the method (using the SelectRows method), it is repositioned to the found value. If the selection was not opened, then it is opened, and after a successful search, the GetRow(), NextRow() and PreviousRow() methods can be used. The main index is also being repositioned.

Returns: type: Number. The number of the found line. If nothing is found, then 0.

Description: Find a string that is equal to or greater than a key.

FindNearestLess / FindNearestLE

Syntax: FindNearestLess(Key, [Index = ""], [hPosition = 0])

Options:

• Key- type: any. If the index is built on one column, then the search value. If the index is on several columns, then this should be a Value List containing the values ​​of the key columns specified in the AddIndex() method.
• Index- type: String, Number. Identifier or index number. The default is the primary index.
• hPosition- type: Number. If 1, then if the search is successful, the index will be positioned on the found string. If a selection was opened before executing the method (the SelectRows method), then it is repositioned to the found value. If the selection was not opened, then it is opened, and after a successful search, the GetRow(), NextRow() and PreviousRow() methods can be used. The main index is also being repositioned.

Returns: type: Number. The number of the found line. If nothing is found, then 0.

Description: Find a string that is equal to or less than a key.

SetFilter / SetFilter

Syntax: SetFilter(KeyMin, KeyMax, [Index = ""], [hOnlyUnique = 0], [hInversion = 0])

Options:

• KeyMin- type: any. Lower limit of the filter. It is set in the same way as in the FindString() method.
• KeyMax- type: any. Upper limit of the filter. It is set in the same way as in the FindString() method.
• Index- type: String, Number. Identifier or index number. The default is the primary index.
• OnlyUnique- type: Number. If 1, then only rows with a unique key value will be included in the selection.
• hInversion- type: Number. 0 - normal filter, 1 - inverse filter.

If the index is built on one column, then the column value can be used as keys. If the index is on several columns, then the key must be a ValueList containing the values ​​of the key columns specified in the AddIndex() method.

One of the keys can be omitted - in this case, the minimum/maximum possible value will be automatically used as the omitted value. Those. the filter will have an open border.

Description: installs a dynamic filter on the table at the specified index. The filter affects the operation of the methods: InStart(), SelectRows(), InEnd(), GetRow(), NextRow(), PreviousRow(), Collapse(), Total(), FillColumn(), Unload(), Load(), Merge().

Subset

Syntax: Subset(swKey, hNumberFixColumns, [Index = ""])

Options:

• szKey- type: List of Values. A list containing the values ​​of the index columns to be fixed.
• hNumberFixColumns- type: Number. Number of fixed index columns
• Index- type: String, Number. Identifier or index number. The default is the primary index.

Description: sets a filter, fixing the values ​​of the first columns of the index.

TZ.AddIndex("and1", "k1, k2"); szKey = CreateObject("List of Values"); szKey.AddValue(5); TZ.Subset(szKey, 1, "and1");

As a result, the sample will contain only those rows whose column k1 = 5.

DisableFilter / DropFilter

Syntax: DisableFilter([Index = ""])

Options:

• Index- type: String, Number. Identifier or index number.

Description: turns off the filter at the specified index.

InStart / FirstRow

Syntax: BStart([Index = ""], [flOnlyUnique = 0])

Options:

• Index- type: String, Number. Identifier or index number. The default is the primary index.
• OnlyUnique

Returns

Description: is positioned before the first row of the specified index, so that the NextRow() method is positioned on the first row.

SelectRows

Syntax: SelectRows([Index = ""], [flOnlyUnique = 0])

Options:

• Index- type: String, Number. Identifier or index number. The default is the primary index.
• OnlyUnique- type: Number. Sampling flag based on unique values. The flag affects the result of all subsequent calls to the GetRow(), NextRow(), and PreviousRow() methods for this selection.

Returns type: Number. 1 - the operation completed successfully, 0 - the selection is empty.

Description: is positioned before the first row of the specified index, so the GetRow() method is positioned on the first row.

LastRow

Syntax: ATEnd([Index = ""], [flOnlyUnique = 0])

Options:

• Index- type: String, Number. Identifier or index number. The default is the primary index.
• OnlyUnique- type: Number. Sampling flag based on unique values. The flag affects the result of all subsequent calls to the GetRow(), NextRow(), and PreviousRow() methods for this selection.

Returns type: Number. 1 - the operation completed successfully, 0 - the selection is empty.

Description: is positioned after the last row of the specified index, so the PreviousRow() method is positioned on the last row.

NextLine / Next

Syntax: NextRow([Index = ""], [hOnlyUnique = 0])

Options:

• Index- type: String, Number. Identifier or index number. The default is the primary index.
• OnlyUnique

Returns

Description:

Note: the unique selection flag set to 1 in the BStart() and BEnd() methods takes precedence over the flag of this method.

GetRow / GetRow

Syntax: GetString([Index = ""], [hOnlyUnique = 0])

Options:

• Index- type: String, Number. Identifier or index number. The default is the primary index.
• OnlyUnique- type: Number. 1 - the next row with a different key value will be obtained, all duplicate rows will be skipped.

Returns type: Number. 1 - the operation completed successfully, 0 - there are no more rows.

Description: move to the next index line.

Note:

PreviousLine / Previous

Syntax: PreviousRow([Index = ""], [hOnlyUnique = 0])

Options:

• Index- type: String, Number. Identifier or index number. The default is the primary index.
• OnlyUnique- type: Number. 1 - the next row with a different key value will be obtained, all duplicate rows will be skipped.

Returns type: Number. 1 - the operation completed successfully, 0 - there are no more rows.

Description: move to the previous index line.

Note: the unique selection flag set to 1 in the BStart() and BEnd() methods takes precedence over the flag of this method.

GetValue

Syntax: GetValue([hRow = EmptyValue], Column)

Options:

• hString- type: Number. Line number. If the line number is not specified, then the current line is taken.
• Column- type: String, Number. Column identifier or number.

Returns type: any. Table cell value.

Description: returns the value in the specified table cell.

SetValue / SetValue

Syntax: SetValue([hRow = EmptyValue], Column, Value)

Options:

• hString- type: Number. Line number. If the line number is not specified, then the value in the current line is set.
• Column- type: String, Number. Column identifier or number.
• Meaning- type: any. New cell value.

Description: sets the value of the specified cell.

Set

Syntax: Set(hRow, Column, Value)

Description: synonym for the SetValue() method.

Collapse / GroupBy

Syntax: Collapse(strColumnsCollapse, strColumnsSum [, ExistingIndexName = ""])

Options:

• LinesColumnsConvolution- type: String. List of columns by which the rollup will be performed. This line is exactly the same as the index expression in the AddIndex() method. For a rollup, the table is first indexed by this expression, and then rows with the same index are summed. The result is always placed in the first line by number, all other lines are deleted.
• LineColumnsAmount- type: String. List of columns to be summed. The separator is the "," character. Spaces are ignored.
• ExistingIndexName- type: String, Number. If an index name is specified, then Collapse Columns are ignored, and the collapse is performed at the specified index. Only those rows that are included in the filter participate in the convolution.

Description: collapses the table by columns<КолонкиСвёртки>, summing the values ​​in the columns<КолонкиСумм>. Columns not included in<КолонкиСвёртки>And<КолонкиСумм>are not removed from the table. Convolution is also possible using an existing index - no time is wasted on building a temporary index, and selective convolution is possible using the current index filter.

Total / Sum

Syntax: Total(ColumnSum, [Index = ""])

Options:

• ColumnAmounts
• Index- type: String, Number. Identifier or index number. The default is the primary index.

Returns

Description: calculates the amount for the specified column, taking into account the index filter.

TotalByRange / SumByRange

Syntax: TotalByRange(Sum Column, [Index = ""], KeyMin, KeyMax)

Options:

• ColumnAmounts- type: String, Number. The column for which you want to get the amount.
• Index- type: String, Number. Identifier or index number. The default is the primary index.
• KeyMin- type: any. Lower limit of the range. It is set in the same way as in the FindString() method.
• KeyMax- type: any. Upper limit of the range. It is set in the same way as in the FindString() method.

Returns type: Number. The amount in the specified column.

Description: calculates the amount in the specified column, specifying the summation range. The function does not take into account installed filters and does not install permanent filters.

TotalByNode/NodeSum

Syntax: TotalByNode(SumColumn [, Index])

Options:

• ColumnAmounts- type: String, Number. The column for which you want to get the amount.
• Index- type: String, Number. Identifier or index number. The default is the primary index.

Returns: type: Number. The amount in the specified column. If the selection is not active (there is no current node), it returns 0.

Description: calculates the sum for the specified column for the current node of the index tree. It makes sense when the index is not unique, and you need to calculate the total for the current key.

TZ.AddIndex("Last Name", "Last Name", 0); TZ.FindString("Last Name", "Ivanov", 0, 1); //position ourselves on the node that contains all the lines with the last name Ivanov Sum = TZ.TotalByNode("Sum", "Last Name"); //returns 420 (100 + 120 + 200) TK.FindString("Last Name", "Petrov", 0, 1); //position ourselves on the node that contains all the lines with the last name Petrov Sum = TZ.TotalByNode("Sum", "Last Name"); //returns 1100 (500 + 600)

Group

Syntax: Group(strGroups, strColumnsSum, [hDecryptionLastLevel = 0])

Options:

• groupGroups- type: String. A string describing the desired grouping structure. Specified in the form<ИмяИндекса1>: <ИндексноеВыражение1> [; <ИмяИндекса2>: <ИндексноеВыражение2>...]. IndexExpression is a string in the same format as for the AddIndex() method, with one addition: if the “&” character is present in the list of column modifiers, then the totals for directory groups will be calculated for this column. There can be only one column with such a modifier at each grouping level.
• LineColumnsAmount- type: String. A line with columns by which amounts should be calculated.
• hDecryption of the Last Level- type: Number. 1 - in each row of the last grouping level there will be a table with a decryption, containing the rows of the original table in an untouched form. 0 - the last level will be collapsed.

Description: generates a table with a tree structure in accordance with groupings. At each grouping level, only unique values ​​at the corresponding index remain. The sum columns contain the sums for all rows with a matching key value. Rows with matching key values ​​are written to a table, which is placed in the column Descendants - this column is created during the grouping process.

Example: we have a table at the input

group it:

TZ.Group("Last name: Last name; First name: First name", "Amount");

and get a table like this (the column TzDescendants shows the contents of the table TzDescendants):

When grouping, it is also possible to calculate amounts by groups of directories - to do this, you need to specify “&” in the list of index modifiers. If the table has a column named "<ИмяКолонкиСправочника>_Parent", then the parent for the element will be taken from this column - this reduces the number of calls to the database and significantly increases performance. The final table will be organized in the form of a tree, exactly repeating the structure of the directory. The next level of grouping is located in the leaves of this tree (i.e. that is, in rows that are not groups). For convenience of working with the resulting tree, the column “__ThisGroup__” is added to the table, in which 1 is written for all groups, and an EmptyValue for elements. A column “__Level__” is also added, which is filled with the value of the level of groups of the directory (numbering starts from 1) For elements, an EmptyValue is written in the “__Level__” column.

FillRow

Syntax: FillRow([hRowNumber = 0], Source, [hSourceRowNumber = 0], [hColumnNames = 0])

Options:

• hLine Number- type: Number. Line number to be filled in. If<= 0, то заполняется текущая строка.
• Source- type: IndexedTable, Table of Values, List of Values. The data source to populate the row.
• hSource Line Number- type: Number. Row number in the source table. By default, the current row in the predefined index is taken. If the Source is a ValueList, then this parameter is ignored.
• hBy Column Names- type: Number. 0 - values ​​from the source are copied by column numbers; 1 - values ​​are copied by column names. If the receiver does not have a column with the same name, then the value is not copied. If the Source is a ValueList, then the column names are considered to be symbolic representations of the values.

Description: fills a table row from a row in another table, or from a list of values.

FillColumn (option 1)

Syntax: FillColumn([Index = ""], Column, Source, [SourceIndex = ""], [SourceColumn = ""])

Options:

• Index
• Column
• Source- type: IndexedTable. Source of data to fill the column.
• Source Index- type: String, Number. The identifier or index number by which the rows of the source table are ordered and filtered. The default is the primary index.
• Source Column- type: String, Number. The identifier or number of the column from which the values ​​will be taken. If the parameter is not specified, then the source column is determined by the parameter Column.

Description: fills a table column from a column in another indexed table.

FillColumn (option 2)

Syntax: FillColumn([Index = ""], Column, Source, [SourceColumn = ""])

Options:

• Index- type: String, Number. The identifier or index number by which the rows of the table being populated are ordered and filtered. The default is the primary index.
• Column- type: String, Number. Identifier or number of the column to be filled.
• Source- type: Value Table, Value List. Source of data to fill the column.
• Source Column- number or identifier of the column from which the values ​​will be taken. If the parameter is not specified, then the source column is determined by the parameter Column. For a list of values, the parameter is ignored.

Description: fills a table column from a column in another table (Value Table) or a list of values.

FillColumn (option 3)

Syntax: FillColumn([Index = ""], Column, Value)

Options:

• Index- type: String, Number. The identifier or index number by which the rows of the table being populated are ordered and filtered. The default is the primary index.
• Column- type: String, Number. Identifier or number of the column to be filled.
• Meaning- type: any, except IndexedTable, ValueTable, ValueList. The value with which the column will be filled.

Description: fills a table column with the same value.

Unload

Syntax: Unload(Receiver [, Index = ""] [, Columns = ""] [, onlyUnique = 0])

Options:

• Receiver- type: IndexedTable, Value Table, Document, Value List, Undefined. The variable into which the table will be loaded. If this is a document, then its tabular part is filled in. If the type is Undefined, then a table of type IndexedTable is created.
• Index- type: String, Number. Identifier or index number. The default is the primary index.
• Columns- type: String, Number. List of columns separated by commas or column number to be unloaded. If no value is specified or an empty string is specified, the parameter is ignored.
• OnlyUnique- type: Number. Flag to upload only unique values.

Description: dumps the current table into another table, or into a new one. If the destination table exists, it is completely cleared. Indexes are not copied during unloading. Unloading is carried out taking into account the index and filter.

Load

Syntax: Load(Source, [Index = ""], [Columns = ""])

Options:

• Source- type: IndexedTable, Value Table, Document. The table from which the data will be loaded. If the type is Document, then the tabular part of the document will be loaded.
• Index- type: String, Number. Identifier or index number in the source table. The default is the primary index. If the Source parameter is of the Document type, then the Index is interpreted as a string containing a list of fields in the tabular part of the document. If the Source is of type ValueTable, then this parameter is ignored.
• Columns- type: String, Number. List of columns separated by commas or column number to load in the source table. Defines a list of columns that will be loaded into the table. If no value is specified or an empty string is specified, the parameter is ignored. If the source is a Document type, this parameter is ignored (use the Index parameter).

Description: loads data from the specified table, according to the index. The current table is completely cleared before loading. Indexes are not copied.

LoadQuery

Syntax: LoadRequest(Request, [hFlag = 0], [hTotals = 1])

Options:

• Request- type: Request. The query from which the data will be loaded.
• hFlag- type: Number, String. 0 - values ​​of groups and functions (default); 1 - values ​​of groups and functions, additional variables; 2 - values ​​of group and function orderings; 3 - values ​​of ordering groups and functions, additional variables; Line - “Item(1), Item(2), Item, Warehouse, Income, Output”, where Item(1) is the value of the first ordering of the “Item” grouping.
• results- type: Number. 0 - do not display grouping results; 1 - display grouping results at the top (default); 2 - display results by groupings from below; 3 - display grouping results at the top and bottom.

Description: loads data from the specified request. The current table is completely cleared before loading.

InnerJoin

Syntax: InnerJoin(strColumnsLeftIT, RightIT, IndexRightIT, strColumnsRightIT)

Options:

• lineColumnsLeftIT
• Right IT
• IndexRightIT lineColumnsLeftIT).
• pageColumnsRightIT <ИмяКолИст>|<ИмяКолИст:ИмяКолНазн> [,...]". <ИмяКолИст> <ИмяКолНазн> <ИмяКолИст> <ИмяКолНазн>.

Description: Inner Join lineColumnsLeftIT pageColumnsRightIT. If a row in the left table is not found in the right table, then the row in the left table is deleted.

LeftJoin

Syntax: LeftConnection(strColumnsLeftIT, RightIT, IndexRightIT, strColumnsRightIT)

Options:

• lineColumnsLeftIT- type: String. Columns whose values ​​will be used to search for a row in the right table.
• Right IT- type: IndexedTable. Attached table.
• IndexRightIT- type: String, Number. The name or index number by which rows in the right table will be searched. The number of index columns must be equal to the number of search columns (see parameter lineColumnsLeftIT).
• pageColumnsRightIT- type: String. List of columns that will be copied from the right table to the left one. The list is specified in the format "<ИмяКолИст>|<ИмяКолИст:ИмяКолНазн> [,...]". <ИмяКолИст>- name of the column in the right table; if such a column does not exist in the left table, it will be created.<ИмяКолНазн>- if specified, then the value from the column of the right table with the name<ИмяКолИст>will be copied to the column of the left table<ИмяКолНазн>.

Description: Performs an operation similar to an SQL command Left Join. For each row from the left table (the left table is the current object), all rows from the right table that match the key expression are added. The key expression is determined by the parameter lineColumnsLeftIT. The values ​​of the columns specified in the parameter are transferred from the right table to the left one pageColumnsRightIT remain unchanged.

RightJoin

Syntax: RightConnection(strColumnsLeftIT, RightIT, IndexRightIT, strColumnsRightIT)

Options:

• lineColumnsLeftIT- type: String. Columns whose values ​​will be used to search for a row in the right table.
• Right IT- type: IndexedTable. Attached table.
• IndexRightIT- type: String, Number. The name or index number by which rows in the right table will be searched. The number of index columns must be equal to the number of search columns (see parameter lineColumnsLeftIT).
• pageColumnsRightIT- type: String. List of columns that will be copied from the right table to the left one. The list is specified in the format "<ИмяКолИст>|<ИмяКолИст:ИмяКолНазн> [,...]". <ИмяКолИст>- name of the column in the right table; if such a column does not exist in the left table, it will be created.<ИмяКолНазн>- if specified, then the value from the column of the right table with the name<ИмяКолИст>will be copied to the column of the left table<ИмяКолНазн>.

Description: Performs an operation similar to an SQL command Right Join. For each row from the left table (the left table is the current object), all rows from the right table that match the key expression are added. The key expression is determined by the parameter lineColumnsLeftIT. The values ​​of the columns specified in the parameter are transferred from the right table to the left one pageColumnsRightIT. If a row from the left table is not found in the right one, it is deleted. If the row of the right table is not in the left one, then it is added, and all columns except those specified in the parameter pageColumnsRightIT

FullJoin

Syntax: FullJoin(strColumnsLeftIT, RightIT, IndexRightIT, strColumnsRightIT)

Options:

• lineColumnsLeftIT- type: String. Columns whose values ​​will be used to search for a row in the right table.
• Right IT- type: IndexedTable. Attached table.
• IndexRightIT- type: String, Number. The name or index number by which rows in the right table will be searched. The number of index columns must be equal to the number of search columns (see parameter lineColumnsLeftIT).
• pageColumnsRightIT- type: String. List of columns that will be copied from the right table to the left one. The list is specified in the format "<ИмяКолИст>|<ИмяКолИст:ИмяКолНазн> [,...]". <ИмяКолИст>- name of the column in the right table; if such a column does not exist in the left table, it will be created.<ИмяКолНазн>- if specified, then the value from the column of the right table with the name<ИмяКолИст>will be copied to the column of the left table<ИмяКолНазн>.

Description: Performs an operation similar to an SQL command Full Join. For each row from the left table (the left table is the current object), all rows from the right table that match the key expression are added. The key expression is determined by the parameter lineColumnsLeftIT. The values ​​of the columns specified in the parameter are transferred from the right table to the left one pageColumnsRightIT. If the row of the left table is not found in the right one, then the values ​​of the columns of the left table remain unchanged. If the row of the right table is not in the left one, then it is added, and all columns except those specified in the parameter pageColumnsRightIT are filled with empty values.

Merge

Syntax: Merge(TZ [, Index = ""] [, Columns = ""])

Options:

• TK- type: IndexedTable, Value Table. The table from which the data will be loaded.
• Index- type: String, Number. Identifier or index number in the source table. The default is the primary index. If the ToR is of the Value Table type, then this parameter is ignored.
• Columns- type: String, Number. List of columns separated by commas or column number in the source table. Defines a list of columns that will participate in the merging process. If no value is specified or an empty string is specified, the parameter is ignored.

Description: adds rows from the source table to the current table. If the source table has columns that are not in the current one, they are created. The join is carried out taking into account the index and filter of the source table.

Intersection / Conjunction

Options:

• TZ2- type: IndexedTable. The table with which the intersection is constructed.
• Index of the Current TK
• IndexSecondTK
• flAddLinesTZ2

Description: intersection of two tables by key fields. The result of the intersection is a table in which there are rows of the current TK, which are in TK2, and rows of TK2, which are in the current TK (taking into account the Add Lines of TK2 fl.). The match of strings is determined by the indices IndexCurrentTK and IndexTK2. The number of columns in indexes must be the same.

Difference

Options:

• TZ2- type: IndexedTable. The table from which the difference is constructed.
• Index of the Current TK- type: String, Number. The name or index number of the current table.
• IndexSecondTK- type: String, Number. Name or index number in TZ2.
• flAddLinesTZ2- type: Number. Flag for adding TZ2 lines to the result.

Description: difference between two tables in key fields. The result is a table in which there are lines of the current TK, which are not in TK2, and lines of TK2, which are not in the current TK (taking into account the Add Lines of TK2 fl.). The match of strings is determined by the indices IndexCurrentTK and IndexTK2. The number of columns in indexes must be the same.

Copy

Syntax: Copy([hCopyIndices = 0], [hCopyData = 1])

Options:

• hCopyIndices- type: Number. If 1, then indexes and filters will also be copied.
• hCopyData- type: Number. If 1, then the data will be copied; if 0, then only the structure is copied.

Returns: type: IndexedTable. A variable that is a copy of an object.

Description: creates a copy of the table.

VFile / SaveToFile

Syntax: VFile(strFileName, [hFormat = 1], [Index = ""], [hColumnNames = 1])

Options:

• strFileName- type: String. The name of the file to which the table should be written. If the file already exists, it is overwritten.
• hFormat
• Index
• hSINamesColumns- type: Number. 1 - the first line of the file will contain the names of the columns. Only makes sense for csv files.

Description: writes the table to a file.

FromFile / LoadFromFile

Syntax: FromFile(strFileName, [hFormat = 1], [hColumnNames = 1])

Options:

• strFileName- type: String. The name of the file from which the table should be read.
• hFormat- type: Number. File format. Can take the following values: 1 - record values ​​in internal format; 2 - recording values ​​in an external format; 3 - csv file with values ​​in internal format; 4 - csv file with values ​​in an external format.
• hSINamesColumns- type: Number. 1 - the first line of the file stores the names of the columns. Only makes sense for csv files. If parameter = 0, then column names are formed as __col__<НомерКолонки>.

Returns: type: Number. 1 - table read successfully; 0 - reading error.

Description: reads a table from a file.

INString / SaveToString

Syntax: INString([hFormat = 1], [Index = ""])

Options:

• hFormat- type: Number. 1 - recording values ​​in internal format; 0 - writing values ​​in an external format.
• Index- type: String, Number. Identifier or index number. The default is the primary index. Uploading to a file is carried out taking into account sorting and filtering (if any) according to the specified index.

Returns: type: String. Row representation of the table.

Description: Converts a table to a string representation for serialization. The resulting string can subsequently be used in the FromString() method.

FromString / LoadFromString

Syntax: FromString(strRepresentation)

Options:

• pagePresentation- type: String. Row representation of the table.

Description: loads a table from a string representation.

ChooseLine

Syntax: SelectRow([Row = 0], [strWindowTitle = ""], [hTimeout = 0], [Index = ""])

Options:

• Line- type: any. The variable where the value is placed is the number of the selected line. When calling the method here, you can pass the value of the starting line number.
• pageTitleWindow- type: String. Title of the selection window.
• hTimeout- type: Number. The time the system waits (in seconds) for a user response. If not specified, the wait time is infinite.
• Index

Returns:

Description:

Show / Show

Syntax: Show([Index = ""])

Options:

• Index- type: String, Number. Identifier or index number. The default is the primary index. In the selection window, rows will appear taking into account the sorting and filter (if any) by the specified index.

Returns: type: Number. 1 - if the selection is made (the "OK" button is pressed); 0 - if the selection is not made (the "CANCEL" button is pressed).

Description: Opens a window for interactively selecting a row in a table of values. The method is intended mainly for debugging.