Performance issue - opening form - how to debug and resolve?
Related to my other post with a question how to prevent retrieving all data in a form on open: https://forum.mendix.com/questions/7003/Form-with-tabs--performance-issue I have an additional question. When opening a specific form (containing a specific record) the first time on a day it can take up to 3.5 minutes to open the form for the specific record. Most probably caused by the fact that a specific piece of related data required somewhere in the form is taking a long time to be retrieved. Once we opened the form the first time, the second time the form opens in seconds (< 5 seconds). On our production environment after approx 2 to 3 hours the form requires 3.5 minutes again. This sounds like database query caching or something... However in the SQL Server Activity Monitor we do not see 'Expensive queries' that are related to the queried object. Using the F12 network analysis in Chrome we did find that the step that causes the delay is in fact the step retrieving (all) the data related to the object of the main data view in the opened form. How to progress debugging and resolving issues like this?
Have you set indexes on relevant attributes in the entity? This can speed up your query A LOT.
When doing so, make note that your server needs enough memory to keep all the indexes in memory, this way, when executing a query, it remembers it, causing it to return your data much faster.
Disclaimer: My technical (database) experience isn't that high, so I might be wrong on some points. However, this solved a lot of our performance issues (eg, pages taking 5 seconds to load)