Worklist Performance Considerations
Blog: Collaborative Planning & Social Business
This is a technical deep dive into system design around providing a worklist to workers in a BPM system, and the technical tradeoffs you will encounter.
We assume a system where you have an application that is designed around business process diagrams. Those diagrams describe tasks to be done by people, and so you need work items which is an object that communicates the need for a person to perform a particular task. The user finds the work items assigned to them, picks one, completes the task, and in so doing advances the process to the next step which might involve more work items for them or for others.
We assume that activities will really be assigned to some sort of role or group which have the skill or responsibility necessary to do the job. This is held separately in some sort of organizational directory which maps group name to a list of individuals who are in the group at the time.
In order to scale such a system, we need to think about how this is structured in the database. What we want to avoid, is a full search of the entire database every time someone wants to see a list of their tasks. So we break the work items out into separate records which are indexed by the assigned user. If process instance 10005 has an activity for “Review” assigned to a group called “Reviewers”. If that group has 5 people in it, then 5 work item records are created in the work item table, one for each assignee. This allows a simply database query to find all the work items assigned to “alex” with a single highly efficient query. When that activity is completed, those 5 records are deleted so that they no longer show up in anyone’s work list.
That is all pretty straightforward until you think about one thing: what happens when the members of the group change? What if a new sixth person joins the group?
The answer to how this should be handled depends on the characteristics of the process and the rate of change of the group.
Group Level Work Items
As described above, work items were created for each individual, but there is another possibility: create a single work item for the group. That is, create a single work item for “Reviewers” instead of for the 5 individual members of the group.
Then, instead of searching for all the work items for “Alex” you search for work items for “Reviewers”. But a person may be in multiple groups, so to find all of their work items, you will need to search for all the groups they are a member of. If Alex is a member of 6 groups, then 6 database queries need to be run (or one fancy not-so-efficient query).
The advantage of the group level work items is that when someone joins the group, all of the existing work items become immediately available to them. With the individual work items, when “Frank” joins the group of “Reviewers” he has no work items initially, because all the existing work items are already created for the other five individuals. A query for work items assigned to “Frank” returns nothing. The advantage of a group level work item is that within a moment after Frank is made part of the group, all of the work items assigned to the group “Reviewers” are immediately available to him.
Refreshing the Work Items
An alternative to group level work items, is an option to regenerate all the work items in the system. That is, walk through all the process instances that involve the group “Reviewers” and regenerate all the work item records. This will typically delete the existing work items, and then create 6 work items now that there are six people in the group.
This has the advantage that Frank can now just do a search for work items assigned to Frank and see all the reviewer tasks, but this is not instantaneous. The group is changed, and then the refresh operation is done, which might take a few minutes and might not be scheduled until later. Refreshing the work items on a large BPM installation can be a serious hit to the resources. If you have 1 million processes, this operation along might take the bulk of 20 minutes of server time, or at the very least put a serious load on it that effects everyone else. This is contrasted to the group level work item which produces absolutely no additional load when adding or removing people, but causes some additional complication by having to check multiple work lists.
Determining which of these to use depends on the characteristics of the process being implemented.
Very Fast Processes
If the activity in question is handled very quickly, then there is no need to bother. For example, in a trouble ticket system, the goal may be to accept the first activity in a matter of minutes. If a process exists for only 5 minutes in that particular activity, then there is no real reason to worry about Frank not having all the current tasks. Just wait 5 minutes and there will be new ones available for him to take up. The additional bother of arranging to get workitems on existing processes just simply is not worth it.
For the most part we are considering a process activity which might hang around for days before being handled. in that situation, you would not want to wait days for Frank to get tasks. For the rest of the discussion, assume we are talking about tasks that take anywhere from days to months on the average.
Frequency of Organizational Changes
The next question is how often the organization changes. If Frank is hired on, and expected to stay years in the position, then you would expect that a person joining would be relatively rare. If you add a person only every couple of months, then recalculating all the work items for each group is a reasonable thing. The overhead at run time retrieving the work list is minimized by being able to do a single query, and the refresh is done only every month or so.
On the other extreme, if this is a charity volunteer organization where different people are dropping by, and the group of people doing the work changes daily or even more frequently, then the overhead of refreshing the work items is large. It would need to be done daily or hourly, and that additional load on a large system would exceed the benefit. In such a volatile organization, one would be far better to choose the group level work items. Just have the person who is sitting in for an hour search for work items assigned to the group, and complete them that way. There is no utility in creating the individual level workitems that will only exist for a day or so, on a task that typically takes more time than that.
As a rule of thumb, updating the database is about 10x heavier hit than just querying it. Thus, if you use an update in order to save querying, then you need ten queries saved for every database update you do. This is not precise at all, particular if the query size is different form the update size, but lets use this to get a feel for the tradeoff.
If you have 100 people who participate in 6 groups each, then for individual work items you will have 100 queries per hour, and with group level work items you will have 600 smaller queries per hour. It is worth noting that with group level work items the work item table will be 6 times smaller as well.
Say you have 100 processes alive at a time. These are distributed equaly across 10 groups.
With 10 groups and each user in six of the groups, then each group has 60 members. There will either be 6000 individual work items, while there would be only 100 group level work items.
Since each group has 10 processes, and each individual is in six groups, the typical individual worklist will retrieve 60 records. Using group level work items you would run 6 queries (one for each group) retrieving 10 records each.
Changing a group and doing a mass update will cause 6000 individual work items to be updated. Because of the 10-to-1 weighting of updates vs. queries, this is equivalent to 60,000 records queried, or 10 hours of querying.
The real difference lies in this: what is the difference between making a single query for 60 items, versus 6 queries for 10 items each? What is the overhead of submitting six queries, versus a single, larger query. With proper indexing the difference should be slight.
This analysis leads me to believe that if a user is in a fixed number of groups, that asking the database for the work items by group might be more efficient than re-updating the database every time a person joins or leaves the group.