GridSearch: performance issues with multi select dynamic drop down

0
This one has puzzled me already for a good part of the afternoon.  So have a listview and I use the dynamic dropdown filters from https://marketplace.mendix.com/link/component/49364. This works all fine but recently I found out I have serious performance problems when users start multi-selecting more than 5 items in the dropdown.  I analyzed the queries that are send to the database and was quite surprised to see that the more items you select, the longer the query becomes. Mendix keeps on adding clauses to the query. The query gets longer and longer the more you items you select, and at the same time slower and slower as well until a point that it becomes totally unworkable. Now maybe this has nothing to do with the widget itself or course. Did anyone experience something like this before? Any direction you could give as so where to start digging some more.   
asked
3 answers
1

Hi Bart,

I don’t know about your exact use case but I once wrote a Java action that creates a dynamic XPath for a simple search engine. For my specific use case, it generated an XPath based on some parameters then returned that list. Perhaps it can help you with your problem by including it in a datasource microflow then using your input fields to generate an XPath for you.

XPath < Item > xpath = XPath.create(getContext(), Item.class)
    .eq(Item.MemberNames.Name, this.Search.getQuery());

//Gt function
if (this.Search.getPriceMin() != null) xpath.and().gt(Item.MemberNames.Price, this.Search.getPriceMin());

//Custom compare
if (this.Search.getPriceMax() != null) xpath.and().compare(Item.MemberNames.Price, "<=", this.Search.getPriceMax());

//Add location boundaries
if (this.Location != null) {
    xpath
        .and().compare(Item.MemberNames.Lat, ">=", this.Location.getLatSW())
        .and().compare(Item.MemberNames.Lat, "<=", this.Location.getLatNE())
        .and().compare(Item.MemberNames.Lng, ">=", this.Location.getLonSW())
        .and().compare(Item.MemberNames.Lng, "<=", this.Location.getLonNE());
}

//Retrieve by generated xpath
List < Item > items = xpath.all();

//Create IMendixObject list from xpath
List < IMendixObject > ObjectList = new ArrayList < IMendixObject > ();
for (Item item: items) {
    ObjectList.add(item.getMendixObject());
}

return ObjectList;

 

answered
0

I figured out what is causing this but I do not know how to influence the dynamic drop down widget to change this. Looking at the query it generates it does something like this:

[Main.Company_Address_Statutory/Main.Address/Main.Address_Country/RequestConfig.Country/CountryName = 'Belgium' or
Main.Company_Address_Statutory/Main.Address/Main.Address_Country/RequestConfig.Country/CountryName = 'Italy' or
Main.Company_Address_Statutory/Main.Address/Main.Address_Country/RequestConfig.Country/CountryName = 'Spain' or
Main.Company_Address_Statutory/Main.Address/Main.Address_Country/RequestConfig.Country/CountryName = 'Portugal' or
Main.Company_Address_Statutory/Main.Address/Main.Address_Country/RequestConfig.Country/CountryName = 'Germany' or
Main.Company_Address_Statutory/Main.Address/Main.Address_Country/RequestConfig.Country/CountryName = 'China' or
]

which generated a crazy long SQL query.

When you do it like below, it is a lot faster.

[Main.Company_Address_Statutory/Main.Address/Main.Address_Country/RequestConfig.Country
[CountryName = 'Belgium' or CountryName = 'Italy' or CountryName = 'Spain' or CountryName = 'Portugal' or CountryName = 'Germany' or CountryName = 'China']]

So now I need to figure out how I can influence the  dynamic dropdown widget to be smarter about this.

answered
0

Widget author here. The dynamic dropdown is a wrapped standard Mendix widget (the dropdown filter box for data grids), so I can’t control how it creates XPath.

I see 2 possible solutions:

  1. Update the “Dropdown – Static” filter to support all of the features of the dynamic one. This would be great but a ton of work. I won’t be able to build it myself but I welcome pull requests on GitHub.
  2. Model your own multi-select dropdown (I see 2 widgets in the marketplace) and build the XPath yourself. Then use AnySearch to apply it.
answered