Indexed Properties
In ThingWorx 9.3 and later, properties can be indexed to allow for faster queries. Only properties that do not change frequently should be indexed. For example, model numbers, serial numbers, cities, or regions. When indexed in ThingWorx, property values are stored in the property persistence provider, where they are indexed by the database to allow for faster queries when using the QueryImplementingThingsOptimized and QueryImplementingThingsOptimizedCount APIs.
If a query includes only indexed properties, the query is run solely as a database query.
If a query contains a mix of indexed and non-indexed properties, ThingWorx determines if the query could benefit from performing an indexed query and will do so. In this scenario, ThingWorx first performs the indexed query from the property database for the fields that are indexed. Once that result set is generated, ThingWorx performs a query for the non-indexed properties against the in-memory cache. An example of this might be if you wanted to query all Things located in a particular city (indexed property) with a temperature (telemetry data - non-indexable) above a certain threshold. ThingWorx will first perform the query for city from the database, and then perform the query on the telemetry data (temperature) on the in-memory cache.
If the requested query cannot benefit from indexing, ThingWorx will query against the in-memory cache.
While querying against the database will most often provide superior performance, in some instances not using indexing queries by QueryImplementingThingsOptimized will perform better than using them. One known instance is when the index query returns more than 80% of the total number of Things in the model. For these instances, ThingWorx includes a “hint” to the query to force QueryImplementingThingsOptimized to not use index querying and to only query from the in-memory cache. For additional information, see Hints and Best Practices sections below.
Indexing is supported on PostgreSQL, MS SQL, and Azure SQL databases. If you are using H2, you can model and create your application on H2, but properties will not be indexed and performance benefits will not be seen with the QueryImplementingThingsOptimized and QueryImplementingThingsOptimizedCount APIs.
Creating or updating indexed properties takes longer to persist in the database. Using QueryImplementingThingsOptimizedWithTotalCount service on the indexed property immediately after property updates returns incomplete or unexpected results. Rerun the QueryImplementingThingsOptimizedWithTotalCount service after 2 seconds to get the correct results.
Identifying Properties for Indexing
Consider the following when determining whether to index a property:
• Indexing is limited to the following property base types: STRING, NUMBER, INTEGER, LONG, BOOLEAN, and any base types that are stored as strings.
 |
The following base types are stored as strings: DATETIME, THINGNAME, USERNAME, GROUPNAME, HYPERLINK, IMAGELINK, MASHUPNAME, MENUNAME, DASHBOARDNAME, TEXT, GUID, NOTIFICATIONCONTENTNAME, NOTIFICATIONDEFINITIONNAME, STYLETHEMENAME, and THINGGROUPNAME.
|
• Only properties with values that do not change frequently or at all should be considered for indexing. Properties that change frequently should not be considered. Examples of properties to index include model numbers or serial numbers. For stationary equipment, city, state, or region could also be considered.
|
|
Telemetry data should never be indexed.
|
• Will the property likely be used when calling QueryImplementingThingsOptimized? All indexed properties are also persisted so some thought should be given to whether indexing a property will provide useful benefit. Persisting properties imposes overhead on ThingWorx, so properties that will not be used for QueryImplementingThingsOptimized should not be indexed.
Configuring Indexed Properties
The Index setting is in the properties section for entities. See Thing Properties for more information.
Indexing Byte Limits
Due to index byte length constraints, string properties have the following limitations:
• Strings are limited to 1500 bytes when using MS SQL or Azure SQL as the persistence provider. Strings are stored in MS SQL and Azure SQL as UTF-16.
• Strings are limited to 1000 bytes when using PostgreSQL as the persistence provider. Strings are stored in PostgreSQL as UTF-8.
• String values that exceed the maximum byte length are not added to the index and will not appear in indexed queries. If this occurs, an error is added in the Application log indicating that the value was too large and not indexed. Although the string value will not be indexed, it will be stored and persisted. APIs, such as GetPropetyValues, can still fetch the stored value, and the value will be available after restarts. If you are concerned that a string value might exceed the maximum byte limit, it is possible to create a notification built on the data change event.
Support for Default Properties
The following table lists the properties that are present on all Things and are supported for index querying.
|
Property Name
|
Property Type
|
Supports Indexed Queries?
|
|---|---|---|
|
name
|
STRING
|
yes
|
|
description
|
STRING
|
yes
|
|
tags
|
TAGS
|
yes
|
|
isSystemObject
|
BOOLEAN
|
no
|
|
homeMashup
|
STRING
|
no
|
|
avatar
|
IMAGE
|
no
|
|
projectName
|
STRING
|
no
|
|
thingTemplate
|
STRING
|
yes
|
QueryImplementingThingsOptimized User Input Parameters
The following operations can be executed when running QueryImplementingThingsOptimized.
|
Operation Name
|
Notes
|
Potential Optimization States (if supplied on the request and not null)
|
||
|---|---|---|---|---|
|
ResultDefinition
|
Specified through the basicPropertyNames and propertyNames parameters on the API call.
• basicPropertyNames — a list of basic properties to return.
• propertyNames— a list of built in and implementing entity properties to return.
|
• If all requested property definitions are indexed, QueryImplementingThingsOptimized runs an index query to generate the result set.
• If some requested property definitions are indexed and the operation is supported, QueryImplementingThingsOptimized will run an indexed query against the indexed properties and uses that result set to query the in-memory cache for the non-indexed properties.
• If none of the requested property definitions are indexed or a parameter is NULL, QueryImplementingThingsOptimized queries the in-memory cache.
|
||
|
NameMask
|
A mask-like pattern to match Thing names against.
|
NameMask has no impact on whether QueryImplementingThingsOptimized uses an index query.
|
||
|
NetworkName
|
A network name that a given implementing thing must belong to. Hints can be supplied. For example, you can supply network max depth and parent network names to help narrow down the search.
|
QueryImplementingThingsOptimized will query against the in-memory cache.
|
||
|
Tags
|
A list of tags the entity must be tagged with to be included in the result.
|
QueryImplementingThingsOptimized will use the index query for tags.
|
||
|
Offset
|
The offset to start query, used for pagination. For example, if there are 200 results in the database with an offset of 5, results 5 to 200 (for a total of 195) are returned.
|
Offset has no impact on whether QueryImplementingThingsOptimized uses an index query.
|
||
|
Sort
|
The sort to apply to the final result.
|
• If all properties defined in the sort are indexed, QueryImplementingThingsOptimized will run an index query to generate the result set.
• If some properties in the sort are not indexed, QueryImplementingThingsOptimized will query against the in-memory cache.
|
||
|
Query
|
The filter to apply to the result records.
|
• If all requested property definitions are indexed, QueryImplementingThingsOptimized will run an index query to generate the result set.
• If some requested property definitions are indexed, and the operation is supported, QueryImplementingThingsOptimized will run an indexed query against the indexed properties and use that result set to query the in-memory cache for the non-indexed properties.
• If none of the requested property definitions are indexed, QueryImplementingThingsOptimized will query the in-memory cache.
|
||
|
Limit
|
The maximum number of items to include in the result.
|
Limit has no impact on whether QueryImplementingThingsOptimized uses an index query.
|
Hints
Database index querying can be disabled by including a hint in the query parameter of QueryImplementingThingsOptimized.
|
Hint
|
Required?
|
Default
|
Action
|
|---|---|---|---|
|
optimizationDisabled
|
no
|
Not included
|
• If a query is not specified, hint is not included in query or is false, QueryImplementingThingsOptimized will attempt to query as described above.
|
Example of query that will not use index querying, where TT_1_Boolean1 is an indexed property:
query: {
"optimizationDisabled": true,
"sorts": [
{
"fieldName": "name"
}
],
"filters": {
"type": "EQ",
"fieldName": "TT_1_Boolean1",
"value": true
}
} /* QUERY */ ,
"optimizationDisabled": true,
"sorts": [
{
"fieldName": "name"
}
],
"filters": {
"type": "EQ",
"fieldName": "TT_1_Boolean1",
"value": true
}
} /* QUERY */ ,
Property Basetype Migration
You can change the property base type for existing properties. To support indexed properties, the following migration scenarios exist:
• If the property has a set value, ThingWorx will attempt to convert the property value to the new base type. For example, if converting a string property with value “String123” to an integer, the new integer value will be 123. Conversely, if converting an integer property of 123 to a string, the property will be "123".
• If the property value is not set and the new base type has a defined default value, it will be used for all queries.
• If the property default value is not defined, ThingWorx will use the default value for the new base type. For example, integers and numbers are set to zero (0) and strings are set to an empty string (““).
Examples
The following two tables provide examples of when queries will use index querying. The example model is detailed in the first table and example behaviors of QueryImplementingThingsOptimized is detailed in the second table.
Model
The following model demonstrates the use of indexed properties. The example model uses a Thing Template with two Things based on that Template. This inheritance can also be achieved using a Thing Shape which is implemented by a Thing Template.
|
Entity Name
|
Entity Type
|
Implements
|
Property Name
|
Property Type
|
Property Indexed?
|
|---|---|---|---|---|---|
|
TestThingTemplate1
|
ThingTemplate
|
GenericThing
|
p1
|
INTEGER
|
Yes
|
|
p2
|
STRING
|
Yes
|
|||
|
p3
|
INTEGER
|
No
|
|||
|
p4
|
STRING
|
No
|
|||
|
TestThing1
|
Thing
|
TestThingTemplate1
|
Inherited
|
Inherited
|
Inherited
|
|
TestThing2
|
Thing
|
TestThingTemplate1
|
Inherited
|
Inherited
|
Inherited
|
|
Scenario
|
Example
|
Index Query Used?
|
Comments
|
|---|---|---|---|
|
All operations are supported, and the filter is fully supported.
|
{"sorts":[{"fieldName":"p1"}],"filters":{"type":"And","filters":[{"type":"EQ","fieldName":"p2","value":"12"},
{"type":"EQ","fieldName":"p1","value":"13"}]}} |
Yes
|
Only an indexed property is queried, so this will use an index query.
|
|
All operations are supported, and the filter is partially supported.
|
{"sorts":[{"fieldName":"p1"}],
"filters":{"type":"And","filters":[{"type":"EQ","fieldName":"p4","value":"12"},{"type":"EQ","fieldName":"p1","value":"13"}]}} |
Yes
|
ThingWorx will run an index query for property P1 and then a cache query on that result set for P4.
|
|
All operations are supported, and the filter is not supported.
|
{"sorts":[{"fieldName":"p1"}],"filters":
{"type":"Or","filters":[{"type":"EQ","fieldName":"p4","value":"12"},{"type":"EQ","fieldName":"p1","value":"13"}]}} |
No
|
ThingWorx performs a full cache query because of the OR filter, so an index query provides no benefit.
|
|
All operations are supported, no filter.
|
{"sorts":[{"fieldName":"p1"}]}
|
No
|
There is nothing to filter, so a query is not performed.
|
|
Some operations are supported, and the filter is fully supported.
|
{"sorts":[{"fieldName":"p4"}],"filters":{"type":"And","filters":
[{"type":"EQ","fieldName":"p2","value":"12"},{"type":"EQ","fieldName":"p1","value":"13"}]}} |
Yes
|
All filtered fields are indexed, so this query will use an index query.
|
|
Some operations are supported, and the filter is partially supported.
|
{"sorts":[{"fieldName":"p4"}],"filters":{"type":"And","filters":
[{"type":"EQ","fieldName":"p4","value":"12"},{"type":"EQ","fieldName":"p1","value":"13"}]}} |
Yes
|
This is the same as the example in row 2, with the exception that the SORT is on a non-indexed property.
|
|
Some operations are supported, the filter is not supported.
|
{"sorts":[{"fieldName":"p4"}],"filters":{"type":"Or","filters":[{"type":"EQ","fieldName":"p4","value":"12"},
{"type":"EQ","fieldName":"p1","value":"13"}]}} |
No
|
The filter is the same as the example in row 3, so this query does not support index queries.
|
|
Some operations are supported, a filter is not provided.
|
{"sorts":[{"fieldName":"p4"}]}
|
No
|
There is nothing to filter, so this is not supported.
|
|
Tags are queried, a filter is not provided.
|
Yes
|
Queries on tags are supported for index querying.
|
Best Practices
• Test your application for performance to determine whether query optimizations should be disabled for your particular use case.
• Index queries will be most performant when they return a small subset of the model. A search for a single Thing will provide the most improvement. For example, querying for a single Thing by a serial number will provide the best performance improvement.
◦ If a query returns 80% or more of the model, use a hint to disable index querying.
• Changing property base types is an expensive operation because a change at the Thing Template or Thing Shape level will have to propagate to all implementing entities. On large models, this could take a significant amount of time and resources.
◦ Do not modify property base types while the system is performing ingest.
◦ Ensure that the persistence provider's Max Queue Size is large enough to accommodate the number of persisted property writes that will occur when adding indexed properties or changing base types of an indexed property. The Max Queue Size should be greater than the number of property writes. The number of writes will be equal to the number of properties modified multiplied by the number of Things that implement that property. For example, your model includes a Thing Template that is implemented by 10,000 Things. If you change the base type for two properties on that template, you will generate 20,000 writes (2 properties X 10,000 Things) to the Persistence Property Write Queue. The default size of the Max Queue Size is 100,000 writes, so for this example you will have sufficient resources for the operation.