Indexed choice columns

Applies to version: 2023 R1 and above; author: Łukasz Maciaszkiewicz

Introduction

Efficient database searching often plays a crucial role in determining the effective operation of the system, as well as the applications created within it. The problem becomes especially evident over time. Growing databases, when managed incorrectly, can considerably slow down system operation and negatively affect the overall user experience. In order to streamline data searching and make queries more efficient, the WEBCON BPS platform introduced indexed choice columns.

Databases in WEBCON BPS

In the WEBCON BPS platform the picker field values entered in the form are saved in the database table WFElements in columns whose names begin with “WFD_AttChoose(…)”. For example, in the screenshot below, you can see a Choice field form field with the column name “WFD_AttChoose1”.

The information about the database column that stores values for a given form field is available in the form field configuration window.

The aforementioned columns store data in a compound format of “ID#Name”, where “ID” serves as an identifier and “Name” represents the displayed name. The first part, “ID”, represents a unique value. It is the element most frequently used in SQL queries designed to filter results displayed in picker fields populated with values from a specific data source. Such filtering is often employed to exclude irrelevant information, presenting only what is of interest to the user in forms, reports, picker fields, and so forth.

• The previous approach – the dbo.ClearWFElemID function.

Since the database columns linked with picker fields store data in the format “ID#Name”, users have been required to use the dbo.ClearWFElemID function until now to restrict the amount of data displayed in these form fields. The function allowed for the extraction of the ID component which could then be utilized for filtering or joining different database records using the JOIN clause in the query.

An example of a query that incorporates the dbo.ClearWFElemID function to extract the ID value from records stored in the specified column

While useful, the function has a negative impact on system efficiency due to the processing of each database table row. This process is particularly time-consuming, especially in the case of large data collections. Additionally, the syntactical complexity of the SQL query containing the aforementioned function can make it difficult to create a correct and effecive query.

To counter those problems we have employed indexed choice columns that offer an alternative to the approach based on the above-mentioned function.

Indexed choice columns

The data indexation mechanism is a process of organizing and structuring information in a way that enables fast and effective searching. It consists of creating indexes that direct to specific data and its location in the collection. Thanks to the indexes configured for particular database columns it is possible to search data included in the table and retrieve it much faster.

NOTE: the below-described solution that involves employing indexed choice column in the WEBCON BPS platform applies only to the picker fields that allow for selecting only one value. For the picker fields operating in the multiple values selection mode the solution described herein does not apply.

At the database level in WEBCON BPS, the aforementioned mechanism has been implemented for picker fields by creating indexed choice columns for each picker field type. Those are indexed, calculated columns for which an identifier (ID) is calculated. Please note here the word “calculated”, as the identifier (ID) is not physically saved in those columns, but rather calculated immediately in the transition between database operations. On the other hand, the choice column itself is in a sense a duplicated version of the picker field column, but unlike the latter one, it includes only information about identifier.

The calculated columns names differ from the picker field columns only by the added “_ID” suffix, i.e. “WFD_AttChoose1_ID”. By using its name (or a corresponding variable in the variables editor in Designer Studio), the column can then be used in an SQL query to retrieve the data specified in it.

The SQL query referring to the indexed choice column and used for filtering results presented on the report

What is important, a query referring to such an indexed choice column is much more efficient and fast – unlike the SQL function described above, it scans only the indexed column, and not the whole database table. Once the data specified in the query is found, the SQL engine loads the remaining data associated with it. Both operations (finding data in the indexed column and loading additional information) are executed very fast, which positively affects the general system efficiency and user experience.

It is worth mentioning here that, thanks to the implementation of indexed choice columns, updating the system to the 2023 R1 version automatically leads to faster operation of some of its elements, and this does not even require making changes in configuration. This applies to, among others, picker field columns on reports and in the BPS View sources, as well as to queries employing the "(form field name) Database field name – ID" (see the point below).

• References to the indexed choice column in Designer Studio

The ability to use the indexed choice column is currently implemented in the Designer Studio tool in all the places that include references to the database columns, e.g. on reports, in BPS View, or variables editor. When creating an SQL query intended for filtering out irrelevant data, its author can use a variable that directly refers to the indexed choice column. Just as before, such a variable is available in the variables editor in the Objects tab. It takes the form of “(form field name) Database field name – ID” and directly refers to the aforementioned column, which makes it easy to insert into a query. The variable itself is not a new feature in Designer Studio, but so far it included the dbo.ClearWFElemID SQL function, which meant that a query containing it would be much less efficient.

It is worth noting that among the variables available in the Objects tab there is also a variable referring to the name (displayed name) in the database column. Nevertheless, in this case it does refer to the indexed column, but continues to rely on a respective SQL function. This is because names themselves are not always unique, and they can be subject to changes. Therefore, they are rarely used for tasks such as joining records, sorting, or grouping.

The variable referring to the indexed choice column can be found in the Objects tab of the variables editor

Summary

The introduction of indexed choice columns to the WEBCON BPS platform noticeably affects the efficiency of both SQL queries and the system as a whole. This solution allows you to create accurate queries in an optimal manner and retrieve required data in a very short time. On the other hand, implementing references to the indexed choice column in the Designer Studio tool greatly simplifies the creation of the aforementioned SQL queries and enables efficient searching even in very large databases.