we have to manage millions db rows and we have created a couple of screens with some filter criteria already definited for the users.
In case of criteria related to an entity, the default components used by the filter are lookup fields that load all data making the loading of the screen very slow.
First issue: How can we change the type of the component in the filter? For example: choose a lookup field in case of a criterion with a number of related entities less than 50 and a pickerfield for a criterion with a number of related entities more than 50.
Moreover, in case of a filter with 15 entity criteria, if the user doesn’t set any filter and presses the search button, the application is really slow. That’s because the filter performs the query with 137 table joins anyway (we logged the sql that the filter component generates when it is loaded).
Second issue: Is it possible to create the query with the only setted criteria reducing the number of table joins? This could really improve the performance of the application.
Is there any way to retrieve the jpql query generate by the filter to manipulate it and its filter criteria before to be applied?
Last question: We noticed that the constructor of the EntityManagerImpl class doesn’t have an access modifier therefore it is not possible to extends it eventually. Is it intentional?
First about the filter components. By default, related entities are selected using PickerField for exactly this reason: to not slow down the system if there are too many rows in the database. The default behavior can be changed if you use @Lookup(type = LookupType.DROPDOWN) annotation on the reference field - in this case, LookupField is used. This is a “static” way of controlling the filter behavior. There is also a “dynamic” way using the Entity Statistics feature - see the lookupScreenThreshold parameter. Let us know if you have any trouble using this mechanism.
About the table joins. If the user doesn’t set a condition, no additional join is required. I.e. an empty filter does not generate any joins. All joins in this case are defined by the view which is set for the datasource. Check the view and remove all unneeded branches from it, keeping only references and attributes shown in your table.
Regarding EntityManagerImpl. It’s really not very suitable for extending now, but we can fix it in a future platform version - see the attached ticket.
Hi Konstantin,
regarding the filter component, it is strange but it seems that the default behaviour for related entities is a Lookup component, not a PickerField component (we didn’t set any annotation like @Lookup(type = LookupType.DROPDOWN)). Anyway, creating an EntityStatistics for a related entity with the parameter lookupScreenThreshold set to 20 for example, the PickerField component is dinamically choosen if the number of instances exceeds 20. It’s fine.
For the table joins, thanks for the explanation. We are checking all the views.
Finally, we resolved the problem in another way without using the EntityManagerImpl class.
Thank you very much.
Regards
Hi Nicole,
You are right, the default component for a reference parameter is LookupField (with drop-down). It was my mistake, sorry. Glad to hear that you have solved the issues.
See the following issue in our bug tracker: