I’m sure that many of you have come across slow-performing searches in Windchill, especially when using wildcards. It might be snappier if you have Index Search installed, but only when the Keyword: field is used; as soon as you enter something in the Criteria section of Advanced Search, the system falls back to basic database queries.
Under the hood, the bulk of the time and resources spent during wildcard search is in database queries containing something like "..AND NAME LIKE ‘%mypart%’…", i.e. with filters that include leading and/or trailing wildcards.
New functionality was introduced in Windchill 10.2 to deal with this. The basic idea is that, for a given business object type and attribute, a separate table is created in the database that holds indexed columns with substrings of the current values. Windchill can then do exact searches of these substrings without using wildcards, which speeds things up significantly. We’ll go through an example here to illustrate how to use it.
Configuring it
I’ll be adding a substring index for the attribute ‘name’ in the business object class wt.part.WTPartMaster. The actual creation and maintenance of the substring index tables is done with the SubStringIndexTool, but before running it, it needs to be configured.
The tool reads the configuration file <Windchill>/conf/subStringIndex.xml. This file does not exist in OOTB Windchill, so I’ll have to create it manually.
For this example, the content of the file looks like this:
The parameters className and attributeName are set to the class and attribute on which the substring index is created. I want the index table name to be JLAPARTINDX; if this is not specified, it will default to <table>$<attribute>$SSI, which is a bit long for my taste. I don’t want it to be case sensitive (caseSensitive=”false”) and the index length is set to 6 (indexLength=”6”). I’ll cover that later when we look at the results.
I’m also enabling pre-query, which is one of two available approaches for applying the substring index during searches. The one chosen here means that Windchill first queries the substring index table for matching object IDs; these are then used to query the actual business object table. There are more details on this in the JavaDoc of the wt.pds.subStringIndex package.
With the configuration done, we can run the tool to create the substring index.
Running the tool
The substring index is created with the following command:
windchill wt.pds.subStringIndex.SubStringIndexTool create <user> <password>
where <user> and <password> is the Windchill database schema username and password. The result is a new table that looks something like this:
The ID column holds the object ID of the related WTPartMaster; the VALUE column holds the full values of the selected ‘name’ attribute. The SS* columns hold the substrings of the given lengths that have been found in the attribute values. This is where the ‘indexLength’ parameter comes in; it was set to 6, so it will generate six SS* columns. SS1 holds all substrings of length 1, SS2 holds the ones with length 2, and so on.
For example, one of the attribute values is '01-51284.prt'. The substrings for this value start in row 13 above: as the POS value increases, you see the various length substrings for that value in the different SS* columns.
So how does it work? Say a user does a search for *5128*. The substring between the wildcards is four characters long, so a fast, indexed search is done in the SS4 column for the exact value 5128. The query returns the ID column values, which are then used to find the matching entries in the original WTDOCUMENTMASTER table.
Maintaining it
A separate table is being used for the substring indexes, which means that a mechanism is needed to keep it in sync with the original business object table. There are three options:
Event-driven – sync is triggered insert, update and/or remove events in Windchill
Scheduled – sync takes place at scheduled, regular intervals, triggered from a background queue.
Manual – sync is done manually with the SubStringIndexTool
This is configured in the subStringIndex.xml file mentioned earlier. Details on these and how to configure them can be found here.
Going forward
If this looks interesting and you wish to implement substring indexes, you’ll find further information in the Windchill Help Center. The purpose of this blog entry was to raise awareness of its existence, as we have seen very few queries on it in Tech Support.
Thank you for your time; as always, comments and feedback are greatly appreciated.
View full tip
