Query Statistics
Via the Utilization Subsystem, the ThingWorx platform provides a statistics namespace for query related operations at run time. The root namespace for all query-related statistics are prefixed with twx.stats.query. Statistics are gathered for a specific query operation handled by persistence providers known as Model and Indexed Property Search Provider (MIPSP) operations. If a persistence provider offers the MIPSP operations, ThingWorx will attempt to optimize the search based on the query parameter input to the search operation using a combination of database and cache filtering strategies. Postgres, MSSQL, and Azure SQL persistence providers provide this type of search capability.
Enabling Query Statistics
Query statistics are enabled in the Utilization subsystem. See Utilization Subsystem for more information.
Managing Query Statistics
Upon execution of a QueryImplementingThingsOptimized service, the query metrics collection is managed by using a set of services on the Utilization subsystem. These services are available in Composer, but are not currently exposed to the Extension Java SDK.
|
Details
|
|
|---|---|
|
Description
|
Returns all query operation-related statistics. The results are provided as an infotable of the UtilizationStatistics data shape. Provides filtering to select a subset of query operation statistics. For example, to retrieve the MIPSP query operation statistics, you can use the GetStatistics service and specify the twx.stats.query.mipsp namespace.  |
|
Parameters
|
• maxItems
◦ Maximum number of items to return
◦ NUMBER base type
• sortColumnName
◦ The name of the infotable result column field to sort on
◦ STRING base type
◦ Default value is count
• sortAscending
◦ The order to sort by: ascending or descending
◦ BOOLEAN base type
◦ Default value is false
|
|
Example Result
|
See Interpreting Query Statistics below for more information.  |
|
Details
|
|
|---|---|
|
Description
|
Writes the query statistics.
|
|
Parameters
|
• fileType
◦ The file type to report to CSV
◦ STRING base type
◦ Default value is csv
◦ Acceptable value is csv
• maxItems
◦ Maximum number of items to return
◦ NUMBER base type
• sortColumnName
◦ The name of the infotable result column field to sort on
◦ STRING base type
◦ Default value is count
• sortAscending
◦ The order to sort by
◦ BOOLEAN base type
◦ Default is false
|
|
Example Result
|
 |
|
Details
|
|
|---|---|
|
Description
|
Resets query statistics.
|
|
Parameters
|
N/A
|
|
Result
|
If successful, no result is provided.
|
Interpreting Query Statistics
If you perform a search operation, such as through a Thing Template or Thing Shape’s QueryImplementingThingsOptimized service, a MIPSP operation is performed. As part of that operation, statistics are captured under a sub-node of the root query statistics namespace called mipsp.
Any statistic prefixed with twx.stats.query.mipsp indicates that metric is collected during MIPSP operations.
The MIPSP query statistics provide the following sub-nodes of statistics when executing the QueryImplementingThingsOptimized service. Each of the statistics provided by this “name key sub-nodes” provide the min, max, mean timings as well as the count, which over several invocations of the named functionality, resolve to the values provided in those columns. As additional invocations occur, each statistics' “name key” is updated to reflect new values on requests to the GetQueryStatistics service.
|
Sub-node
|
Parent-node
|
Full Path
|
Details
|
|---|---|---|---|
|
supportdetermination
|
mipsp
|
twx.stats.query.mipsp.supportdetermination
|
Provides stats for determining how to optimize and support the query based on the parameter provided.
For example, if a namemask parameter is provided as an input parameter to a query, then this supportdetermination functionality will determine if it can support the namemask parameter. Results of the supportdetermination for the namemask will provide input for the platform to decide on how to optimize the query requested.
The stats will indicate how long it took and the number of times supportdetermination functionality was executed across various permutations of the query parameter input that passes through this functionality.
|
|
interrogate
|
mipsp
|
twx.stats.query.mipsp.interrogate
|
Provides stats that ask a persistence provider what type of search functionality it provides.
For example, if a namemask parameter is provided, the persistence provider will interrogate whether it supports that search functionality.
|
|
searchthingnames
|
db
|
twx.stats.query.mipsp.db.searchthingnames
|
Provides stats for any operation that has been determined to use the database to search for Thing names that meet the search parameters to optimize the search.
|
|
searchthingnames.buildquery
|
searchthingnames
|
twx.stats.query.mipsp.db.searchthingnames.buildquery
|
Provides stats for building the SQL query necessary to perform a database search for Thing names.
The min, max, mean times and counts for this metric are specific and unique stats to the buildquery functionality of the searchthingnames operation. However, the stats for the searchthingnames provided by twx.stats.query.mipsp.db.searchthingnames include the stats for the buildquery stats such that:
Where A = “twx.stats.query.mipsp.db.searchthingnames”
and B = “twx.stats.query.mipsp.db.searchthingnames.buildquery“.
Then A - B = C where C is the count/timings that do not involve the buildquery part of searchthingnames. C involves the count and timings for executing the query against a database but not the building of a SQL query that is performed against the database during searchthingnames operation.
|
|
getpropertyvtqcount
|
db
|
twx.stats.query.mipsp.db.getpropertyvtqcount
|
Provides statistic metrics, parent metrics, and aggregated metrics for the sub statistics of thebuildquery and buildfilter parameters. The stats count and the timings include the values of timings and counts for any of its sub stats.
|
|
getpropertyvtqcount.buildquery
|
getpropertyvtqcount
|
twx.stats.query.mipsp.db.getpropertyvtqcount.buildquery
|
This stat is similar to twx.stats.query.mipsp.db.searchthingnames but for the getpropertyvtqcount service on the UtilizationSubsystem rather than for a searchthingnames operation.
The GetPropertyVTQCount service is used by the phone home feature within the ThingWorx platform. For every reporting period, it invokes this API to retrieve stats to report within the phone home metrics. You may see two or more counts even after a reset if the phone home period has caused a collection of metrics to report home within the time since the metrics for twx.stats.query have been reset.
|
|
getpropertyvtqcount.buildFilters
|
getpropertyvtqcount
|
twx.stats.query.mipsp.db.getpropertyvtqcount.buildfilters
|
Statistics collected for processing the query input of filters and filter collections into an internal construct to build a SQL query to retrieve property_vtq count data. These minimum and maximum time values include building a simple query filter through complex nested filter collection queries, respectively.
|