Substring Index on Windchill table(column) "WTPartmaster(WTPartNumber)" in order to Optimize search

[RK_8524786]

Hi All,

We are planning to create substring Index on Windchill table(column) "WTPartmaster(WTPartNumber)" in order to Optimize Leading and Trailing wildcard search strings like *ABC*.
I wanted to know If someone has implemented it. What are the challenges? Any performance overhead during table syncing.

1. How does synchronization of new Substring Index table "PARTMASTERNUMBERSSI" happen as changes are made into original table WTPartmaster?Which table synchronization option is best?
2. Event-Driven Synchronization immediately reflects the source attribute WTPartmaster( WTPartNumber) changes to Substring Index table "PARTMASTERNUMBERSSI". PTC document says “The disadvantage to this option is that extra processing time is added for the operation”. Does this mean it will impact performance? We have ~ 2.5 million number of rows on "WTPartmaster” table and newly added substring Index table "PARTMASTERNUMBERSSI" has ~ 35 million rows.
3. Where will windchill start searching first primary table (WTPartmaster) or secondary sub-string Index table (PARTMASTERNUMBERSSI)?
4. What will happen with search results, If primary table (WTPartmaster) and secondary sub-string Index table are not in sync due to some reason or delay? Do we still get correct search results from primary table (WTPartmaster)?
5. What will happen for WTPart number searches performed through windchill customization, will it still work as earlier?

I will really appreciate your inputs/suggestions with this. Thank you in advance!

[AndrewMichaelAr]

It looks like you've already read through the reference materials for Substring Indexes. So I'll just try to expand on them a bit.

1. There really isn't a "best" strategy for maintaining these indexes since it mostly depends on your users' typical search habits. The event-driven strategy is the most resource intensive simply because it's used to update the indexes immediately every time a WTPartNumber is either added, removed, or even updated. With this strategy, entries are placed in the SubStringIndexQueue,which immediately processes each entry in the order it's placed in the queue.
   By contrast, the Scheduled Background Queue Processing Synchronization puts entries in the SubStringIndexScheduleQueue, which you can configure to run during certain hours.
   The third type, Manual Synchronization, does exactly what the name implies. So the drawback to this method is you'll need to remember to run it once a week or so to make sure the indexes reflect all recent changes.
2. The "extra processing time" for Event-Driven Synchronization occurs because whenever a new WTPartNumber is added or an old one is deleted, the database will need to reflect those changes in that related WTPARTNUMBERSSI table. And since WTPartMaster.WTPartNumber will have at least 4 Substring Indexes (IndexLength=4), the database will do a lot of work to maintain them. The WTPARTNUMBERSSI table will have rows for each single character string, 2 character string, 3 character, and 4 character. That's why the WTPARTNUMBERSSI table has so many more rows than WTPartNumber does.
3. With Substring Indexes, a user's wildcard search will first use the Substring Indexes, regardless of whether the wildcard is leading or trailing. Then, if the index fails to return any results, the the database will look for a result in the WTPartMaster table itself. This type of query, without Substring Indexes, might take a bit longer, but it will return the results the user is expecting.
4. If the Substring Indexes are out-of-sync, wildcard searches will simply revert back to searching through WTPartMaster.WTPartNumber itself after the Substring Index search fails to return the needed results. The amount of time the search takes can vary depending on what other search criteria the user selects. For example, searching for only certain Object Types is faster than searching for all Object Types, searching within a single Context is faster than searching through All Contexts, and searching with the "Latest Version Search = True" preference set is faster than when it's set to "False".
5. If your customization builds upon Windchill's default search API, it will take advantage of the Substring Indexes. But if it's not, any queries it runs that use wildcards will ignore the existence of that WtPartMasterSSI table. 

[RK_8524786]

@AndrewMichaelAr Thank you so much for your inputs. Also, can you please share If this is implemented by someone you know?

[RK_8524786]

Hi All,

I would really appreciate if anyone can share his/her thoughts regarding challenges faced (If any) after implementing substring Index on Windchill table(column). We are also planning to implement this.

Thanks!

[RK_8524786]

Hi All,

I would really appreciate if anyone can share his/her thoughts regarding challenges faced (If any) after implementing substring Index on Windchill table(column). We are also planning to implement this.

Thanks!