I have a relational database consisting of a few million of records having many different relations, which can have additional relations. E.g. product can have its site to display on, and its image, which can have its default site.

Now, with tuned-up entity framework query and indexes, one query takes approx. 1.5 – 2 seconds.

Now I have REST API to provide these data, ideally all on one request so I can not use design to offer all possible related sites e.g., not all sites.

Some of these data have possible updates, so using document DB to store everything is impossible, because updating e.g. site would need to update millions of products, millions of images … etc.

What I have in mind so far is to have some of these data pre-joined and then product query can pre-load some of these structures already. Is there any better solution for this? Maybe specific DB/system design or DB engine?

thank you