The goal of the Modern Queryplanner is this:
Reduce server roundtrips for data fetch by analyzing viewmodel context and inform server with precision on what to deliver. Doing this will reduce latency penalties on fetch and reduce over all sql server load by using fewer larger questions rather than many small questions.
The new modern QueryPlanner works like this:
It statically looks at a ViewModel and all its expressions. It decides from this what features of these classes are used and which of these features that are not normally default fetched (all attributes except explicitly marked “delayed fetch” and embedded associations locator ends are fetched by default).
The ViewModel is a non cyclic tree of information transformation. The ViewModel as such holds the instructions for these transformations – ie the expressions. It is important to separate the expressions that comes from the structure of the ViewModel from the expressions for a given level (level of nesting) of the ViewModel.
The reason for it being important to separate structure-need from level-need is best explained with an example:
If the structure has a filter like this:
self.SalesArticlesForAllCountries->select(sa|sa.Country=TheImportantCountry)
And the level columns in the ViewModel nesting has expressions like this:
self.AHeavyLinkForSomethingPerSalesArticle
It is important to not load the “AHeavyLinkForSomethingPerSalesArticle” for all the SalesArticlesForAllCountries that are not TheImportantCountry.
Furthermore it has been found important to use a combination of static model based decisions as well as an intercepted lazy loading.
The reason for the need of both these approaches can be explained with the following example of a level (viewmodel column without associated nesting):
self.SomeNumber + (self.LinkBackToListOfObjectsOfSameClass.LinkBackToListOfSameClass.SomeNumber->sum)
When looking statically at the above expression it is obvious that we need to fetch TheClass.LinkBackToListOfObjectsOfSameClass for all the objects on the level of the matching class. But it is not obvious that we also need to extend the list of objects to load from by looking in the result of the expression. This can however be caught by trying to execute the expression in a lazy load interception harness – it may also be needed to execute the expression again after loading in order to come to the bottom of object hierarchies.
One may argue that the benefit of such milking a hierarchy (shaking the tree) in this rather complex way is small as to compare it to a traditional lazy load – but in fact the benefits are there when we can avoid an uninformed lazy load fetch and instead combine the need for a specific load with the static knowledge of other loads that will be needed as well.
Object hierarchies will entail multiple questions correlated to tree depth and if you need to work around that consider adding redundant links to get to all of the nodes of a tree in one go (root to keep list of all level children) – and then look up parent of tree nodes in a separate expression.
Scenarios
A viewmodel not used for UI – but for a report, a Rest interface, a clone-template, a transform-template or what have you – these do not often hold inner complexity and are normally full tree fetched (every nesting and every level is tracked to the bottom of the tree).
But a ViewModel used for UI has the potential to follow different re-occurring patterns.
One pattern is the seeker – when a large amount of the data is replaced by the result of a search expression from time to time.
Another pattern is the picker-cursored – when a picker indirectly acts as cursor for what to show further down the viewmodel.
The seeker pattern
When you design a seeker to show extended details of the current row of the search result you will probably not want to load details for all the result rows all the time the result is updated. The Modern QueryPlanner recognize the fact that your viewmodel has seeker definitions and changes behavior from the normal fetch full tree to a more interactive cursored approach – fetch nesting details for vCurrent only.
The picked Cursor pattern
It is not uncommon that you design a viewmodel that holds a picker that allows you to choose what details to show. Such choice could pose additional load need and the QueryPlanner must be allowed to see the switch and intercept the lazy load that would otherwise occur. The full-load approach will be applied – but the construction of the viewmodel is such that only one (the currently picked) of many possible branches is actually in the graph and thus picked. To handle this the Modern query planner places subscriptions on nesting roots and execute the queryplan on change of the nestings.
Issues discovered and fixed after initial release
We have two sqlpassthrough ocl operations that are very practical for reversed-database-systems – and they totally messed up the query-planning. The Query planning re-iterates expressions to find out as much information prior to fetch as possible – but having the sqlpassthrough operations inside the expressions was contra productive – since we now ended up creating more network traffic not less. To mitigate this the sqlpassthrough operations informs the queryplanner that they are used – and they avoid to execute during the FetchFactFinder loops. QueryPlanner makes note of such expressions and mark them as outofdate after it has finished.
We have the Constraints ocl operator that evaluates all constraints on an object and returns a tuple list with information about them – if they are broken or not and what the message should be. The constraints was however fully evaluated everytime the constrains operator was called and this became painfully expensive when running with the queryplanner that try to milk information about needed fetches from expressions. The constraints operator was re-written to be remembered and subscribed so that the constraints only need to evaluate when explicitly isbroken-queried (on a per constraint basis) and out of date.
Further issues discovered and fixed
Since the queryplanner iterates expressions and milks the expressions for things unloaded – ie it shakes the tree to find load needs – we had a new type of situation never encountered before; dynamic divide by zero. This can happen with this expressions “something.anumber/something.alink.anothernumber” – then if the “alink” is not loaded when the expression tries to execute inside the no-load harness we get a divide by null. This divide by null could happen in a derivated member that thus was failing its derivation – all in all we must avoid to throw exceptions and disturb derivations and queryplan work. The fix is to discover this case for the division operator.
Yet more discoveries for ModernQueryPlan
Subclasses may override their derived members – and the queryplan did not check for this fully. Also subclasses may have very different DefaultStringRepresentations and this too was not fully appreciated by the queryplanner.
We have also seen hangs due to the queryplanner never finishes the milking operations. This happens when there is a clear load need but the queryplanner does not perform that load. This is of course due to an omission or bug in the queryplanner that must be understood and fixed. But getting stuck in an infinite loop is not acceptable. To combat this issue a fail-safe has been added along with clear logging. The investigate log will start if the QueryPlanner does more than 10-laps, and the fail safe exit will happen after 30 laps. The log till state the members needed to be loaded and I anticipate that the 20-logged-laps will contain the same, or pulsating, members. But as this issue is not root-caused I will need wait for logs before solving this one:
