Performance Issues with DataGrid2 Using a Complex SQL Query with calculations loading huge data

0
Dear Mendix-Community,   I created a microflow that loads data from an SQL database using a query. The loaded data is displayed in a DataGrid2. The DataGrid2's data source is the microflow. In this process, the query includes calculations, and additional attributes are used through associations with other entities to perform further calculations. Altogether, this results in very slow performance on the page where the DataGrid2 is displayed—it lags and responds very slowly. I am quite certain that the issue lies with the complex query, as well as the large number of rows and calculations involved. Therefore, my question to the community is: Does anyone have tips on how to improve the performance?   Thank u.
asked
4 answers
2

Hi Hamid Rezaie,

                       What SQL query did you use for the database retrieval? 

for optizimation go through this documentation

https://docs.mendix.com/refguide/community-best-practices-for-app-performance/

answered
2

Hi Hamid

          Database Views 

                               For very complex calculations, consider using database views or stored procedures that can be pre-computed on the database side. This reduces the amount of logic that needs to be executed by the application.

           Set a Row Limit:

                                      For large datasets, limit the number of rows displayed at once. Displaying hundreds or thousands of rows at once can be very taxing on the browser. Instead, consider displaying a smaller, manageable set of rows with pagination or filtering.

 

answered
1

Hello Hamid,

 

The best practices for mendix can be found here :

 

https://docs.mendix.com/refguide/community-best-practices-for-app-performance/

 

However, in your case, if i am understanding it correctly, you get data from outside the mendix database with an sql query and within the query you do calculations and after you do more calculations, so probably the problems is lying in there question you can ask that would maybe help you are the following:

 

- can i reduce the data collected

- do i really need to calculate the fields at the moment of retrievel can it be before or can it be later, can it be done in the background.

- can i optimize the query there is a lot of information around sql queries on the internet 

 

https://www.geeksforgeeks.org/best-practices-for-sql-query-optimizations/

 

Hope this gives you some direction,

 

Good luck!

answered
0

When retrieving data from an external database and showing it in a data grid, there are 2 main causes of bad performance:

  • slow query - your query is inefficiënt or just trying to do too much. You should look into improving your query, your table structure, your indexes, or database statistics. Best to work with a DBA here.
  • retrieving too much data into the Mendix app. All data is loaded into memory and transported to the browser. This only works for a limited set of records. Try to reduce the number of records you retrieve, e.g.  by adding support for paging, or store/cache the data first in the Mendix app in an entity, and then show the entity in your data grid.

 

in your case it looks like you are running into both problems, and you may need to address both.

answered