While I'm aware that many of those optimizations can be built on top of the DAL, I think it would be nice if they were provided as optional parts of a more complete optimisation solution with the DAL:
1) Fine Grained Cache Instead of simply caching selects in memory and retrieving it in memory, cache it in memory, but only retrieve it in memory if the data hasn't been changed since it was last stored in memory (otherwise, retrieve it from the database and refresh the memory version). At the very least, support it at the table-level (look which tables the cached request accesses and consider the memory version valid if no insert, delete or update occured on the tables since the last memory refresh ). >From there, you can provide more fine-grained optimisation by supporting it at the field level (as long as no insert, delete or update on the particular field occured since the last memory refresh). And from there, you can make it even more specific by supporting it at the field value level (for exemple: If a request looks for fields with ID less than 100, only consider the memory version invalid if an insert, delete or update is involved with an ID value less than 100). I'm aware this can get more involved for complex requests, but still manageable if you check whenever a record is inserted, deleted or updated (in the later case, you must check twice before the record is updated and after) if it would be included in the select or not. In the case of embedded selects, the above procedure would have to be recursively applied to each select. The above would be useful in situations where there are many more reads than there are writes (which is fairly common). Its definitely more work, but I also think it would be more useful for a lot of people (who need the better performance without a loss in accuracy). 2) Distributed tables With this optimisation, split a table in several tables and spread the records amongst the tables depending on a set of criteriaUse the criteriaduring all database operations to modify/access the right tables. The user could specify the criteria for modifying/accessing the various table and would be responsible for insuring that the combined set of all the criteria cover all possible record permutations. The above would be useful in situations where select/updates are frequent and tend to be localized to a particular sub-table. Ex: If the first letter of the username is in the range a-h (case insensitive), store/modify/fetch the record in table 1. If the first letter of the username is in the range i-m (case insensitive), store/ modify/fetch the record in table 2. If the first letter of the username is in the range n-z (case insensitive), store/modify/fetch the record in table 3. Anyways, some food for thought.
