Question

Poor Performance : Mendix Generates a lot of SQL queries

0

We have an application built with Mendix that is currently deployed in an on-premise datacenter. The database it connects to is located in the same datacenter. For organizational reasons/constraints, we are in the process of migrating this application into another datacenter where the database it connects to won't be physically located very next to the application (not in the same datacenter). So far our tests showed that the application can be up to 6 times slower when it connects to a database where there is even very little latency (we are talking about ~5ms latency, so totally acceptable on a network perspective). A business transaction that used to take 5 seconds on premise takes now 30 seconds. (This is unacceptable for our business) After analyzing reports of the SQL profiler, we came to the conclusion that poor performance of the Mendix application was due to the amount of SQL queries it generates when the model of the application gets quite big. The number of SQL queries seems to be especially driven by the number of associations the entity model contains. Why? The report shows that almost all of SQL queries generated by Mendix are simple SELECT statement to fetch an entity linked with another one. Those SQL queries are executed (almost) instantaneously by the SGBD (we tried with PostgreSQL and MSSQL so far): 0ms for every single SQL statement. What we noticed however is that there is a small gap of ~5ms between those SQL queries when the database is deployed in another datacenter. When the database is deployed in the same datacenter, there is no gap between all the queries and they roughly get executed in the same millisecond. The screenshot below shows the duration and the gap in millisecond between each SQL queries when the Mendix application is connected to a database in another datacenter. The problem is that, in our case, mendix is generating a lot (if not a HUGE) number of those SQL queries. In the specific business process we tested (and it was not a complicated one), it generated more than a thousand SQL queries (!!). You'll find a small extract of the trace below. For obvious security reasons, I anonymized data in this trace. This trace shows every single SQL queries executed by MSSQL SGBD in chronological order (top to bottom). If you pay attention to each of them, you'll notice some patterns: some ID's are used over and over again in order to fetch another entity. For example, the ID "68398419657003969" is used 57 times in order to fetch other entities. exec sp_executesql N'UPDATE [entityA] SET [somattribute] = ? WHERE [id] = @P10','72620544144117444 go declare @p1 int set @p1=7 exec sp_prepexec 'UPDATE [entityA] SET (...) someAttribute=64176294690059780 WHERE [id] = 72620544144117622 select @p1 go exec sp_executesql N'SELECT * FROM [entityB] WHERE [entityB].[id] = @P0',N'@P0 bigint',43910096366862361 go exec sp_executesql N'SELECT * WHERE [entityC].[entityBid] = @P0',N'@P0 bigint',43910096366862361 go exec sp_executesql N'SELECT * WHERE [entityD].[entityBid] = @P0',N'@P0 bigint',43910096366862361 go exec sp_executesql N'SELECT * WHERE [entityE].[entityBid] = @P0',N'@P0 bigint',43910096366862361 go exec sp_executesql N'SELECT * WHERE [entityF].[entityBid] = @P0',N'@P0 bigint',43910096366862361 go exec sp_executesql N'SELECT * FROM [entityB] WHERE [entityB].[id] = @P0',N'@P0 bigint',88383142735171856 go exec sp_executesql N'SELECT * FROM [entityG] [entityC] WHERE [entityC].[entityBid] = @P0',N'@P0 bigint',88383142735171856 go exec sp_executesql N'SELECT * FROM [entityH] [entityD] WHERE [entityD].[entityBid] = @P0',N'@P0 bigint',88383142735171856 go exec sp_executesql N'SELECT * FROM [entityI] [entityE] WHERE [entityE].[entityBid] = @P0',N'@P0 bigint',88383142735171856 go exec sp_executesql N'SELECT * FROM [entityJ] [entityF] WHERE [entityF].[entityBid] = @P0',N'@P0 bigint',88383142735171856 go exec sp_executesql N'SELECT * FROM [entityH] WHERE [entityH].[id] = @P0',N'@P0 bigint',88383142735171834 go exec sp_executesql N'SELECT * FROM [entityK] [atj1entityK] WHERE [atj1entityK].[entityHid] = @P0',N'@P0 bigint',88383142735171834 go exec sp_executesql N'SELECT * FROM [entityL] [atj6entityL] WHERE [atj6entityL].[entityHid] = @P0',N'@P0 bigint',88383142735171834 go exec sp_executesql N'SELECT * FROM [entityM] [atj5entityM] WHERE [atj5entityM].[entityHid] = @P0',N'@P0 bigint',88383142735171834 go exec sp_executesql N'SELECT * FROM [entityN] [atj7entityN] WHERE [atj7entityN].[entityHid] = @P0',N'@P0 bigint',88383142735171834 go exec sp_executesql N'SELECT * FROM [entityO] [atj4entityO] WHERE [atj4entityO].[entityHid] = @P0',N'@P0 bigint',88383142735171834 go exec sp_executesql N'SELECT * FROM [entityP] [atj3entityP] WHERE [atj3entityP].[entityHid] = @P0',N'@P0 bigint',88383142735171834 go exec sp_executesql N'SELECT * FROM [entityQ] [atj2entityQ] WHERE [atj2entityQ].[entityHid] = @P0',N'@P0 bigint',88383142735171834 go exec sp_executesql N'SELECT * FROM [entityR] WHERE [entityR].[id] IN ( ...) go exec sp_executesql N'SELECT * FROM [entityS] WHERE [entityS].[id] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityT] [atj1entityT] WHERE [atj1entityT].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityU] [atj20entityU] WHERE [atj20entityU].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityV] [atj19entityV] WHERE [atj19entityV].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM[entityW] [atj17entityW] WHERE [atj17entityW].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityX] [atj18entityX] WHERE [atj18entityX].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityY] [atj16entityY] WHERE [atj16entityY].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityZ] [atj15entityZ] WHERE [atj15entityZ].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_A] [atj14entityS_A] WHERE [atj14entityS_A].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_B] [atj13entityS_B] WHERE [atj13entityS_B].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_C] [atj12entityS_C] WHERE [atj12entityS_C].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_D] [atj11entityS_D] WHERE [atj11entityS_D].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_E] [atj10entityS_E] WHERE [atj10entityS_E].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_F] [atj9entityS_F] WHERE [atj9entityS_F].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_G] [atj7entityS_G] WHERE [atj7entityS_G].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_H] [atj8entityS_H] WHERE [atj8entityS_H].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_I] [atj6entityS_I] WHERE [atj6entityS_I].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_J] [atj5entityS_J] WHERE [atj5entityS_J].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go ... go exec sp_executesql N'SELECT * FROM [entityS_K] [atj4entityS_K] WHERE [atj4entityS_K].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go IF @@TRANCOUNT > 0 COMMIT TRAN go exec sp_executesql N'SELECT * FROM [entityS_guarantee] [entityS_L] WHERE [entityS_L].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go set implicit_transactions off IF @@TRANCOUNT > 0 COMMIT TRAN go exec sp_executesql N'SELECT * FROM [entityS_B_A] [entityS_M] WHERE [entityS_M].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_B_B] [entityS_N] WHERE [entityS_N].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_B_C] [entityS_O] WHERE [entityS_O].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_B_D] [entityS_P] WHERE [entityS_P].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_B_E] [entityS_Q] WHERE [entityS_Q].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_B_F] [entityS_R] WHERE [entityS_R].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_B_G] [entityS_S] WHERE [entityS_S].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_B_H] [entityS_T] WHERE [entityS_T].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_B_I] [entityS_U] WHERE [entityS_U].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_B_J] [entityS_V] WHERE [entityS_V].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_B_K] [entityS_W] WHERE [entityS_W].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_B_L] [entityS_X] WHERE [entityS_X].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_B_M] [entityS_Y] WHERE [entityS_Y].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_B_N] [entityS_Z] WHERE [entityS_Z].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_B_O] [entityS_A_A] WHERE [entityS_A_A].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_B_Q] [entityS_A_B] WHERE [entityS_A_B].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_B_P] [entityS_A_C] WHERE [entityS_A_C].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_B_R] [entityS_A_D] WHERE [entityS_A_D].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_B_S] [entityS_A_E] WHERE [entityS_A_E].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_B_T] [entityS_A_F] WHERE [entityS_A_F].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_B_U] [entityB_A] WHERE [entityB_A].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_B_V] [entityB_B] WHERE [entityB_B].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_B_W] [entityB_C] WHERE [entityB_C].[entitySid1] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_B_X] [entityB_D] WHERE [entityB_D].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_B_Y] [entityB_E] WHERE [entityB_E].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_B_Z] [entityB_F] WHERE [entityB_F].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_C_A] [entityB_G] WHERE [entityB_G].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_C_B] [entityB_H] WHERE [entityB_H].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_C_C] [entityB_I] WHERE [entityB_I].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_C_D] [entityB_J] WHERE [entityB_J].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_C_E] [entityB_K] WHERE [entityB_K].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_C_F] [entityB_L] WHERE [entityB_L].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_C_G] [entityB_M] WHERE [entityB_M].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM[entityS_C_H] [entityB_N] WHERE [entityB_N].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_C_I] [entityB_O] WHERE [entityB_O].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_C_J] [entityB_P] WHERE [entityB_P].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go exec sp_executesql N'SELECT * FROM [entityS_C_K] [entityB_Q] WHERE [entityB_Q].[entitySid] = @P0',N'@P0 bigint',68398419657003969 go Possible solutions 1. Deploying the database inside the same datacenter We actually already did it and it indeed removes the small latency, hence solves the performance issue. But it is unfortunately forbidden by our organisation. 2. Refactoring the application code to avoid generating those SQL Queries This is theoretically feasible but would require 1 to 2 years of refactoring for us. Unacceptable... 3. Improve Mendix entity relationship handling. There seems to be room for improvement regarding how Mendix handle entity relationship loading. I understand that changing the behavior of such central piece of code is difficult but I still would like to share an idea in this matter. The Idea: Parallelism It seems that Mendix is executing each SQL queries sequentially. That is, it waits for the completion of an SQL query before executing the next one. This is sometimes necessary when the result of the first query is needed in order to fetch results of the second one. But sometimes it is not necessary. In the trace I shared above, you can see that the ID "68398419657003969" is used over and over again to fetch other entities. If all those queries could be executed in a parallel mode, we could save a lot of time and mitigate the issue of executing all those SQL queries. What are you thoughts on this? Are we the only user in this case?

asked 2024-12-01

Arnaud De Backer

5 answers

Joost Stapersma · Answer 1 · 2024-12-06

Hi Arnaud,

First of all, you are definitely not the only user facing these kinds of performance issues! As user of Mendix we can't really influence the internal workings of the platform as mentioned in solution 3 - other than providing feedback using the (Idea) forum and/or reaching out to Mendix support.

Another options you do have (other than solution 1 and 2) is tuning the runtime settings regarding the database configuration (see https://docs.mendix.com/refguide/tricky-custom-runtime-settings/#connection-pooling for more information). However, these settings usually only improve performance issues based on the amount of concurrent users, it will not decrease the network latency.

This can be seen as a disadvantage, but on the other side the developer does not have to worry about how the application logic is changed into SQL commands, the platform handles it for you in the 'average best way' - Mendix needs to support multiple infrastructure types and database engines to provide flexibility and similar performance regardless of your setup.

Refactoring is indeed costly, but there might be ways to refactor the bottlenecks in a relatively short timeframe, without having to spend a year to refactor everything. e.g. for a read-heavy entity with too many associations, you could create a separate entity with the primary key and attributes to be shown in the frontend, and keep this entity in sync with the main entity. This would add complexity and might not help at all in your situation, however with a similar approach I managed to resolve severe performance issues without refactoring the entire application..

Hope this helps!

Joost Stapersma

Arnaud De Backer · Answer 2 · 2024-12-19

Thank you for your answer @Joost,

Unfortunately we already tried tweaking the connection pooling without any effect.

The best solution on our side we can think of so far is to create a read-replica of the database into the same datacenter as the application and use a proxy in front of this database cluster to redirect read SQL queries towards the read-replica.

But allow me to elaborate a bit more on the solution 3 I proposed in my previous post, because it may solve the issue once-and-for-all.

Disclaimer: I don't have access to the Mendix codebase so I only can make guesses about how Mendix is working.

According to me, there might be a way of improving a lot time taken by the process that fetches related entities without having to refactor the entire module.

When fetching an entity, Mendix will loop through all related entities, I assume it does it for all 1-on-1 associations, at least. It performs a single SELECT statement for all related entities. It is actually fine doing that, we saw that all these queries are executed almost instantly by the DBMS. All these queries also have the advantage that they can be put into a cache very easily. The real problem is that Mendix seems to wait for a SELECT statement to finish before executing the next one, this ruins performance when running in a network with even a slight latency. What I think is happening is that the process of fetching all related entities is performed into the same single thread using a kind of for loop and since Java is a blocking language, all SELECT queries are executed sequentially. If this assumption is true, the work on Mendix side seems quite scoped and limited: They will have to multi-thread this part of the code only.

I'm eager to help on this if necessary and if possible.

Arnaud

Andrej Koelewijn · Answer 3 · 2024-12-19

Hi Arnaud,

Thank you for you input and suggestions, i will discuss with the rnd team what we can do.

Performance has a high priority for us so we will definitely look into this.

Some work is already in progress to improve performance:

* View entities - this is a new feature that will allow you to use "named oql" queries to more efficiently and with more control determine what data you need. Associated data can be included in a single query, avoiding the association queries generated by the platform. Tests at customers have shown up to 15x performance improvements for data grids where multiple associated entities were used in a data grid.

* Replace association tables with foreign keys - although associations tables can be useful to support model changes, they do come at a performance cost. We're currently replacing association tables with regular foreign key constructs, which will significantly reduce the number of queries for associated tables.

Some initial feedback from the dev team: queries running in the same transaction on the database need to share a single connection from the connection pool, this blocks parallelization. We've done internal tests running queries in parallel but didn't see much performance improvements.

This page has some interesting notes regarding threadsafety of jdbc sql connection: https://www.baeldung.com/java-sql-connection-thread-safety

Andrej

Attila Szabo · Answer 4 · 2024-12-19

Have you identified the source of the SQL queries, most of the time this type of issues comes from chosen suboptimal retrieves of Data in mendix. eg : your symptoms can be caused by placing a dataview inside table row, or just a simple Loop what requesting additional information over and over again for each List item.

According to our experience to improve performance you can:

- tune retrieve logic

- tune model and retrieve logic eg. save copy of association attributes during create on master object

- tune access control

- tune presentation layer (sync / async)

- use OQL

- precalculate data whit schedule jobs

i think the first step would be to identify the root cause. where those repetitive SQLs comes from

Slavko Skoric · Answer 5 · 2024-12-19

Hey Arnaud,

Looking at your post, there are two points that provie that your Mendix queries are insanely fast.

First point is checking your screenshot we can see that your queries StartTime and EndTime have gap of 0.000 sec and that is amazing right? and second point When the database is deployed in the same datacenter, there is no gap between all the queries and they roughly get executed in the same millisecond. , so this mean it's not problem with Mendix. You are using Mendix on-premises? Probably yes, so your next point is find what is cause delay it's not Mendix for sure I can guarantee you that. Depends on which DB you are using in logs you will something like following e.g. Oracle - "SQL*Net message from client", this message should be your starting point to start reaching out to other people that are involed in process of making application live (DBA, Infra...),maybe you have multiple schemas on DB that are stealing your resource since you are on datacenter? maybe docker image is outdate? maybe your proxy is messing around with message?

Mendix Community - Question Details

And for other part that you provide that you ask:

If you pay attention to each of them, you'll notice some patterns: some ID's are used over and over again in order to fetch another entity. For example, the ID "68398419657003969" is used 57 times in order to fetch other entities.

For me it's just look as bad development, I will love to see some screenshot of microflow that are causing this.

I hope you will find solution to your problem.

Best regards, Slavko