Indexed Task Search and mapping tables

, ,
1

Hello,

We have the following problem on our TASK applications.

We have implemented our task applications using webMethods 7.1.3. This is why they were implemented using Standard Task Search.

In order to have a performance improvement we decided this year (since we are using 8.2 now) to migrate to Indexed Task Search.

The catch is the following:
During the first implementation of our task applications there were a lot of changes that the users wanted regarding the values stored on the tasks.

We have some fields that can have a limited number of possible value (in UI they are drop-downs).
An example field: TaskData/BusinessData/invoiceType
Let’s take for example the field invoiceType that can have the following values:

  • My Invoice Type
  • His Invoice Type
  • Her Invoice Type
  • My Father’s Invoice Type

Because of the following:

  • users always wanted to rename this strings let’s say from “His Invoice Type” to “Their invoice type”
  • renaming this would have meant to iterate over all tasks (active and completed) and update on every task instance the field TaskData/BusinessData/invoiceType from “His Invoice Type” to “Their invoice type”
  • we do not know how to update completed tasks (the API does not allow this out of the box)

We did the following:

  • created a mapping table in the DB with the following content
    ID | Name
    1 | My Invoice Type
    2 | His Invoice Type
    3 | Her Invoice Type
    4 | My Father’s Invoice Type
  • saved on the TaskData/BusinessData/invoiceType the ID and not the name
  • this way when a renaming was necessary we just made the modification in the mapping table and no tasks need to be updated.

When migrating to Indexed Search this has proved to be a problem regarding the sorting of the search results.

The sorting for indexed search is done directly in the DB which means that the ID’s will be sorted and not the actual names.
This means that the sorting against the DB will yield the following results (ASC):

  • My Invoice Type
  • His Invoice Type
  • Her Invoice Type
  • My Father’s Invoice Type

Of course this is not correct from the Name point of view, as it is not correctly sorted.

The only solution we had is to hook in the TaskProvider after the search is done and to sort the results on the TASK Application layer (not the DB layer).
This solution has 2 major drawbacks:

  • we are able to sort only what is retrieved to us (current page of results)
  • performance loss (we are doing an extra sort)

Due to this drawbacks our solution was not accepted by our clients.

I have some questions for which you might know the answer:

  • does the new version of webMethods (9.8) offer any kind of support in this area? Meaning that we are able to seamlessly handle both the INDEXED TABLE and our own defined DB mapping table without loosing any of the provided functionality?
  • are there any statistics on SAG side about how many fields are optimal to be indexed when doing the Indexed Task Search (maintaining DB indexes also implies a slight performance cost). I would really like to see some benchmarks that shows the search performance in correlation with the number of indexed fields.
  • when an application uses indexed task search, what is the reason of saving the task also in the T_TASK DB table in the BLOB. Is it still used somewhere? Because how I see it, it might bring some performances cost when updating a task (which means updating T_TASK, updating the INDEX TABLE and also updating the DB indexes).

Thank you for providing your feedback on this.

Best regards,
Vlad Turian

2

Hi Vlad:

We had a similar issue and we changed our policy about “what data we need/store in business data”. We agreed to store only the necesary data for expressions and transitions. Limit the data in your custom inbox and display the data in the “custom id”. Remainder of your “business” data must be in your database or backend system.

Regarding your particular problem I suggest you to develop a flow service to extrat the business data info and move it into a database table.

Best regards.
Norberto.

3

Hi Norberto,

Thank you for the response.

I think you have a very good idea, although I have to confess that I do not understand it to the fullest.

What I did understand was that:

  • you store the large majority of the data in your own DB or backend system
  • you keep on the task just the strict minimum (data that is used for process transition and, I assume, for expressions that are to be used in the CAF UI, for example)

What I do not understand is:

  • how do you make the linkage between the TASK data and the data from your own DB?
  • how does the search work? At this moment I do not know of a way to join the TASK DB tables with custom DB tables in order to provide a search result.

Can you please elaborate on this so I can fully understand your solution?

Thank you.
Vlad Turian