Bad query performance due to IN query

3
Hi,   I know Mendix queries get complicated super fast due to domain model structure (security roles). However, we have cases with very simple access rules (just 1 rule that is being applied) generating a relatively simple query that is super slow (like 20 seconds +). Here's an example of a query generated by a datagrid that shows products associate to a company. This company has about 400.000 products linked to their company. We only load 40 items per page. So the subset from the IN query is 400k.    The query within the IN() operator is fast. However the combination of the main query + the IN() query is super slow. Eliminating the IN() part and manually adding the constraint to company/user in the main query is also fast.    After doing an explain/analyze on the query I found out that postgres was doing an index scan for the subquery. This is terrible slow. If I rewrite the IN() subquery to this:  ... productmanagement$product"."id" = ANY((SELECT ARRAY( SELECT ...... subquery ... WHERE ...)::bigint[])) Then it's like 80% faster again. This was a fix I found on the internet however I cannot adjust the queries in Mendix to do this…    Also, doing this:  set enable_nestloop to off; Right before executing the query also increases performance with 80%. But not sure what the implications of doing this would be if I for example would turn this off globally.    Full query:    SELECT "productmanagement$product"."id", "productmanagement$product"."name" FROM "productmanagement$product" WHERE ("productmanagement$product"."id" IN ( SELECT "a4productmanagement$product_company"."productmanagement$productid" FROM "productmanagement$product_company" "a4productmanagement$product_company" INNER JOIN "usermanagement$account_company_active" "c3usermanagement$account_company_active" ON "c3usermanagement$account_company_active"."usermanagement$administratieid" = "a4productmanagement$product_company"."usermanagement$administratieid" WHERE "c3usermanagement$account_company_active"."usermanagement$accountid" = 123123123123 )) ORDER BY "productmanagement$product"."name" DESC, "productmanagement$product"."id" DESC LIMIT 40  
asked
8 answers
2

Hi Marcel,

 

Unfortunately, the way you want it is not an option in Mendix 😥

I did do a reconstruction in Mx 7, 8 and 9 and this is indeed behavior you cannot control in any way.

So, as far as I can see, you have a few options left:

  • Keep retrieving persistent entities using Database/XPath, but don't apply row-level-security (seems a no-go for me)
  • Use another source, like microflow and use a helper filter entity to mimic the same filter functionality as the datagrid
  • Use non-persistent entities filled by OQL and use a helper filter entity to mimic the same filter functionality as the datagrid. Don't make mistakes in your OQL, as you bypass the security layer of Mendix this way.

Good luck!

Johan

answered
1

I think there is a access rule for the user active on the entity you tried to retrieve. 
the in statement is more/less the restriction which is applied to the productlist (you are only allowed to see the products which are associated with this company)

and because this company might have many products, a lot of id's will be applied to IN part of the main query.
I don't see your historic xpath part within the sql statement.

Try to copy/paste the sql into pgadmin sql tab and click the explain button. there you can see which part of the query is the heaviest (highest cost).

The result shown on screen might also be validated with the access rules. this also might be a bottleneck.


Maybe you can make use of OQL, which bypasses security, and put the result into a helper entity on which you can apply sorting etc. 
be sure that you add the company constraint within the OQL.
 
to get the actual product by ID, you can make use of RetrieveById from the saferOQL module.

 

answered
1

Another approach would be to have a helper object on the page as a context object. Then manually create your search fields as input / dropdowns. Then on change you can trigger a microflow that retrieves that correct records and applies the correct sorting etc. Then you can associate the resulting list to you helper object and show only the associated objects in the list. A bit of a workaround tho, but if Mendix doesnt work out of the box you have to get creative.

answered
1

Maybe it's also a good idea to share your example with the Mendix devs via a support ticket. Maybe they can suggest other solutions, or take this example to improve the platform. 

answered
0

Hi Marcel,

What are the settings you use for the datagrid? Retrieve by association or XPath?

Does Mendix generate the same SQL when you do a similar retrieve but in a retrieve action in a microflow, or is that performance perhaps better? 

answered
0

Hi Marcel,

 

Could you share your XPath? Did you already try changing the order of the XPath, with the most restrictive line on top? 

answered
0

Hi Marcel,

 

I haven't setup a test project (yet), but this seems quite the challenge!

 

You mention that the IN operator originates via the security, but I see a company id in the query. Is that because a UserAccount is associated to a single company?

 

In that case, you could try an enveloping Company dataview (loaded by MF/NF via your UserAccount) and retrieve using Database or XPath the associated Products? Maybe then the IN is replaced.

answered
0

Upvote this Idea :) 

answered