Mendix adds sort on ID to datagrids select queries -> slowing down performance

6
We're experiencing slow datagrids on a table with 4 million records.  When debugging in a test project I noticed that Mendix is always adding ID to the Order by clause: SELECT "myfirstmodule$test"."id",  "myfirstmodule$test"."a"  FROM "myfirstmodule$test"  ORDER BY "myfirstmodule$test"."a" DESC,  "myfirstmodule$test"."id" ASC LIMIT 20 This is a problem because there is no index on the combination column (a, id). There is a index on column a, but it can't be used. This results in a whole different query plan, slowing this datagrid down 20 times slower Anyone else experiencing this problem? Why is Mendix adding the ID column to every sort? Idea's to work around this issue? Here the full example based on a different but similar query. In this case there is an index on createddate   Update 1: I have some extra info as I inspected the indexes with PG admin. It seems that Mendix is adding ID to every index as well. The problem with the example is that I'm sorting on createddate DESC, id ASC -> while the index is created on createddate ASC, id ASC.  So sorting ASC is fast, sorting DESC is slow since you have no influence on the direction of the indexes from within the Modeler.  Leaving ID out of the indexes and out of the Order By would fix all this problems.
asked
3 answers
3

Hi Herbert,

In Mx 7.14.1 the index configuration is extended to specify for each attribute the sorting direction of the index: release notes. To change the index in the Modeler you need to upgrade or otherwise change in the database configuration.

answered
2

In those kinds of cases consider adding additional indexes by hand.

There are so many different types of indexes, and different ways of configuring those indexes that simply adding all those combinations makes indexes to complex to configure in the platform. The index configuration we have in the platform works for most cases. 

If for that specific scenario you need different indexes just add them directly to the database (in 7 you have jdbc access to do/check this at startup).  For another project with 30+ million records in an entity we added a custom index on the table and got the (contains) search on that datagrid down to 200ms (from 20+ seconds without indexes).
Due to the custom nature and expertise necessary to add the right indexes, at least for now, we chose to not include this in the standard index functionality. (Adding those things wrong can ruin your database response).

answered
2

I can answer your specific question " Why is Mendix adding the ID column to every sort? Idea's to work around this issue? ".

Datagrids are paginated. If you leave our the sort on an attribute that's guaranteed to be unique (which id is), you will get undesired results with pagination. If you leave our the sort by id, the database is allowed to use any ordering for every individual query as long as it still conforms to your specified order by.

Let's say you have 100 records in your database. Fifty of those have a=Henk and 50 of those have a=Herbert.

If you show 10 records per page and you would only order by a, the database might show the same 10 records for the first 5 pages and the same 10 records for the last 5 pages. This seems counterintuitive but makes sense if you think about what is the fastest way for the database to come up with results that meet the specified order by criteria.

This behaviour will be different in practice for different dba vendors, but according to the ansi sql specs (or whatever they're called) you get no guarantees of having any sensible sorting if you don't order by at least one unique attribute.

answered