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.
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.