TWSearch and Saved Search Performance
MarkFilley 27000392R8 Visits (6725)
I would like to talk about the every popular and common features of TWSearch and saved searches for IBM Business Process Manager (BPM).
Searching for instances and tasks is a common operation in IBM BPM. Three common methods are with the TWSearch JS API (example 2), exposed saved search, and the REST API searches (saved search and query). Something we see in Support is "How and why are my searches taking a long time?" Sometimes the performance issue is the query itself and how the query plan of the database executes the SQL sent by BPM. Sometimes the slowness is the pre / post operations around the search calls. For example you may need to have an integration service get data to determine what to search or do some extra processing on the results after they are returned. The best way to isolate what sections are impacting performance is to get a trace and figure out exactly how long the searches are taking. Traces help narrow down exactly what is happening where. I have seen cases where the TWSearch displayed in a coach takes 150ms, and the overall coach load is 1500ms.
How do you know if the search is slow? Here are some places to start looking:
All of the described task search options eventually run the same underlying code. The main class is Proc
Look for these entry and return lines for the time gap to return a search:
0000010d wle_search > com.
From this we already see a good indicator, the number of records returned count=4. The exact name of the query is not printed, but the query parameters are, so that might help in some debugging cases where your TWSearch is dynamic and based on user input. Within the same thread (000010d example) look for the parameters used in building the query, something like parameter par_7 ==> CustomerName [cla
All of the parameter values and the full result set is printed out in the trace. This can help you identify what parameters were searched and the values in them.
Also look for the line where actualSearch is listed. This is the indicator that the task search query is in use. Below is a partial set of the full SQL sent to the database (full SQL is printed in logs):
SELECT actualSearch.*, ROW_NUMBER() OVER ( PARTITION BY instanceid order by taskDueDate, instanceId, task
This portion will sometimes appear in high or long running SQL queries from a database report.
There are several solutions to improve the performance of the searches. All should be investigated by the BPM Operations team as well as the BPM Application Development team.
BPM Application Development
For the saved search tools, remember the rebuild of the tables only happens when a change is made to the existing exposed variables or when new ones are added. If the next snapshot deployment only contains integration service updates or coach improvements, then dropping and rebuilding the pivot vars tables is not needed. The Application team needs to clearly communicate to the Operations team when exposed business variables are modified so the pivot tables are properly configured.
Of the available options for improving performance, adding the pivot tables is the best with the largest reward.