Projects:Foreign Key Filtering Performance Improvement/Technical Specification

Introduction

There are two possible ways to filter a foreign key column:

• a) by selecting an option from the drop down list
• b) by entering text in the filter textbox

By default a) would result in a criteria like this {fieldName: columnName$_identifier, operation: 'equals', value:...}, and b would result in something like: {fieldName: columnName$_identifier, operation: 'iContains', value:...}.

a) should use a more efficient efficient criteria. Instead of filtering using the column identifier, the column id should be used. That way the indexes defined for that column could be used, and the query would have better performance.

This project affects only to option a).

This project will also address the change of the join done to create the query resulting from filtering the grid. Currently 'left join' is always used, but if certain conditions are met an 'inner join', which has better performance, can be used.

Filtering Using the ID - Technical Considerations

• Identifier(s) should be shown in the filter text area. After refresh they should be kept.
• If view is saved with this filter, identifier should be shown and filter by id should be performed
• If after selecting a single record, identifier is shown and some text is appended to it, we’re in case b)
• Using contextual menu “User as Filter” is case a)
• UX design to visually differentiate case a) and b)
• The Grid Configuration project should support to restrict the foreign key filters so that only a) works.
• Differentiate case 1 and 2 to generate correct criteria

Left Join Vs Inner Join - Technical Considerations

An inner join performs better than a left join, but they are not always interchangeable. These two conditions must be met in order to be able to use an inner join when creating a query due to some foreign key columns of the grid being filtered:

• All the columns that are part of the identifier of the target entity must be not nullable (mandatories)
• The joined entity can only be used in the where clause, due to filtering the grid. For instance, if the Sales Order grid is filtered by the Business Partner column, and also being sorted by it, a 'left join' will be used. The gridFilterExclusiveJoinMap attribute is used in the AdvancedQueryBuilder class to know if the joined entity is being used only in the where clause or not.