The use of non-optimized Data queries in ThingWorx can lead to performance issue and even system outage (OutOfMemory for example).
I'm sharing some observations that I have collected while reviewing customers code in the context of RCAs.
This was initially tested in ThingWorx 8.4.5 using PostgreSQL and MSSQL Persistence Providers, but it is still applicable to 9.0.2.
Observation 1 : the query filter is always applied in-memory on the 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 DB, the filter is then applied in-memory on the platform.
Applies to : QueryDataTableEntries, QueryStreamData, QueryStreamEntries, QueryStreamEntriesWithData
Alternative : (data table) use the values param on FindDataTableEntries and QueryDataTableEntries
Observation 2 : maxItems on QueryDataTableEntries is applied in-memory on the 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 DB, the limit is then applied in-memory on the platform.
Behavior is different for Stream Query APIs, see Observation 3 below.
Observation 3 : maxItems on Stream query services is applied in-memory on the platform when used in conjunction with the query filter (always in-memory for Data Tables, see Observation 2)
var query = {...};
var result = Things["myStream"].QueryStreamEntriesWithData({maxItems: 1, query: query});
The code above is fetching the entire stream from the DB
var result = Things["myStream"].QueryStreamEntriesWithData({maxItems: 1, source: "myThing"});
The code above is fetching only one record from the DB since the query param is not used.
More to come :
- Use of Data Table configurable indexes ...
