While my investigations have still left me sad to find that there is no real way to match the read performance of an SQL database I have found out some interesting little pieces of information on Caché.
1. Bitmap indexes always appear to be faster than standard indexes no matter how many unique values there are.
2. Traversing standard indexes manually is the fastest way to return a query first time (second time bitmap index SQL is the fastest).
3. Single Insert performance is good
4. Reversing through an on disk array (e.g. $ORDER(^global,-1) ) is slower than going through it forwards putting the data into an in memory array and then reserving through it! To my surprise all my tests showed that it was at least a 50% performance improvement and as the data grows it can double. The only problem is if the memory array grows too big then you will get a store error as the memory runs out.
I am considering re-writing my queries to only reverse $ORDER in memory and checking the $STORAGE to make sure it is not running out, then writing it to disk if it becomes too large.
It appears to be the case that using SQL with bitmap indexes and potentially using triggers to make sure no values become calculated or require significant levels of joins is the only way to achieve reasonable read performance.
Hopefully this information will be helpful in to you in getting some vague level of performance out of the system.