Using the QueryDataTableEntries and QueryStreamEntriesWithData Service
The QueryDataTableEntries service queries the data tables and returns an infotable of records that match the request parameters. The use of non-optimized data queries in ThingWorx can lead to performance issue and even system outage.
To understand how this service works, see the example scenarios below.
Example Scenarios
Scenario 1: The query filter is always applied in-memory on ThingWorx Platform, it is never used by the SQL statement.
var query = {
"filters": {
"type": "EQ",
"fieldName": "firstname",
"value": "Doe"
}
};
var result = Things["myDataTable"].QueryDataTableEntries({query: query})
The code above is fetching the entire Data Table from the database, the filter is then applied in-memory on ThingWorx Platform.
This scenario applies to: QueryDataTableEntries, QueryStreamData, QueryStreamEntries, QueryStreamEntriesWithData
Alternative scenarios: Data Table using the values parameters on FindDataTableEntries and QueryDataTableEntries
Scenario 2 : maxItems parameter on QueryDataTableEntries service is applied in-memory on the ThingWorx Platform, it is not used by the SQL statement
var result = Things["myDataTable"].QueryDataTableEntries({maxItems: 1 });
The code above is fetching the entire Data Table from the database, the limit is then applied in-memory on ThingWorx Platform.
The behavior is different for Stream Query API. For more information, see Scenario 3 below.
Scenario 3 : maxItems on Stream query services is applied in-memory onThingWorx Platform when used in conjunction with the query filter (always in-memory for Data Tables, see Scenario 2)
var query = {...};
var result = Things["myStream"].QueryStreamEntriesWithData({maxItems: 1, query: query});)
The code above is fetching the entire stream from the database.
var result = Things["myStream"].QueryStreamEntriesWithData({maxItems: 1, source: "myThing"});
The code above is fetching only one record from the database since the query parameter is not used.