rewrite for supply path logic

[jdetaeye]

after all model changes, the code is unnecessarily complex and should be revamped.

Core logic is fine, but the recursion data structure is not the best one any longer...

[jdetaeye]

Time to reprioritize this?

• performance improvement with recursive query
• review logic for the newly added auto-producing-flow. Current it issues a query per entity

[jdetaeye]

#1091 also impacts this - suboperations can now also be defined from the operation table itself with a simple "owner" field

[hichamlahlou]

@jdetaeye
If you have a wago model somewhere, I would be curious to know if this is faster than before.

[jdetaeye]

Can't compare yet - the Wago model has many routings and also some alternates, and the new report doesn't come up correctly yet. To give an idea of depth and size of the supply path: between the "demand X asks" and "demand X gets answer" it is common that there are >1500 lines in the log file. Without the new log file structure I wouldn't have been able to solve the latest issue they found.

[jdetaeye]

Wrote my previous commit without seeing your latest commit...
Will do a comparison right now.

[hichamlahlou]

What is still not implemented is alternate operations (type=alternate), that piece of code is still missing but the logic should be quite close from the routings.

[jdetaeye]

Quick test on Wago dataset https://wago.frepple.com/scenario16/supplypath/forecast/2002-1408%7C20021408_400019_40All%20customers/

Cloud-live:

New report:

• committed fix for forecast supply path
• doesn't open, but results of SQL query seem to match at first glance
  
• master query runs for 43 seconds. Not sure how much room for optimization there is.
  

[hichamlahlou]

Hmmm... clearly disappointing :-(
Will see where we can save some time.

[jdetaeye]

On community branch (using the "demo" dataset) I see this case where the purchase operation isn't shown in the supply path:

Even when I specify the location it doesn't work correctly. -> Update: That is not correct. When filling the location in the itemsupplier records, i get the correct results.

[hichamlahlou]

I tried few different things for this ticket.
Current committed code is preprocessing all operations with their bom in a cte but that can take up to 45 seconds to display the graph on huge models as seen on wago dataset.
Then I tried to write a sql starting from a buffer and looking for upstream objects so that I wouldn't have to build the huge cte described above.
Problem is it's almost impossible to have a single recursive query that goes thourgh the upstream objects considering that recursive queries in postgresql have limitations:

• we cannot put a "union (all)" in the recursive part of recursive query (which is a problem as distribution and purchasing operations are not part of the operation table) so we need to use an OR which makes the query more complex.
• we cannot use an aggregating function (jsonb_agg or array_agg) in the recursive part of a recursive query. It becomes also a problem when trying to fetch the bom of an operation into a single field.

Also, the fact that the producing record became optional in operation material tables adds complexity with plenty of coalesce here and there.
However, the new suboperation model using owner_id and priority made the query easier

I tried to write a query considering above constraints and, even before finishing writing it, it gave poor performance results on wago dataset.

bottom line is either

• we stick to current commits (plus a couple of updates to fix problems found) => poor performance on large datasets

• we keep current implementation that is faster on large datasets but still takes some time as een during wago workshop.

• we preprocess the data as part of the supply planning (either we build the all_operations cte upfront or add a column upstream_operations at operation level) => extra data in the database, no refresh of the data unless a workflow has run.

[jdetaeye]

I would rule out the third option - makes things difficult and unintuitive for newbie modellers. Not worth going that route.

Maybe a 4 fourth option: we could have 1 query per buffer, and let the view call&combine these.
This would overcome the limitations and complexity of recursive queries, and still reduce the number of database calls drastically from the original level.

For 6.0 release, I leave it to you to choose between options 1 or 2, or spend some more time on the 4th option.

[hichamlahlou]

Put fix where item supplier location is null

[hichamlahlou]

Not perfect but to me no more a showstopper if you want to move new django to cloud-live.
currently:

• operations of type 'alternate' not supported.
• slower on very large dataset (like wago).
• does only work with new suboperation model (where parent is defined in operation.owner_id)
• found a problem on the routings drawing on wago, only one suboperation is drawn. Not sure why as data is the same as with old code. It could be linked to the order of the records that are sorted differently.

What is left now is to rework the whole thing to have a query per visited buffer (operation?).

[hichamlahlou]

Actually, thinking twice about it, the fact that this code only works with new suboperation model makes it a showstopper. It can only move with the 6.0.

[jdetaeye]

Gives an error on wago model on this URL: https://wago.frepple.com/scenario8/supplypath/item/0221-0412%7C2210412/

[jdetaeye]

On the wago model, the latest query runs for 222 seconds on my laptop...

[hichamlahlou]

just pushed to community and enterprise a first version of the recursive supply path.

[hichamlahlou]

I think it deserves testing
cannot do better than 5 seconds for complex wago supply paths like this:

Hexcel alternates are also supported, world record is this one with 27 alternate suboperations:

Had to add a security to stop recursion at some point. Problem rises when you go downstream from a raw material on a wago-like model. You might end up visiting all supply chain. Aligned on html, recursion stops at 400 nodes.

[jdetaeye]

Performance on wago dataset is like 20 seconds for this one.
The routings aren't displayed correctly here. The steps are numbered 5, 10 and 14 - could that explain it?

[hichamlahlou]

Are you sure you have latest code ? It should show up in 5 seconds.
Suboperations look like this because they have not been correctly numbered:

[hichamlahlou]

added a fix for incorrect numbering of suboperations

[jdetaeye]

Looks good now. Time is about 7 seconds - better than what we had with the old generation of the report code.