Change the default query criteria - HxGN SDx - Update 63 - Administration & Configuration

Administration and Configuration of HxGN SDx

Language
English
Product
HxGN SDx
Search by Category
Administration & Configuration
SmartPlant Foundation / SDx Version
10

The default query criteria for a new entry point is a ‘*’ wildcard. If you do not change this value in the search box, any query returns all matching data of the selected type, paginated according to the configured page size. This is unlikely to return the results you want, so your next action is likely to refine the query, or filter the results. To prevent system resources from being consumed by superfluous queries that can affect other users, aim to get to your specific data with the fewest clicks and queries..

The default criteria can be modified in the following ways, against individual entry points.

Set the default criteria to ‘Empty’ for a specific entry point

This is designed to discourage ‘*’ queries and encourage targeted queries. This is configured on the entry point as follows:

EPO1

The Default Search Criteria is deliberately empty, and the empty criteria is applied to the entry point. This will result in removing all default criteria from the search control for this entry point.

EPO2

Set the pre-populated default criteria to a value appropriate to a specific Entry Point

This will not only discourage wide queries, but also pre-populate potentially common prefixes.

EPO4

This will pre-populate the search control when the modified Entry Point is selected.

Where ENS is used for the data type, the configured prefix may be a good starting point. Where ENS is not used, analysis of the data distribution for the specific data type may identify a large proportion of the objects of this type that share, for example, common sets of characters. Configuring an appropriate default search string can both guide you (and save keystrokes) and prevent initial wide queries that you need to narrow down later.

SQL queries, such as the following, can help identify common data patterns that you can use to create appropriate default search text:

select distinct(n), count(*) as cnt from (select substring (objname,1,2) as n from docobj) d group by n order by cnt desc

Peek tuning

‘Peek’ queries are made to the database as you enter criteria in the search box and return any matching data, prior to running the full query. The first four rows of matching data are displayed as follows:

EPO5

This is a popular feature that users expect in a modern web application. When querying a large data set, you can tune the behavior for optimization purposes.

Criteria entry debounce

Queries to support the Peek query functionality are made, by default, 300ms after you type a character in the search control. This allows you to make rapid keystrokes, such as two consecutive characters typed within the 300ms threshold, while issuing only one query. However, if the default 300ms threshold is too fast and still results in too many queries, the SPF Web Client peek debounce duration value can be increased in the Web Client Site in the Server Manager. This setting applies to all Entry Points (site-wide setting), and means that slower data entry will not result in as many intermediate queries.

The maximum debounce duration is 1 second (1000ms), meaning that a query will not be made if you type another character within 1 second of the previous one. In other words, 1 second has to elapse after a keystroke before a peek query is issued.

On large data sets, it is important to minimize the quantities of peek queries being issued since every query is processed by the server, even if its results are no longer required by the Web Client (for example, if you have entered more criteria in the meantime). Running these ‘cancelled’ queries would result in unnecessarily loading the database server by running unneeded queries and consuming resources that could impact other concurrent users.

Peek invocation character threshold

Peek queries are made, by default, after you enter the third character into the search box. For some data types with longer prefixes, it may make more sense to delay the initial Peek query until you have entered more. The character threshold at which Peek will first be invoked is configurable for each Entry Point.

EPO6

If no value is entered, the default of three characters will be applied. The quantity of characters specified must be larger than three (we do not allow Peek after 1 or 2 characters) and less than 10 (since peeking beyond this point is considered unhelpful).

Having a higher threshold means that fewer Peek queries are issued, and data suggestions are only requested when you have entered something meaningful to the data type.

Disable Peek

If Peek is too disruptive for a specific data type, you can turn it off completely, for each Entry Point, by selecting the Disable peek check box.

Query properties

You can use the column set for each Entry Point to control the available or default properties to query by. These can be reviewed and fine-tuned in the following ways.

Review / reduce the default selected query properties

The available query properties are displayed in the query control as follows:

EPO8

You can choose which properties to include in the criteria, depending on the nature of their query. Default selections can be configured by an administrator so that users can avoid setting the same query properties each time they run a search. However, this should be used economically to avoid queries that span properties, and potentially database tables, resulting in queries that are more complex than necessary. Where several properties are selected by default, you should ensure that only the minimum set is defaulted, and evaluated with the following criteria:

  • Any selected properties in the query criteria will use OR (for example, “Name = ‘ABC’ OR Description = ‘ABC’). The Data Access Layer (DAL) is able to optimize AND queries by not repeating any navigations. However, any navigations for OR queries will be repeated, resulting in larger, and therefore less efficient, SQL.

  • Criteria should be grouped to the same object where possible. For example, if using a Documents Entry Point to query against Name, Description, and Contractor Document Number, it is more efficient to move the Name and Description properties to the same level as Contractor Document Number in the corresponding column set.

    • The DAL has AND criteria from the Entry Point and security rules for all parts of the document already. In this case, it can join into that criteria another AND, for example:

    AND (Name = ‘x’ OR Description = ‘x’ OR ContractorDocNo = ’x’)

    This is simpler than:

    AND (Name = ‘x’ OR Description = ‘x’ OR <Navigation from version to revision>.ContractorDocNo = ‘x’)

    • This is improved further by the fact that these properties are on the XXXOBJ table so will not require joins to the XXXOBJPR table.

  • Any potential configuration changes must be run with SQL capture switched on so that the resultant SQL can be evaluated. Generally, smaller SQL is more efficient than larger SQL.

    • We advise that you check for repeated references to the same RelDef, in case configuration changes can be made to avoid it.

  • Default search properties are configured on the column set that corresponds to the Entry Point:

    Where the defaults have not been modified, Name will always be selected by default.

Remove expensive columns from the available list

Certain column items may be valid for the Entry Point’s column set, in that they need to be part of the results, but they may be inappropriate for querying against. Typically this would be relationship or edge-based column items. In this case, to prevent users selecting an expensive column item to be part of the query criteria, individual column items can be configured to be removed from the criteria options, yet still be part of the data set that is returned. This is also configured on the column set that corresponds to the Entry Point:

Identify the column item to suppress, and choose the ExcludeFromFilters option in the Web Client Filter Behavior column in the Manage Column Items dialog box.

Ensure column items are contextualized properly and efficiently

For documents in particular, column items must be contextualized properly and efficiently. For example, column items for the column set that corresponds to a Documents Entry Point must be contextualized to the Version. It is inefficient to retrieve the Name, for example, from the Master since it involves navigating an EdgeDef unnecessarily. This is controlled from the path on the column item in the column set that corresponds to the Entry Point. The guidance in the Review / reduce the default selected query properties topic applies here too.

With document column sets, navigations are hard to avoid, since it is likely that properties from different parts of the complex object will be required in the result set. However, queries that use properties from these different parts will be less efficient than those that query the same part of the complex object. You should consider this when selecting available and default filter properties.

Default operator

By default, all searches from the search box are sent using the Contains operator, meaning that implicit wildcards are added to the search. For example, a criteria of ‘CDE’ using Contains would return data named ‘ABCDEF’ since the criteria is contained within the matching data.

You can choose to use the Is equal to operator instead, meaning that wildcards have to be manually specified. This is useful when looking for data that begins or ends with a given string, as opposed to being contained anywhere within it. It is also useful for optimizing queries on large data sets where Contains is not needed.

You can change from Contains to Is equal to on individual Entry Points by selecting the Use Equal To Operator By Default check box.

Other optimizations

Review complexity of property comparisons

Some Entry Points use property comparisons to focus the data that is returned. These can be reviewed to make sure that any extraneous comparisons, particularly those involving a relationship expansion, are removed:

EPO9

Redirect to search bar

This can help to prevent open queries from the home page. Entry Points can be exposed on the home page as tiles within a feature set. These can perform direct ‘single-click’ queries to return all matching data, with a * wildcard.

EPO10

To prevent or discourage wide open searches, and help you get to your required data quicker, you can modify the Entry Point to redirect users to the search bar, rather than immediately performing a wide open query. This can be configured for each Entry Point by selecting the Redirect To Search Bar check box.

This results in a change to the icon on the home page tile:

EPO11

Column Set Sorting

Column sets can be configured to have a default sort order so that returned data is sorted in ascending or descending order by a selected column.

Sorting by columns, which requires a navigation, should be avoided if possible, as this can lead to less efficient queries.