How to configure high volume windows

Contents 1 Objective 2 Improve sorting 3 Improve filtering 3.1 Equals 3.2 iEquals 3.3 StartsWith 3.4 iStartsWith 3.5 Contains 3.6 iContains 3.7 Unfiltered Foreign Key Combo 3.8 Disable Foreign Key Combo 3.9 Allow Filtering Foreign Keys by its Identifier 3.10 Filter on change 3.11 Threshold to Trigger Filter on Text Field 4 Lazy filtering 5 Allow Summary Functions 6 Disable linked items 7 Implicit filter

Objective

Grid configuration provides a series of optional settings to be applied in a grid in order to improve its performance when loading, filtering and sorting.

Working with high volume of data we recommend to deactivate sorting and filtering in some/most columns in backend grids.

For the column left which are not deactivated and which are not fast enough, it is necessary to configure the most appropriate option and create an index if the option requires it.

Improve sorting

• Create a multi-column index
• The columns must be:
  • identical to the order by definition of the table.
  • additional primary key because backend grids are always ordered by the identifier of the table.

 CREATE INDEX M_PRODUCT_NAME_ID ON M_PRODUCT (NAME, M_PRODUCT_ID);

Improve filtering

Equals

• Configure equals at text filter behaviour in grid configuration

• Create index

 CREATE INDEX M_PRODUCT_NAME ON M_PRODUCT (NAME);

iEquals

• Configure iEquals at text filter behaviour in grid configuration

• Create index

 CREATE INDEX M_PRODUCT_NAME ON M_PRODUCT (UPPER(NAME));

StartsWith

• Configure startsWith at text filter behaviour in grid configuration

• Create index

PostgreSQL

 CREATE INDEX M_PRODUCT_NAME ON M_PRODUCT USING BTREE (NAME varchar_pattern_ops);

Oracle

 CREATE INDEX M_PRODUCT_NAME ON M_PRODUCT (NAME);

iStartsWith

• Configure iStartsWith at text filter behaviour in grid configuration

• Create index

PostgreSQL

 CREATE INDEX M_PRODUCT_NAME ON M_PRODUCT USING BTREE (UPPER(NAME) varchar_pattern_ops);

Oracle

 CREATE INDEX M_PRODUCT_NAME ON M_PRODUCT (UPPER(NAME));

Contains

• Configure contains at text filter behaviour in grid configuration

• Create index

 CREATE INDEX M_PRODUCT_NAME_TRGM ON M_PRODUCT USING GIN (NAME gin_trgm_ops);

iContains

• Configure iContains at text filter behaviour in grid configuration

• Create index

 CREATE INDEX M_PRODUCT_NAME_TRGM ON M_PRODUCT USING GIN (UPPER(NAME) gin_trgm_ops);

Unfiltered Foreign Key Combo

• The filter drop down will show the rows from the referenced table unfiltered by specific rows being referenced in the referencing table. The filters entered by the user in the grid filter editor will be applied.
• This configuration makes sense if the referenced table does not have that much volume, but loading the foreign key filter drop down takes a lot of time anyway.

Disable Foreign Key Combo

• In some cases the population of the drop down is very slow. In that case, it could be useful to use this configuration, that will get rid of the foreign key filter combo, so that the column will be filtered like a standard text column.
• Use this only if filtering this way outperforms the population of the combo

Allow Filtering Foreign Keys by its Identifier

• If this flag is unchecked, then the only way to filter the foreign key columns will be by picking some value from the filter drop down.
• The query done to fetch data when the filter is set this way is much better than the query done when the column is filtered without using the filter drop down.
• Even if this flag is unchecked the user will be able to enter text to filter the data shown in the drop down list.

Filter on change

• If this flag is checked, text columns will trigger a filter each time its content changes.

Threshold to Trigger Filter on Text Field

• Threshold in ms to trigger the filter on text fields. The default value is 200ms, therefore the value entered in this field should be greater.

Lazy filtering

It is interesting to configure it when we have high volumes of data to display in a grid.

If it is set in grid configuration, the grid won't perform a filter and/or a sorting action until the 'Apply Filters' button be pressed. In the same way, the summary functions present in the grid won't be recalculated until this button be pressed.

Allow Summary Functions

If this flag is enabled then the user can add summary functions into the grid, by using the column header context menu. If not enabled, then the summary functions menu will not be available. It is interesting to disable it when we have high volumes of data.

Disable linked items

Linked items functionality tries to find any record with a FK linked to current one. This, in some cases, requires many DB queries which, specially with big volumes of data, can be very heavy.

To disable it we have the preference Disable Linked Items Section, when this preference is defined for a window with its value set to Y, the Linked Items section will be disabled for that window. If there is not any window defined in the visibility settings, this section will be disabled for every window in the application.

Implicit filter

Some grids, especially those with large data volumes, have preset hidden filters, the so called implicit filters. Typical implicit filters are set to filter out already process transactional documents or documents before a certain date. Implicit filters can be cleared by clicking the funnel icon in the top right of the grid. When a window contains an implicit filter, it is necessary to do a performance testing, to be sure that is not a problem load the window with it. When this implicit filter is a problem is possible to disable it setting to false Allow Transactional Filters.

Category: Performance