Right now I'm working on developing a DB schema for an application which requires versioning several different sets of numerical data which must be joined together for use in views. The application requires that we're able to 'look back' and find the state of what these multiple variables were like at an arbitrary point in time.
I've built up a few view definitions using subqueries to fetch the various inputs along arbitrary join criteria as well as checking the version against a
time_created field. This isn't a strict SCD2 implementation, but it's close. (link to Slow Changing Dimensions page in Wikipedia)
You can see a similar ( albeit greatly simplified ) implementation to our schema here: DB Fiddle link. The real schema has 6 subqueries/joins.
This works very well for my purposes, except that when we start scaling it up into the thousands of 'purchase' records we incur a significant query cost. A query against 2000+ rows is taking ~1s, which is unsuitable for our application.
The first solution that springs to mind is creating a table which joins a 'purchase' to it's various versioned input tables, to be created by the application when a purchase is created. This should be much more efficient, but requires more schema complexity.
Can anyone offer me advice on how to best plan my schema/queries so as to be most optimal for this kind of setup?
Admittedly this is a cross-post from SO, but it received zero interest there over 24 hours, so I figured it may be better suited for the DBA stackExchange. Apologies in advance if this causes any offence. The original can be found here.