Implementing Saved Search acceleration tools

Follow

Starting in BPM 7.5.1, but predominantly in BPM 8.x, for systems with large business data used in searches, the Process Portal may experience delays in returning result sets from saved searches.  To alleviate this issue, IBM has provided an optional tool for Process search optimization:

OVERVIEW:

The default database table layout for business data is located in the table LSW_BPD_INSTANCE* tables tends to be a long table which can be expensive to search.  The tool "flattens" the table making the data easier and faster to search, but with some additional overhead for the administrators.  It does this by creating two new database tables:

  • LSW_BPD_INSTACE_VAR_NAMES (variables table)
  • LSW_BPD_INSTACE_VARS_PIVOT (pivot table)

WARNINGS:

  • You use this process only for production deployments, or for systems that simulate production deployments.  Do not use on Process Centers.
  • You must rerun the tools if:
    • You have deployed a new a BPD or updated an existing BPD (deploying a new BPD snapshot) that contains any searchable business data variables.
    • You have deployed a modified BPD that has changes to its business data variable definitions (adding new variables, renaming any existing variable, modifying the type of any existing variable). 
  • Before rerunning the tools, you will need to drop the two pivot tables listed above (LSW_BPD_INSTACE_VAR_NAMES and LSW_BPD_INSTACE_VARS_PIVOT).
  • Restriction: If you do not do rerun the optimization tools when a BPD has changed, your process searches will generate failures.

STEPS:

FRIST TIME SETUP:

  1. Stop the runtime server.
  2. Next, examine your database tables and note that the following tables do not yet exist:
    • LSW_BPD_INSTACE_VAR_NAMES (variables table)
    • LSW_BPD_INSTACE_VARS_PIVOT (pivot table)
  3. Generate the schema for the new tables:
    • Open a command line prompt
    • Change directory:
      • cd <install_root>/BPM/Lombardi/tools/search-optimizer
    • Enter command to generate schema:
      • ./SchemaGenerator.{sh|bat} -profileName <PROFILE_NAME> -OPTION
      • PARAMETER: -profileName
      • DESCRIPTION: This parameter is optional and should be used if you want to run the tool against a profile that is different from the default profile for your environment. Specify the name of the profile that you want to use.The OPTION argument defines one of the following:
        • execute - The tool immediately creates the entire schema.
        • output FILE_NAME - Instead of immediately creating the schema, the tool writes all the SQL statements the database file defined by FILE_NAME.
        • screen - Instead of immediately creating the schema, the tool writes all the SQL statements to the command window.
  4. Check the database to ensure the tables have been created.  The data is not yet populated.
  5. Loading the data into the new tables:
    • After the table schema has been generated, we need to populate the tables with the appropriate data.  This ensures the search queries will see the existing instances as well as any new instances that may be created.
    • Change directory:
      • cd <install_root>/BPM/Lombardi/tools/search-optimizer
    • Enter the following command to generate the data for the pivot tables:
      • DataLoad.{sh|bat} -profileName <PROFILE_NAME>
    • Once completed, you should see the message:
      • DataLoad completed sucesssfully.
  6. OPTIONAL: One final check of the database tables to see there is data should complete the operation.

RUNNING A SECOND TIME:

When you rerun the tools, you will need to drop the tables listed above (LSW_BPD_INSTACE_VAR_NAMES and LSW_BPD_INSTACE_VARS_PIVOT). After removing the tables, you will need to re-run the above procedure.

Remember, you only need to perform the procedure:

  • If you have deployed a new a BPD or updated an existing BPD (deploying a new BPD snapshot) that contains any searchable business data variables.
  • If you have deployed a modified BPD that has changes to its business data variable definitions (adding new variables, renaming any existing variable, modifying the type of any existing variable)

DISABLE OPTIMIZATION:

To remove the procedure you took above:

  • Shut down the server.
  • Remove/drop the pivot and variable tables from the database
  • Start the server.
Have more questions? Submit a request

Comments

  • Avatar
    Michal Gregor

    Hello. Thank you for great description.
    Could you somehow describe the speed-up of saved search? We have about 50 000 instances for one process (total number of instances is about 110 000). Each instance has about 15 variables checked as business data. Problem is that the customers want almost all of this data shown in portal. Right now, saved search is taking from 5s to 80s to load. I would like to know if the speed-up is about 10% or even about 50%.
    Thank you for reply in advance.

  • Avatar
    Dave Rosen

    Very glad you enjoyed the article. The increase in speed will depend on your specific scenario and environment. To get the specific percent increase in speed, we would have to test your specific BPM code with your environment (or a test environment that is comparable) and measure the load times using a browser tool like firebug or IETools.

    The acceleration tools are simple enough to implement, test, and remove if you need to. I would suggest replicating the behavior in a non-production environment (just to be sure you don't impact production users), and see what the result are. Personally, we have seen increases between 20-30%, however that was just one (very small) test case.

    Hope that helps and if you need some additional assistance, please let us know.

Powered by Zendesk