cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Note about Data Tables and Streams Query APIs

Highlighted
Pearl

Note about Data Tables and Streams Query APIs

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 3maxItems 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 ...
Announcements