Question

Query performance issues

1

I’m having performance issues with multiple UI queries taking more than 10 sec. to process. The logs provide me with the details of said queries. But what’s the best way to troubleshoot those queries? Specifically: How may I pinpoint the screen/microflow that originated a specific query? Is there an easy way to get an execution plan from a query? And provided there is, can I actually optimize it? I see redundancy in the queries whereby some inner joins are executed multiple times. I’m assuming that it comes from the application of both entity rules and xpath constaints that frequently overlap. Does it mean that I should try to get rid of one of those two layers, which I had understood as the former being meant for database isolation and the latter, for business logic? My database doesn’t hold that many records (no table has more than 50K rows) but I frequently need to gather data from multiple tables at the same time. I already have indexes in place. Thanks for your help

asked 2022-02-08

Patrick Prasquier

1 answers

Ronald Catersels · Answer 1 · 2022-02-08

It would help us developers so much if Mendix would indeed specify where those warnings are coming from. There are some hints though.

COUNTS
If you see those then you might want to look for a datagrid where you show the amount of records and wonder if you really need to have a total result of all your records. Most of the time we do not think at all about these fields but they can be a cause for those warnings. And is would have been so nice if Mendix did put the object of the count on the same line as the warning. Searching your logs might give you a lot of hits but the next line gives you the crucial piece of information about what entitity it is. Already filled a support ticket for that if that could be changed.
XPath contains _SEC
This means that security is added. Most of the time that will be forms unless you have a lot of microflow with entity access on. So try to pinpoint the forms where this entity is used.

Now optimizing is always tricky. You probably need to focus on your XPaths in the entity access. Try always to put the constrained that limits the most at the top.
XPath constrains that have the contains function in them are heavy on the database. You should try to avoid those if possible. Starts-with is much less heavy and might also do the trick.

Examine your XPaths and make sure that the stuff you use also has an index on it. For instance when checking a date greater then etc. make sure that date field has the right index (ascending or descending).

Try to measure your different XPath to check if they are indeed faster. You can use the timer functions from the community commons for that. It is not ideal but better then nothing. I create test microflow to measure this. And use this before and after you set an index on an attribute to see what works best.

And you could try to set your loglevel DataStorage_QueryPlan to trace. That gives you a lot of info.

But It would be so nice if I could right click a retrieve action in a microflow and measure that retrieve on my current local database. That would helps us developers so much.

Regards,

Ronald