ORDER BY performance #36
Description
Result of the discussion with @prabash @yampeku
We used to sort using ORDER BY context start time.
The EVENT_CONTEXT in openEHR is optional, therefore using that to sort will exclude some compositions, therefore we removed it.
We tried to filter instead over the commit_time of the VERSION.
These had several problems, ORDER BY on the version commit_time is not support by all vendors.
Secondly, some vendors default the LATEST_VERSION predicate (but not support it explicitly) others require it.
Therefore there cannot be one query consistent query for all, this has been reported to the vendors.
Another option would be using ORDER BY e/time_created/value" which is again not supported by some vendors.
We use now ORDER BY on the ehr uid value, this might be performance wise not the best pick.
Since requires sorting on UUIDS.
We should migrate to either version or ORDER BY e/time_created/value in the future if it really is better performance wise.
This needs to be checked with several platforms using large enough data-sets.