Hi All, Please find idea inline,
> >> *Transitive Substitute* >> We need to lookup for an available user if the given substitute is >> unavailable. >> >> Option 1: With the above DB structure, we will have to go through a >> recursive call until we find an available substitute. Since this is getting >> executed for each user task creation this will be a problem in performance. >> > > I am not sure which underlying DBMS we are using. There are relational > DBMS that process recursive queries quite well! From my past I know that > DB2 was very efficient in computing bill-of-material recursively. > > Can we do some measurements?= > > > >> >> Option 2: Adding a new row to store the 'transitive sub'. With this, we >> don't need to calculate the new substitute in each task creation. >> But, How do we update this field? A scheduler? >> > > If we decide to use a scheduler, it must be customizable. I.e. the period > has to be able to be set at different granules (globally per installation, > per process model etc - see motivation in my separate mail on this > subject. > > I think as much as possible we have to achieve this with Event-driven architecture rather than schedulers. If we use the event driven way we can cover shift times, short leaves, half day, Out of office cases easily. And as Frank mentioned above, schedulers should be configurable. If we provide functionality to configure scheduler per process, so we end up with many schedulers or scheduler with high frequency. The event-driven approach may expensive in some very rare worst case scenarios if we don't use some DB level simple caching mechanism . So we can use simple cache mechanism as below: When task hits to a person which is unavailable, we can use *recursive query* passing the responsibility to resolve the substitute (considering transitivity). In case the result is transitive substitute, we update transitive_sub column with the result Assignee -> Sub (Transitive-Sub) [Invalidate] .... A -> B (F) <invalidation Timestamp> B -> C (F) C -> E (F) D -> E (F) E -> F And we keep the resulting substitute (+Transitive-Sub) without updating until invalidation timestamp (simple cache mechanism). If we need we can provide to configured this cache time per process (so if the organization only interested about per day, the transitive-Sub is calculated only once per day, per unavailable assignee. So now we reached the same performance of the scheduler ran once per day :) ). So due cache invalidation can be configured per process, if the user doesn't care task creation time, user can reduce the timeout. If cared about creation time, can increase up to 24Hrs. So tradeoff decision can be made by the user, depending on their use case/ usage. So likewise with little enhancements, we can avoid using schedulers without compromising performance. *In case we decided to use schedulers* How about recalculation of the substitute by stored procedure, rather than handling the logic in BPS. So the scheduler only triggers the stored procedure. Thanks, Milinda -- Milinda Perera Software Engineer; WSO2 Inc. http://wso2.com , Mobile: (+94) 714 115 032
_______________________________________________ Architecture mailing list [email protected] https://mail.wso2.org/cgi-bin/mailman/listinfo/architecture
