I wanted to know more about how mendix queries the database so I installed PostGreSQL on my machine with PGADMIn and set the logs to log every single call to the database. Then I created a brand new app from scratch and made it SUPER SIMPLE. I added only these two entities and put in just two parent records and 2 child records each (4 child records total) I set up a home page with a list view that shows all FIRST ENTITIES (only 2) and a list view inside that shows each SECONDENTITY (2 each). A well optimized program should really only need to make one single SELECT statement call to the database to get the information needed (one call to the main table and a left join to the child table). However, looking at the PostgreSQL logs, loading the home page shows *MANY* calls, many of which are redundant. I can see how maybe it would make one call to get all the FIRSTENTITY records and loop through each child record to get the second entity data (1 parent, 4 child calls). This wouldn’t be optimal, but I can see how it would happen to make this as modular a possible. However, even THAT doesnt happen. There are 9 CALLS to get the data to show up here: SQL calls (that don’t include the calls on the overview page, this is JUST to display the home page). SELECT "myfirstmodule$firstentity"."id", "myfirstmodule$firstentity"."field1", "myfirstmodule$firstentity"."field2" FROM "myfirstmodule$firstentity" ORDER BY "myfirstmodule$firstentity"."id" ASC LIMIT $1 2023-05-25 14:30:52.538 EDT [9316] DETAIL: parameters: $1 = '112' SELECT "myfirstmodule$firstentity"."id", "myfirstmodule$firstentity"."field1", "myfirstmodule$firstentity"."field2" FROM "myfirstmodule$firstentity" WHERE "myfirstmodule$firstentity"."id" = $1 2023-05-25 14:30:52.602 EDT [9316] DETAIL: parameters: $1 = '13229323905413799' SELECT "myfirstmodule$firstentity"."id", "myfirstmodule$firstentity"."field1", "myfirstmodule$firstentity"."field2" FROM "myfirstmodule$firstentity" WHERE "myfirstmodule$firstentity"."id" = $1 2023-05-25 14:30:52.605 EDT [20064] DETAIL: parameters: $1 = '13229323905413906' SELECT "myfirstmodule$secondentity"."id", "myfirstmodule$secondentity"."fielda", "myfirstmodule$secondentity"."fieldb" FROM "myfirstmodule$secondentity" WHERE "myfirstmodule$secondentity"."id" IN ( SELECT "atj1MyFirstModule$SECONDENTITY_FIRSTENTITY"."myfirstmodule$secondentityid" FROM "myfirstmodule$secondentity_firstentity" "atj1MyFirstModule$SECONDENTITY_FIRSTENTITY" WHERE "atj1MyFirstModule$SECONDENTITY_FIRSTENTITY"."myfirstmodule$firstentityid" = $1 ) 2023-05-25 14:30:52.647 EDT [9316] DETAIL: parameters: $1 = '13229323905413799' SELECT "myfirstmodule$secondentity"."id", "myfirstmodule$secondentity"."fielda", "myfirstmodule$secondentity"."fieldb" FROM "myfirstmodule$secondentity" WHERE "myfirstmodule$secondentity"."id" IN ( SELECT "atj1MyFirstModule$SECONDENTITY_FIRSTENTITY"."myfirstmodule$secondentityid" FROM "myfirstmodule$secondentity_firstentity" "atj1MyFirstModule$SECONDENTITY_FIRSTENTITY" WHERE "atj1MyFirstModule$SECONDENTITY_FIRSTENTITY"."myfirstmodule$firstentityid" = $1 ) 2023-05-25 14:30:52.647 EDT [20064] DETAIL: parameters: $1 = '13229323905413906' SELECT "atj2MyFirstModule$SECONDENTITY_FIRSTENTITY"."myfirstmodule$secondentityid" AS "parent", "atj2MyFirstModule$SECONDENTITY_FIRSTENTITY"."myfirstmodule$firstentityid" AS "child" FROM "myfirstmodule$secondentity_firstentity" "atj2MyFirstModule$SECONDENTITY_FIRSTENTITY" WHERE "atj2MyFirstModule$SECONDENTITY_FIRSTENTITY"."myfirstmodule$secondentityid" IN ($1, $2) 2023-05-25 14:30:52.649 EDT [20064] DETAIL: parameters: $1 = '6473924464345284', $2 = '6473924464345412' SELECT "atj2MyFirstModule$SECONDENTITY_FIRSTENTITY"."myfirstmodule$secondentityid" AS "parent", "atj2MyFirstModule$SECONDENTITY_FIRSTENTITY"."myfirstmodule$firstentityid" AS "child" FROM "myfirstmodule$secondentity_firstentity" "atj2MyFirstModule$SECONDENTITY_FIRSTENTITY" WHERE "atj2MyFirstModule$SECONDENTITY_FIRSTENTITY"."myfirstmodule$secondentityid" IN ($1, $2) 2023-05-25 14:30:52.649 EDT [9316] DETAIL: parameters: $1 = '6473924464345475', $2 = '6473924464345717' SELECT "myfirstmodule$firstentity"."id", "myfirstmodule$firstentity"."field1", "myfirstmodule$firstentity"."field2" FROM "myfirstmodule$firstentity" WHERE "myfirstmodule$firstentity"."id" = $1 2023-05-25 14:30:52.666 EDT [20064] DETAIL: parameters: $1 = '13229323905413906' SELECT "myfirstmodule$firstentity"."id", "myfirstmodule$firstentity"."field1", "myfirstmodule$firstentity"."field2" FROM "myfirstmodule$firstentity" WHERE "myfirstmodule$firstentity"."id" = $1 2023-05-25 14:30:52.666 EDT [9316] DETAIL: parameters: $1 = '13229323905413799' Is there a way to design the app better to make less SQL calls? Again, this is as simple of a model as I can think of.
asked
Brian Lorraine
1 answers
0
Hi Brian,
When you have nested listviews, Mendix will execute an extra query for every record displayed.
You can minimize this by using one listview which retrieves data on the lowest level and includes information about the parent level. Displaying this nicely might become more difficult, so that's the trade-off.
I agree the current way of working is not optimal, but as long as the number of records are limited, the end-user won't really notice this. It's like programming in assembly vs. high code vs. low code; it's getting less efficient, but way more generic to use.
I'm curious what your reasoning is to have this more optimal.