Dataprovider class. "Data provider" class Configuring indexes using regular platform tools

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

Such acceleration is simply impossible to achieve in 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 you can do this in several different ways.

In the video tutorials, we will look at several ways to create an index. We will also consider the situation when an index of the required composition cannot be created by the standard means of the platform and it will have to be created in the DBMS.

Configuring indexes using regular platform tools

The lesson shows which indexes are actually created for objects at the DBMS level.
In this topic, not everything is as obvious as it might seem at first glance. Indeed, for a number of objects there are features of creating indexes.
We will consider all the nuances in this video.

Indexing with additional ordering

The video shows the difference between the index building option Index from Index with add. streamlining.
The example shows how the platform will build an index when using additional ordering.

Create an index for register dimensions

The indexing of the first register dimension has several nuances.
The video shows which indexes are created for register dimensions. The situation of indexing the first dimension of the register is also considered.

NewColumn / NewColumn

Syntax: NewColumn(stringColumnName)
Parameters:
streamNameColumns- type: String. The ID of the column to be created. Any characters can be used in the column identifier, but it should be remembered that the characters "-", "*", "#", "^" and "&" are used as modifiers when building index expressions, and it will be possible to build an index on columns with such characters impossible.

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

Description: Creates a column in a table.

RenameColumn / RenameColumn

Syntax: RenameColumn(Column, strNewColumnName)
Parameters:
Column

pageNewNameColumns- type: String. The new column ID.

Description: Renames a table column.

ColumnCount / ColumnCount

Syntax: Number of Columns()

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

Description: Returns the number of columns in the table.

ColumnName / ColumnName

Syntax: ColumnName(Column)
Parameters:
Column- type: String, Number. Column identifier or number.

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

Description: Returns the ID of the specified column, as specified in the NewColumn() method.

ColumnNumber / ColumnNumber

Syntax: NumberColumn(Column)
Parameters:
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 line number created.

Description: Creates a new row at the end of the table.

RemoveRows / RemoveRows

Syntax: DeleteRows([Index])
Parameters:
Index If the parameter is specified, and a filter is set for this index, then the rows that satisfy 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.

RowCount / RowCount

Syntax: Number of Rows([Index = ""], [flOnlyUnique = 0])
Parameters:
Index- type: String, Number. ID or index number. If the parameter is specified, and a filter is set for this index, then only rows that satisfy the filter conditions are considered.

flOnlyUnique- type: Number. Flag for considering only unique index values.

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

Description: Returns the number of rows in the table.

RowNumber / RowNumber

Syntax: RowNumber(Index)
Parameters:
Index- type: String, Number. identifier or index number.

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

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

Clear / Cleanup

Syntax: clear()

Description: Complete cleaning of the table. All indexes, rows and columns are removed.

AddIndex / AddIndex

Syntax: AddIndex(strID, strExpression, [hOnlyUniqueValues = 0])
Parameters:
strID- type: String. ID of the index being created;

p Expression- type: String. index expression. The index expression consists of a comma-separated list of column identifiers. If the column name is preceded by a "-" character, then sorting is done in reverse order. If the column name is preceded by the symbol "*", then the sorting is carried out according to the internal representation of the object. If the column name is preceded by the "#" symbol, spaces are trimmed from the left and right before string comparison. If the column name is preceded by the "^" character, then the strings are compared case insensitively. Sort 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 (you cannot set a filter on such an index).

wOnlyUniqueValues- 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 they will not be included in the operations of iterating over rows, summing, folding, unloading, loading.

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

Description: Adds an index to a table.

Number ofIndexes / IndexCount

Syntax: NumberIndex()

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

Description: Returns the number of indexes in the table.

IndexName / IndexName

Syntax: IndexName(Index)
Parameters:
Index- type: String, Number. ID or index number.

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

IndexExpr / IndexExpr

Syntax: IndexExpression(Index)
Parameters:
Index- type: String, Number. ID or index number.

Returns: type: String. index expression. If the index is not in the table, an exception is thrown.
Description: Returns the index expression for the given index.

IndexNumber / IndexNumber

Syntax: IndexNumber(Index)
Parameters:
Index- type: String, Number. ID 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 / Sort

Syntax: Sort(strExpression)
Parameters:
p Expression- type: String. index expression. The index expression consists of a comma-separated list of column identifiers. If the column name is preceded by a "-" character, then sorting is done in reverse order. If the column name is preceded by the symbol "*", then the sorting is carried out according to the internal representation of the object. If the index expression is an empty string, then sorting is disabled (a filter cannot be set on such an index).

Description: Change sorting in the main index.

IndexIsUnique

Syntax: IndexUnique([Index = ""])
Parameters:
Index- type: String, Number. ID 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.

Number of UniqueKeys / UniqueKeyCount

Syntax: Number ofUniqueKeys([Index = ""])
Parameters:
Index- type: String, Number. ID or index number.

Returns: type: Number. The number of unique keys.

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

KeyValueCount / KeyValueCount

Syntax: Number of KeyValues([Index = ""], Key)
Parameters:
Index- type: String, Number. ID 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])
Parameters:
Index- type: String, Number. ID 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 it must be a ValueList containing the values of the key columns specified in the AddIndex() method.

hFindLatest- type: Number. If 1, then the last row with the matching key value will be found.

hPosition

Returns:

Description: Find a string that exactly matches the key.

Find NearestMore / FindNearestGE

Syntax: FindNearestMore(Key, [Index=""], [hPosition=0])
Parameters:
Key- type: any. If the index is built on one column, then the search value. If the index is on several columns, then it must be a ValueList containing the values of the key columns specified in the AddIndex() method.

Index

hPosition- type: Number. If 1, then on successful search the index will be positioned on the found row. If a selection was opened before the method execution (using the SelectRows method), then it is repositioned to the found value. If the selection has not been opened, then it is opened, and after a successful search, the GetRow(), NextRow() and PreviousRow() methods can be used. The main index is also repositioned.

Returns: type: Number. Line number found. If nothing is found, then 0.

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

FindNearestLess / FindNearestLE

Syntax: FindNearestLess(Key, [Index=""], [hPosition=0])
Parameters:
Key- type: any. If the index is built on one column, then the search value. If the index is on several columns, then it must be a ValueList containing the values of the key columns specified in the AddIndex() method.

Index- type: String, Number. ID or index number. By default, the main index is used.

hPosition- type: Number. If 1, then on successful search the index will be positioned on the found row. If a selection was opened before the method was executed (Select Rows method), then it is repositioned to the found value. If the selection has not been opened, then it is opened, and after a successful search, the GetRow(), NextRow() and PreviousRow() methods can be used. The main index is also repositioned.

Returns: type: Number. Line number found. 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], [hInvert = 0])
Parameters:
KeyMin- type: any. Lower bound of the filter. It is set in the same way as in the FindString() method.

KeyMax- type: any. The upper limit of the filter. It is set in the same way as in the FindString() method.

Index- type: String, Number. ID or index number. By default, the main index is used.

chOnlyUnique- 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 value of the column 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 be with an open border.

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

Subset / Subset

Syntax: Subset(szKey, hNumberFixColumns, [Index = ""])
Parameters:
csKey- type: List of Values. A list containing the values of the fixed index columns.

hNumberFixColumns- type: Number. Number of fixed index columns

Index- type: String, Number. ID or index number. By default, the main index is used.

Description: sets the filter by fixing the values of the first columns of the index.

TK.AddIndex("i1", "k1, k2"); csKey = CreateObject("List of Values"); csKey.AddValue(5); TK.Subset(szKey, 1, "i1");
as a result, the selection will contain only those rows whose column k1 = 5.

DropFilter / DropFilter

Syntax: Turn offFilter([Index = ""])
Parameters:
Index- type: String, Number. ID or index number.

Description: turns off the filter at the specified index.

FirstRow

Syntax: AtStart([Index = ""], [flOnlyUniques = 0])
Parameters:
Index- type: String, Number. ID or index number. By default, the main index is used.

chOnlyUnique

returns

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

SelectRows / SelectRows

Syntax: SelectRows([Index = ""], [flOnlyUnique = 0])
Parameters:
Index- type: String, Number. ID or index number. By default, the main index is used.

chOnlyUnique- type: Number. Selection flag for unique values. The flag affects the result of all subsequent calls to the GetLine(), NextLine(), PreviousLine() methods for the given 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.

VEnd / LastRow

Syntax: VEnd([Index = ""], [flOnlyUniques = 0])
Parameters:
Index- type: String, Number. ID or index number. By default, the main index is used.

chOnlyUnique- type: Number. Selection flag for unique values. The flag affects the result of all subsequent calls to the GetLine(), NextLine(), PreviousLine() methods for the given 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 that the PreviousRow() method is positioned on the last row.

NextLine / Next

Syntax: NextRow([Index = ""], [hOnlyUnique = 0])
Parameters:
Index- type: String, Number. ID or index number. By default, the main index is used.

chOnlyUnique

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])
Parameters:
Index- type: String, Number. ID or index number. By default, the main index is used.

chOnlyUnique- type: Number. 1 - the next row with a different key value will be received, all duplicate rows will be skipped.

returns type: Number. 1 - operation completed successfully, 0 - no more rows.

Description: move to the next index line.

Note:

PreviousLine / Previous

Syntax: PreviousRow([Index = ""], [hOnlyUnique = 0])
Parameters:
Index- type: String, Number. ID or index number. By default, the main index is used.

chOnlyUnique- type: Number. 1 - the next row with a different key value will be received, all duplicate rows will be skipped.

returns type: Number. 1 - operation completed successfully, 0 - 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 / GetValue

Syntax: GetValue([hString = EmptyValue], Column)
Parameters:
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([hString = EmptyValue], Column, Value)
Parameters:
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. The new cell value.

Description: sets the value of the specified cell.

Set

Syntax: Set(hRow, Column, Value)

Description: a synonym for the SetValue() method.

Collapse / GroupBy

Syntax: Collapse(strCollapseColumns, strSumColumns [, ExistingIndexName = ""])
Parameters:
strColumnsConvolution- type: String. The list of columns by which the rollup will be performed. This line is completely analogous to the index expression in the AddIndex() method. For folding, the table is first indexed by this expression, and then the rows with the same index are summed. The result is always placed on the first line by number, all other lines are deleted.

strColumnsSum- type: String. List of columns to be summed. The separator is the character ",". Spaces are ignored.

NameExistingIndex- type: String, Number. If an index name is specified, then CollapseColumns are ignored, and the collapse is performed at the specified index. Only those rows that are included in the filter participate in the rollup.

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

Total / Sum

Syntax: Total(ColumnAmount, [Index = ""])
Parameters:
ColumnAmounts

Index- type: String, Number. ID or index number. By default, the main index is used.

returns

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

TotalByRange / SumByRange

Syntax: TotalByRange(ColumnAmount, [Index = ""],KeyMin,KeyMax)
Parameters:
ColumnAmounts- type: String, Number. The column for which you want to get the amount.

Index- type: String, Number. ID or index number. By default, the main index is used.

KeyMin- type: any. The lower limit of the range. It is set in the same way as in the FindString() method.

KeyMax- type: any. The upper limit of the range. It is set in the same way as in the FindString() method.

returns type: Number. The amount for the specified column.

Description: calculates the sum for the specified column, with the summation range specified. The function does not take into account the installed filters and does not install permanent filters itself.

TotalByNode / NodeSum

Syntax: TotalByNode(ColumnSum[, Index])
Parameters:
ColumnAmounts- type: String, Number. The column for which you want to get the amount.

Index- type: String, Number. ID or index number. By default, the main index is used.

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

Description: calculates the sum of 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.

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

Group

Syntax: Group(strGroup, strColumnsSum, [hExplanationLastLevel = 0])
Parameters:
strgroupings- type: String. A string describing the desired grouping structure. Set in the form<ИмяИндекса1>: <ИндексноеВыражение1> [; <ИмяИндекса2>: <ИндексноеВыражение2>...]. IndexExpression - a string in the same format as for the AddIndex() method, with one addition: if the "&" symbol is present in the list of column modifiers, then the totals for the lookup groups will be calculated for this column. There can be only one column with this modifier at each grouping level.

strColumnsSum- type: String. A line with columns for which sums are to be calculated.

hDecryptionLastLevel- 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 its untouched form. 0 - the last level will be collapsed.

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

Example: at the input we have a table

group it:
TK.Group("Last name: Last name; First name: First name", "Amount");
and we get a table of this kind (the column dzDescendants shows the contents of the table dzDescendants):

When grouping, it is also possible to calculate sums by groups of directories - for 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 as a tree that exactly repeats the structure of the directory. The next level of grouping is located in the leaves of this tree (i.e. (i.e., in rows that are not groups). For the convenience of working with the resulting tree, the column "__ThisGroup__" is added to the table, in which 1 is written for all groups, and EmptyValue for elements. The column "__Level__" is also added, which is filled with the value of the group level of the directory (numbering starts from 1) For elements, EmptyValue is written in the column "__Level__".

FillRow / FillRow

Syntax: FillRow([hRowNumber = 0], Source, [hSourceRowNumber = 0], [hByColumnNames = 0])
Parameters:
hLineNumber- type: Number. Line number to fill in. If<= 0, то заполняется текущая строка.

A source- type: IndexedTable, ValueTable, List of Values. The data source to populate the row.

hNumberRowSource- type: Number. The row number in the source table. The default is to take the current row at a predefined index. If Source is a List of Values, 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 there is no column with the same name in the receiver, then the value is not copied. If Source is a List of Values, then the names of the columns are symbolic representations of the values.

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

FillColumn / FillColumn (option 1)

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

Column

A source- type: IndexedTable. The source of data to populate the column.

SourceIndex- type: String, Number. The identifier or index number by which rows in the source table are ordered and filtered. By default, the main index is used.

ColumnSource- type: String, Number. 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: populates a table column from another indexed table column.

FillColumn / FillColumn (option 2)

Syntax: FillColumn([Index = ""], Column, Source, [SourceColumn = ""])
Parameters:
Index- type: String, Number. The ID or index number by which the rows of the populated table are sorted and filtered. By default, the main index is used.

Column- type: String, Number. Identifier or number of the column to be filled.

A source- type: ValueTable, ValueList. The source of data to populate the column.

ColumnSource- 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 another table column (ValueTable) or a list of values.

FillColumn / FillColumn (option 3)

Syntax: FillColumn([Index = ""], Column, Value)
Parameters:
Index- type: String, Number. The ID or index number by which the rows of the populated table are sorted and filtered. By default, the main index is used.

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 / Unload

Syntax: Upload(Destination [, Index = ""] [, Columns = ""] [, hOnlyUnique = 0])
Parameters:
Receiver- type: IndexedTable, ValueTable, Document, ValueList, Undefined. The variable into which the table will be unloaded. If this is a document, then its tabular part is filled. If the type is Undefined, then a table of the IndexedTable type is created.

Index- type: String, Number. ID or index number. By default, the main index is used.

speakers- type: String, Number. List of columns separated by commas or column number to upload. If no value is specified or an empty string is specified, the parameter is ignored.

chOnlyUnique- type: Number. Flag for unloading only unique values.

Description: dumps the current table into another table, or into a new one. If the target 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 = ""])
Parameters:
A source- type: IndexedTable, ValueTable, 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. An identifier or index number in the source table. By default, the main index is used. If the Source parameter is of the Document type, then the Index is interpreted as a string containing a list of fields in the document's tabular section. If the Source is of type ValueTable, then this parameter is ignored.

speakers- type: String, Number. List of columns separated by commas or column number to load in the source table. Defines a list of columns to be loaded into the table. If no value is specified or an empty string is specified, the parameter is ignored. If the source is of type Document, this parameter is ignored (use the Index parameter).

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

LoadQuery / LoadQuery

Syntax: LoadRequest(Request, [hFlag = 0], [hTotal = 1])
Parameters:
Inquiry- type: Request. The request from which the data will be loaded.

hFlag- type: Number, String. 0 - values of groups and functions (by default); 1 - values of groups and functions, additional variables; 2 - values of orderings of groups and functions; 3 - values of orderings of groups and functions, additional variables; String - "Item(1), Item(2), Item, Warehouse, Incoming, Expenditure", where Item(1) is the value of the first ordering of the "Item" grouping.

results- type: Number. 0 - do not display totals by groupings; 1 - show totals by groupings at the top (default); 2 - show totals by groupings at the bottom; 3 - output the totals by grouping from above and below.

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

InnerJoin / InnerJoin

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

RightIT

IndexRightIT pColumnsLeftIt).

pColumnsRightIt <ИмяКолИст>|<ИмяКолИст:ИмяКолНазн> [,...]". <ИмяКолИст> <ИмяКолНазн> <ИмяКолИст> <ИмяКолНазн>.

Description: Inner Join pColumnsLeftIt pColumnsRightIt. If the row of the left table is not found in the right table, then the row in the left table is deleted.

LeftJoin / LeftJoin

Syntax: LeftJoin(strColumnsLeftIT, RightIT, IndexRightIT, strColumnsRightIT)
Parameters:
pColumnsLeftIt- type: String. Columns whose values will be used to search for a row in the right table.

RightIT- type: IndexedTable. Attached table.

IndexRightIT- type: String, Number. The name or number of the index 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 pColumnsLeftIt).

pColumnsRightIt- type: String. List of columns that will be copied from the right table to the left. The list is in the format "<ИмяКолИст>|<ИмяКолИст:ИмяКолНазн> [,...]". <ИмяКолИст>- column name in the right table; if there is no such column 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 a SQL command left join. For each row from the left table (left table is the current object), all rows from the right table that match the key expression are added. The key expression is defined by the parameter pColumnsLeftIt. The values of the columns specified in the parameter are transferred from the right table to the left one. pColumnsRightIt remain unchanged.

RightJoin / RightJoin

Syntax: RightJoin(strColumnsLeftIT, RightIT, IndexRightIT, strColumnsRightIT)
Parameters:
pColumnsLeftIt- type: String. Columns whose values will be used to search for a row in the right table.

RightIT- type: IndexedTable. Attached table.

IndexRightIT- type: String, Number. The name or number of the index 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 pColumnsLeftIt).

pColumnsRightIt- type: String. List of columns that will be copied from the right table to the left. The list is in the format "<ИмяКолИст>|<ИмяКолИст:ИмяКолНазн> [,...]". <ИмяКолИст>- column name in the right table; if there is no such column 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 a SQL command right join. For each row from the left table (left table is the current object), all rows from the right table that match the key expression are added. The key expression is defined by the parameter pColumnsLeftIt. The values of the columns specified in the parameter are transferred from the right table to the left one. pColumnsRightIt. If the row of the left table is not found in the right table, then it is deleted. If the row of the right table is missing in the left one, then it is added, while all columns except those specified in the parameter pColumnsRightIt

FullJoin / FullJoin

Syntax: FullJoin(strColumnsLeftIT, RightIT, IndexRightIT, strColumnsRightIT)
Parameters:
pColumnsLeftIt- type: String. Columns whose values will be used to search for a row in the right table.

RightIT- type: IndexedTable. Attached table.

IndexRightIT- type: String, Number. The name or number of the index 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 pColumnsLeftIt).

pColumnsRightIt- type: String. List of columns that will be copied from the right table to the left. The list is in the format "<ИмяКолИст>|<ИмяКолИст:ИмяКолНазн> [,...]". <ИмяКолИст>- column name in the right table; if there is no such column 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 a SQL command Full Join. For each row from the left table (left table is the current object), all rows from the right table that match the key expression are added. The key expression is defined by the parameter pColumnsLeftIt. The values of the columns specified in the parameter are transferred from the right table to the left one. pColumnsRightIt. If the row of the left table is not found in the right table, then the values of the columns of the left table remain unchanged. If the row of the right table is missing in the left one, then it is added, while all columns except those specified in the parameter pColumnsRightIt filled with empty values.

Merge / Merge

Syntax: Merge(Tr [, Index = ""] [, Columns = ""])
Parameters:
TK- type: IndexedTable, ValueTable. The table from which the data will be loaded.

Index- type: String, Number. An identifier or index number in the source table. By default, the main index is used. If the TK is of the ValueTable type, then this parameter is ignored.

speakers- type: String, Number. List of columns separated by commas or column number in the source table. Specifies the 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, then they are created. Merging is carried out taking into account the index and filter of the source table.

Intersection / Conjunction

Parameters:
TK2- type: IndexedTable. The table with which the intersection is built.

Index of Current TK

IndexSecondTK

flAddLinesTk2

Description: intersection of two tables on key fields. The result of the intersection is a table that contains the rows of the current TM that are in TM2, and the rows of TM2 that are in the current TM (taking into account the AddRows of TM2 fl). The strings match is determined by the indexCurrentTW and IndexTW2. The number of columns in indexes must be the same.

Difference / Difference

Parameters:
TK2- type: IndexedTable. The table with which the difference is built.

Index of Current TK- type: String, Number. The name or index number in the current table.

IndexSecondTK- type: String, Number. Name or index number in TK2.

flAddLinesTk2- type: Number. Flag for adding TK2 lines to the result.

Description: difference of two tables on key fields. The result is a table that contains rows of the current TM that are not in TM2, and rows of TM2 that are not in the current TM (taking into account the AddTr2Rows flag). The strings match is determined by the indexCurrentTW and IndexTW2. The number of columns in indexes must be the same.

Copy / Copy

Syntax: Copy([hCopyIndexes = 0], [hCopyData = 1])
Parameters:
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.

WFile / SaveToFile

Syntax: WFile(strFileName, [hFormat = 1], [Index = ""], [hSIColumnNames = 1])
Parameters:
streamFileName- type: String. The name of the file to write the table to. If the file already exists, it is overwritten.

hFormat

Index

hSInamesColumns- type: Number. 1 - column names will be written to the first line of the file. It only makes sense for csv files.

Description: writes the table to a file.

FromFile / LoadFromFile

Syntax: FromFile(strFileName, [hFormat = 1], [hWithColumnNames = 1])
Parameters:
streamFileName- type: String. The name of the file to read the table from.

hFormat- type: Number. File format. It can take the following values: 1 - write values in the internal format; 2 - writing values in an external format; 3 - csv file with values in internal format; 4 - csv file with values in external format.

hSInamesColumns- type: Number. 1 - the first line of the file contains the names of the columns. It only makes sense for csv files. If parameter = 0, then column names are formed as __col__<НомерКолонки>.

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

Description: reads a table from a file.

Instring / SaveToString

Syntax: INString([hFormat = 1], [Index = ""])
Parameters:
hFormat- type: Number. 1 - write values in the internal format; 0 - write values in external format.

Index- type: String, Number. ID or index number. By default, the main index is used. Uploading to a file is performed taking into account sorting and filtering (if any) by the specified index.

Returns: type: String. The string representation of the table.

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

FromString / LoadFromString

Syntax: FromString(strRepresentation)
Parameters:
strRepresentation- type: String. The string representation of the table.

Description: loads a table from a string representation.

ChooseLine / ChooseLine

Syntax: SelectRow([Row = 0], [strWindowTitle = ""], [hTimeout = 0], [Index = ""])
Parameters:
Line- type: any. The variable where the value is placed is the number of the selected row. When calling the method, you can pass the value of the starting line number here.

pageTitleWindow- type: String. The title of the selection window.

hTimeout- type: Number. The amount of time the system waits (in seconds) for a user response. If not set, then the timeout is infinite.

Index

Returns:

Description:

Show / Show

Syntax: Show([Index = ""])
Parameters:
Index- type: String, Number. ID or index number. By default, the main index is used. Rows will appear in the selection window, sorted and filtered (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.

Vandalsvq 1114 08.11.14 17:00 Now in the 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 conditional design, as well as grouping, but alas. There is only one reason for this - I do not deal with 7 on a commercial basis and therefore do not finalize these tools. There is nothing even elementary to test. Earlier and sql bases were both file and different configurations in bulk. Now it seems like only one on which there was development and that one was opened a very long time ago.

Anyway, here are the latest changes:
5.0.1 dated 04/15/12
- Changed the selection system in dynamic extensions (Directory, Document, Journal, Register, Operations, Postings). Added the possibility of arbitrary construction of conditions with a hierarchy.
- In connection with the change in the selection system, changes were made to the extension objects "Data Provider. Log", "Data Provider. Transactions", "Data Provider. Documents", "Data Provider. Directory", "Data Provider. Operations", "Data Provider. Register".
- Fixed behavior of the "Select by current column" button. When pressed, other selections are not disabled. Also, if selection is enabled, it is disabled.
- When a column is activated, the display of the "Select by current column" button changes.
- Optimized the construction of the conditions for selections by details.
- Fixed a bug where the current row was lost when changing the hierarchical view of the "DataProvider.Directory" extension object.
- Disabled use of the "Display Selection Icon" option. The parameter is left for backwards compatibility.
- The "Data Provider. Type Description" and "Data Provider. Comparison Types" objects are removed from the data provider. Instead, the common objects "Description of Types" and "Types of Comparison" are used, respectively. For details, 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 bug when stacking 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 processing of movements" attribute
- Fixed identified bugs.

5.0.3 dated 05/22/13
- The "DataProvider.DirectQuery" object has been updated to version 1.8.6 dated 05/22/13.
- all classes are collected together in one archive + examples

5.0.4 of 10/25/13
- Fixed identified bugs

5.0.5 from 05/23/14
- Fixed bugs when adding data columns
- Added the ability to build a tree structure in the available fields when adding data columns (in fact, now you can use the "Selection" object to build selections like SKD in 8-ke, but a must have file)

or

Why should a 1C developer “index” register measurements and details?

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

The short answer to the title's question is that this will allow queries to run quickly and reduce the negative impact of locks on .

What is an index?

Index placement optimization

When the volume of tables does not allow them to "fit" in the server's RAM, 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 of actions http://technet.microsoft.com/ru-en/library/ms175905.aspx
Using an index from a different filegroup improves the performance of non-clustered indexes due to the parallelism of I/O and the index itself.
To determine the dimensions, you can use the above mentioned processing.

Influence of indexes on locks

The absence of the necessary index for the query means iterating over all the table records, which in turn leads to excessive locks, i.e. redundant records are blocked. Also, the longer a query runs due to missing indexes, the longer the locks are held.
Another reason for locks is a small number of records in tables. In this regard, SQL Server, when choosing a query execution plan, does not use indexes, but bypasses the entire table (Table Scan), blocking entirely. In order to avoid such locks, it is necessary to increase the number of records in the tables up to 1500-2000. In this case, table scans become more expensive and SQL Server starts using indexes. Of course, this can not always be done, a number of directories such as "Organizations", "Warehouses", "Subdivisions", etc. usually have few entries. In these cases, indexing will not improve performance.

Index efficiency

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

Queries that specify "narrow" search criteria. Such queries should read only a small number of rows that meet certain criteria.
Queries that specify a range of values. These queries must also read a small number of rows.
The lookup that is used in link operations. Columns, which are often used as bind keys, are great for indexes.
A search that reads data in a specific order. If the result dataset is to be sorted in clustered index order, then no sorting is needed because the result dataset is already pre-sorted. For example, if the clustered index is built on the lastname, firstname columns, 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, for all the usefulness of indexes, there is one very important BUT - the index should be "efficiently used" and should allow you to find data using fewer I / O operations and system resources. Conversely, unused (rarely used) indexes are more likely to degrade data write speed (since each operation that changes data must also update index pages) and create excess database space.

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

In order to determine the effectiveness of an index, we can roughly evaluate using a free online service showing the "query execution plan" and the indexes used.

Question: Indexed Table of Values

The question is, is it possible to make the Table of Values on the dialog form indexed and then work with it, as with an indexed table?
Or is it better to work separately with the ITZ and then unload the results into the Table of Values visible on the form?
And the second question is how to make sure that when unloading the ITZ into the Table of Values, the column format does not fly off?
PySy -component 1s ++ costs, if cho.

Answer:() after the cooldown of the natahouse, the gingerbread is nowhere to be found, unless of course it's just a subject from a joke

Question: Working with a table of values

Good day to you, dear programmers. I ask for help in solving the problem in 1C, I myself, sorry, this is not boom-boom
I tried to do it myself, but I didn’t even have the mind to compile the example

1. Place a table of values in the dialog form.
2. In the form module, describe the columns of the table: Product, Quantity, Price, Amount.
3. Place on the dialog form the details of the dialogue "Product", "Price", "Quantity" and the Add line button, which adds a new line to the table of values and fills it with the values of the corresponding details. The amount must be calculated as the product of the price and the quantity.
4. Add a Delete Row button that deletes the current row of the value table. Consider the situation of an empty table of values.
5. Add the Collapse button, which should sum up the amount and quantity by goods, in the Price column the average price of the goods should be calculated.
6. Add an Expand button that restores the state of the value table before the collapse.
7. Add a Sort button to sort rows by product.

Thank you in advance!

Answer: platform? Managed or normal mode?
In the synth room "Table of values"

Question: 8.2 - Get data from a calculated field of a table of values

I don't know how to really explain.
The form has a table field. In the procedure Upon Receiving Data (Element, Row Appearances) into a separate
column I calculate the value:

For Each Row-Design From Row-Design Loop.... Row-Design.Cells.Shipped.Value = Formula Here.... EndCycle

So how do I iterate through the rows in the value table and get the data from that cell?

Answer:

you are confusing concepts. the tabular field is also a tabular field - TZ does not smell here. as well as you calculate the same and bypass

Question: SKD: Hierarchy from the table of values

Hello!
Tell me how to create a hierarchy from a table of values like this

You need to somehow get the tree out of it. So far it works programmatically, but I think that such a tree can be completely built in the skd.

Answer: Well, programmatically you create a Value Tree object and drag the owner's handles (nodes) and subordinate elements (strings) there.

And this data is only in the form of a table of values, but not elements of a hierarchical reference book? If it's a hierarchical directory, then it's as easy as shelling pears.

And if you need it from the table of values, creating it as you like, then do this:

You define in the query a temporary table where you place your TK.
Then you do selection on it with connection of a line with the parent.

Question: Transferring the Table of Values to a common module

Good afternoon!
Newbie in 1C. I study version 3.0 Thin client. There is an external managed form. I want to take out the table of values in the general modules. Stuck, please help:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

& OnServer Procedure Execute QueryOnServer() 3= New request; H. Text = "SELECT | Outgoing Cash Order | ExpenditureCash OrderDecryption of Payment. Amount of Payment| FROM | document; Tz \u003d Z. Run (). Unload() ; //Output query result to form table TF(Tz); EndProcedure & OnServer Procedure TF(Tz) CommonFunctions. Fill in the Form Table (Tz) End of Procedure

General module:

Error:(Form.Form.Form(23)): Error calling context method (FillFormTable)
GeneralFunctions.FillFormTable(Tz)
because of:
Type mismatch (parameter number "1") (Invalid parameter type. Only primitive types, references, struct are allowed)
How to transfer TK for further processing?

Answer:

Message from Prsvet

Why does the type transfer a number, and when transferring a TK, it is necessary to set the reuse of returned values \u200b\u200bnot to be used?

Because,

Message from Prsvet

reusing return values

Cached on the client and the value table is not serialized on the client

Added after 32 seconds
This setting must be handled with care!

Added after 35 seconds
Prsvet if you don't know how this option works, then don't use it!

Question: Table of values with cyclic filling of columns

All the best.
The task is to display a table of income/expenditure of goods for the selected period with a weekly breakdown. I decided to implement it as a Value Table (at first I thought about making it a report, but I didn’t find how). Since the number of columns is not known in advance, the table is generated programmatically:
//*** Description of the TK as a form attribute ChoiceType array = new array; arrayChoiceType.Add(type("table of Values")); ChoiceTypeDescription = new TypeDescription(ChoiceType array); arrayAttributes = new array; arrayAttributes.Add(new Form attribute("FormForm", ChoiceType description, "", "FormForm")); //*** description of types CF = new QualifiersNumbers(10,2); cs = newStringQualifiers(100); mas = new array; wt.Add(type("String")); StringType = newTypeDescription(mass, cs); mass.Clear(); mas = new array; wt.Add(type("Number")); TypeNumber = newDescriptionTypes(wt, cn); mass.Clear(); // *** //create a temporary table of values ТЗ = new Table of Values; TK.Columns.Add("product", TypeString, "product"); for n=1 by Number of Weeks cycle TK.Columns.Add("arrival" + n, TypeNumber, "arrival" + n); TK.Columns.Add("expense" + n, TypeNumber, "expense" + n); EndCycle; for each column from ts.Columns loop array of Attributes.Add(new FormAttribute(column.Name, column.ValueType, "TKForm")); EndCycle; changeAttributes(array ofAttributes); table of ChoiceFields = Elements.Add("TKForm", type("FormTable")); table of ChoiceFields.PathToData = "TKForm"; tableChoiceFields.Display = displayTable.List; for each column from TK.columns loop newItem = elements.Add(column.Name, type("FormField"), ChoiceFields table); newItem.View = FormFieldView.InputField; newItem.DataPath = "TRForm." + Column.Name; newElement.Width = 10; EndCycle;
Then the name column is filled in:
Zaprnom = new request; lockNo.Text = "select GoodsIn WarehousesTurnovers.Nomenclature AS nomenclature | from Accumulation Register.GoodsInWarehouses.Turnovers() AS GoodsInWarehousesTurnovers"; TZNom = zaprNom.Execute().Unload(); for each strNom from TZNom cycle strNom = strNom.nomenclature; //add this nomenclature to the temporary TK StrTZ = TK.Add(); StrTZ.product \u003d strNom; EndCycle;
Finally, we unload the goods from the temporary TOR into the TOR of the form:
ValueVFormAttribute(TM, "TMForm");
The problem is the cyclic filling of receipts and expenditures. If I understand correctly, each row of this data is filled with a loop nested in the loop for filling the products column. That is, at each iteration of filling in the line with the goods, we make a request for income and expense, in which we pass the name of the goods, the dates of the beginning and end of the period. The result of the query is unloaded into a table of values, which eventually has one row. This works when data is shown for the entire period, that is, when there are three columns: product, income, expense.
But in this case, two columns are required for each week. Let the names of these columns differ by the number of the week, that is, income1, expense1, income2, expense2, and so on. It is clear that in this case a cycle is organized in which in the request for income and expense, instead of the end of the period, the date that was at the beginning of the iteration + week is transmitted.
The question arises when transferring the amounts of income and expense from the table of values based on the results of the query to a temporary table. How to describe that in each iteration, when uploading data from the query result table to the temporary TK, the number in the name of the income and expense columns is increased by 1? After all, in general, the unloading process looks like this:
TZIncomeExpenseEquipment = ProhibitIncomeExpenseEquipment.Execute().unload(); for each strInflowOutflowFrom TZInflowOutflow Cycle strT.income = strInflowExpenseInput.income; strTZ.expense = strIncomeExpenseEquipment.expense; strTZ.turnover = strIncomeExpense Turnover; EndCycle;

Answer:

finally got to the code. thanks, it helped. it turned out like this:
TK = RequestNomenclature(); for n=1 by number of Weeks cycle TK.columns.add("incoming" + n, typeNumber, "incoming" + n); TK.columns.add("flow" + n, typeNumber, "flow" + n); EndCycle; for n \u003d 1 for the number of Weeks cycle ColumnExpansion = TK.columns.find("expenditure" + n); EndPeriod = StartPeriod + week; TZPR \u003d Request Turns (Beginning of the Period, End of the Period); МPrih = TZPR.unloadColumn(0); Mrax \u003d TZPR. unload Column (1); TK.loadColumn(MPrix, ColumnPrix); TK.loadColumn(Mrax, ColumnRax); StartPeriod = EndPeriod; EndCycle;
did not optimize (for example, regarding a query in a cycle), since the final view of the table did not initially suit in terms of visibility, I wanted another solution. I remembered the layout and spreadsheet document, I try through it

Question: How to process it in a Query without knowing the structure of the table of values

Hello. There is a table of values. The exact names of the columns are not known. It is necessary to process the table of values and add one column to it and fill it. Filling is carried out on the basis of one column with a known name.
The easiest option is to add a column and process the table in a loop. But this is not entirely good.
Is it possible to do this in a query?
Thanks.

Answer:

As a first approximation:
Select * PUT VT_ExternalData FROM &Table AS ExternalData
Select ExternalData.*, Table.FIELD1 FROM VT_ExternalData AS ExternalData LEFT JOIN<Таблица>AS Table ON ExternalData.Field1 = Table.Field1

Question: How, when selecting the value of a cell of the table of values on the UV, do the selection by the owner?

Dear colleagues! Not so long ago, life forced me to face managed forms, so I still don’t understand much about them. Do not consider the question stupid and help whoever can:) The question is the following. I form my table of values and display it on the form. The first column is a link to the reference book of the nomenclature, the remaining columns are links to the reference book for which the Nomenclature is the Owner. How can I make it so that at the beginning of selecting a value in such a column, the list of values already had a selection by the owner. While I did it through the StartChoice event, I open the list with the necessary selection there. But I don’t understand the problem where to shove the resulting value in the selection processing? And there is a suspicion that the selection by owner can be set through the property of the ParametersChoice column. How to be? Tell!

Answer: and here I have

&AtClient
Procedure Choice Processing(SelectedValue, ChoiceSource) If ChoiceSource.FormName = "Directory.ObjectPropertyValues.Form.ListForm" Then And I don't know what to write
to place the SelectedValue in an editable table cell!!!

EndIf;
EndProcedure

Question: Grouping and Collapse() by value table

carefully, it's 7.7 :)
So a subject: I unload PM of the document in the table of values.
The PT of the document has the form
Expend. inc. | Client | delivery address.

I process the table of values:
selectRows();
while getString() = 1 Loop tbl.NewString(); tbl.Client = Client; tbl.DeliveryAddress =DeliveryAddress; tbl.DocNumber = Invoice.Docnumber; EndCycle ;
The point is the following. In the layout, when printing, the Client, the Delivery Address and the numbers of all invoices for the client should appear in one cell, and there may also be one client, but different delivery addresses. That is, if the Client and the Address match, display a list of invoices by these parameters.

Answer:() Use a black query about documents. Groupings: Client, Delivery address, Implementation (Ordering by number). You use cycles taking into account groupings. In the source table, display the lines in the second grouping (Delivery address), but after the cycle through the third grouping (Implementation), because in the second grouping you will form a line with invoice numbers.
Invoice Numbers = "";
While Query.Grouping(3 ) = 1 LoopInvoiceNumbers = InvoiceNumbers + ?(EmptyString (InvoiceNumbers) = 1 , "", ", ") + Query.OrderingValue((TableString.Data,StringLength)=WhatLooking For Then Array of Found Strings.Add(TableString ); EndIf ; EndCycle ;