Hi Willem,
In short, the first option retrieve 2 lists will put a wee bit more load on the database. Because you will retrieve it as two different data sets. This also creates 2 object lists in memory (the one with haveRead and the one with toRead).
The second option will retrieve all objects at once, slightly decreasing the load on the database. However you will have 1 list in memory (containing all objects) and then create 2 more lists: haveRead and toRead in memory. Putting more load on the memory. It is advised to clear the list containing all objects (since you won't be needing it, and this allows the garbage collector to collect these objects and free memory) after you created the two filtered lists.
I'm not sure if there is one right answer, many factors can influence the speed (the hardware, mendix memory and database performance, indexes, size of the data set). I would recommend to test both cases and measure the performance. This works best if you have a production-like data set available.
With kind regards, Stephan
A lot of things can be said about this question:
Only try to optimize something like this if there really is a need to. In this specific example both choices will be fine, because a database is very good at retrieving based on an indexed boolean, and a cpu is very good at filtering lists based on a boolean.
Benchmarking these two cases against each other may not make any sense, since it is likely that in practice you're measuring a .001% impact on total processing time for handling the complete request/scheduled event against a .002 % impact. So even if one method is twice as slow as the other in a benchmark, it may still not be relevant.
If you're just interested in this out of curiosity or because you are sure this really is a performance bottleneck, the only way to be sure would be to do a benchmark on an environment that is a good representation of you production environment.
In general, it is better to go for a model that is the best reflection of what you're intending to do, not what performs best. Real world performance issues generally do not occur because of a choice for something like this. Way more likely are additions to a model that's not well understood, or things like missing indexes, way more data then expected, or an inefficient algorithm.
In a well performed benchmark, my money would be on the two separate database retrieves being faster.
I would recommend the 2 database retrieves every time. It will be the more efficient route probably more than 99% of the time.
A relational database is designed and optimized to work with sets (especially when filtering by a Boolean that is indexed), while the in-memory Java processes must use loops filter records.
As a default, I would retrieve twice rather than retrieve once filter twice...uses less memory I believe. Curious to see what others say.