TWSearch and Saved Search Performance

Body

The popular and common features of TWSearch and saved searches for IBM Business Automation Workflow.

Searching!

Searching for instances and tasks is a common operation in IBM Business Automation Workflow. Three common methods for searching are the TWSearch JS API (example 2), exposed saved search, and REST API searches (saved search and query). Sometimes searches have poor performance, taking a long time to execute, or contribute to overall performance issues on the server. Sometimes the performance issue is the query itself and how the query plan of the database executes the SQL sent by Business Automation Workflow. Sometimes the slowness is the pre or 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. There are cases where the TWSearch displayed in a coach might take 150ms, but the overall coach load is 1500ms.

How do you know if the search is slow? Here are some places to start looking:

• Custom task list dashboard which uses search is not meeting business SLA (for example 1 minute to render page).
• Database has very high CPU and memory usage when TWSearch functions are used or loading a task list in Process Portal.
• Database reports show a query with actualSearch.* is in the top 10 of poor performing queries.
• Trace logging indicates the ProcessSearchEngineImplexecuteSearchQuery takes 1000ms or longer.

Traces!

All of the described task search options eventually run the same underlying code. The main class is ProcessSearchEngineImplexecuteSearchQuery and the method is executeSearch. The compact search for tracing is WLE.wle_search=all. This trace can be enabled in the Runtime tab of the AppTarget JVM. Once you get a trace and isolate the behavior here is what to look for in the trace logs.

Look for these entry and return lines for the time gap to return a search:

0000010d wle_search    > com.lombardisoftware.server.core.pse.impl.ProcessSearchEngineImplexecuteSearchQuery ENTRY
0000010d ProcessSearch < com.lombardisoftware.server.core.pse.impl.ProcessSearchEngineImplexecuteSearchQuery RETURN com.lombardisoftware.data.api.QueryResultWithCount{count=4 countLimit=0limit exceeded=false results=

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 [classjava.lang.String]

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, taskPriorityRanking, taskId ) AS instance_result_row_idx

This portion will sometimes appear in high or long running SQL queries from a database report.

Solutions!

There are several solutions to improve the performance of the searches. All should be investigated by the Business Automation Workflow Operations team as well as the Business Automation Workflow Application Development team.

Business Automation Workflow Operations:

• Work with the BPM database administrators and confirm current database tuning operations are being performed, see Section 5.2.5 of the IBM Redbooks Publication "IBM Business Process  Manager Operations Guide."
• Increase CPU and memory on database. It could be based on your business load, the database is under capacity, check historical usage to get a baseline.
• Implement the saved search acceleration tools feature. This really is a fantastic feature and can make dramatic improvements in the search queries.

Business Automation Workflow Application Development:

• Reduce the number of business columns and the complexity of search. Yes, sometimes adding a single new variable to a search can drastically change the database query plan which results in poor performance.
• Reduce the frequency of the search (application business logic). The searches are not cached in BPM and are new queries each time.
• Work on data retention policies. Less task data means less work for queries. Communicate with the BPM Operations team to find out which apps and what time period of data is a minimum.

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.

Summary!

Of the available options for improving performance, adding the pivot tables is the best with the largest reward.