I will try to give you a different view angle of this problem by separating write and read operations and their respective stores. A big red warning before diving into this in more depth: that is, while you can optimize for performance you are also introducing complexity which you should understand and consider and talk about with your business/operations managers.
Write operation
As you mention your "transaction with a subset of functionality", these are your use cases/models for write operations. This way you can optimise the write models as your business drives you. Your write models could be still stored in your current RDMS choice.
The first important distinction and added value here is that you allow yourself to:
- Separate the write concern from reads (and its persistence store)
- Ease of adding cross cutting concerns like testing, logging, auditing, deadlock management, authorising, scaling, etc.
Your writes and basically commands that originates from your clients requests. These commands contain all the neccessary payload for storing your use-case optimised model to the write persistence store.
Read operation
Depending on your data structure and relations, read models are a way to store data in a denormalized way. As you wrote: a current read operation consists of 14 SQL joins which could be a nightmare to deal with. This is where use-case optimised read models can step in.
Your read models are the queries that serve the denormalised data (maybe from its separate data store). While using separate read models you optimise for performance but also add neccessary complexity.
- Separate the read concern from writes (and its persistence store)
- Ease of adding cross cutting concerns like testing, logging, auditing, caching, authorising, scaling, etc.
Storage of the read side could be ranging from RDMS to fast NoSQL solutions like document db's and key-value stores.
Synchronisation between the data stores
The way I do it is: whenever a command executes it publishes an event containing the payload of id's neccessary to start the operation of reconstituting the read store -to be in sync with the write side.
EDIT: to answer your question you have asked in comment, syncing really depends on your actual architecture. I for one am really against pushing code outside to external sources if possible like triggers, batch jobs, etc... but rather it's inverse, to push all code as deep into my own implementations (which I have direct control over). As said I'm using commands, events and queries for data manipulation.
A concrete lightweight example:
- A request is caught by IIS hosted WebApi or MVC controller.
- Controller populates the respective command object with values from the request.
- A CommandHandler get fired up from Controller.
- The command handler does it's job storing the given write model.
- Raises an event to notify the subscribers about the change
- An EventHandler which is responsible of handling the event raised by the previous command handler, fires up a new command that's held responsible of reconstituting the read store side.
Summing of points above: you have the flow of syncing in your hand, and do not depend on third party solutions like triggers, nt services and so on.
Some things to note:
- since we adhere to Command Query Separation, we can easily decorate point 2 with command authorisation, validation, logging etc. without actually modifying the code in controller or the command handler.
- As you may have noticed we struggle to keep parts of this subsystem as decoupled and testable as possible. Also the concerns are blindingly separated.
Conclusion
All this above is nothing new. I like to call this CQRS Lite - a term I have borrowed from Dino Esposito. He has a very comprehensive tutorial course on this at PluralSight, I recommend it wholeheartedly to watch it there is a lot learn from him.
Another good source in subject is CQRS Performance Engineering: Read vs Read/Write Models from Derick Bailey.