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

Community Tip - Your Friends List is a way to easily have access to the community members that you interact with the most! X

Data Table and Stream Query APIs Pitfalls

100% helpful (2/2)

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.

Version history
Last update:
‎Feb 21, 2024 06:21 AM
Updated by:
Labels (1)
Contributors
Tags (1)