TWX Service timesout when querying influxDB for the moving average and for the standard deviation

[Janicen]

Hi PTC,

In my thingworx service am trying to query influxDB to get the moving average and the standard deviation of one or more properties.

Below is my code: 

//AggregateFunctions_Inputs is an array of inputs

AggregateFunctions_Inputs.forEach(functionName => {
if(functionName === "stddev"){//stddev
fluxQuery += 'from(bucket: "thingworx")' +
'|> range(start: ' + start + ', stop: ' + end + ')' +
'|> filter(fn: (r) => ' + Measurements_Inputs.map(m => 'r["_measurement"] == "' + m + '"').join(' or ') + ')' +
'|> filter(fn: (r) => ' + Fields.map(f => 'r["_field"] == "' + f + '"').join(' or ') + ')' ;
}else if (functionName === "movingAverage"){//movingAverage
fluxQuery = 'from(bucket: "thingworx")'+
'|> range(start: '+start+')' +
'|> filter(fn: (r) => ' + Measurements_Inputs.map(m => 'r["_measurement"] == "' + m + '"').join(' or ') + ')' +
'|> filter(fn: (r) => ' + Fields.map(f => 'r["_field"] == "' + f + '"').join(' or ') + ')' +
'|> movingAverage(n: '+windowSize+')'+
'|> yield(name: "movingAverage")';
}else {
fluxQuery += 'from(bucket: "thingworx")' +
'|> range(start: ' + start + ', stop: ' + end + ')' +
'|> filter(fn: (r) => ' + Measurements_Inputs.map(m => 'r["_measurement"] == "' + m + '"').join(' or ') + ')' +
'|> filter(fn: (r) => ' + Fields.map(f => 'r["_field"] == "' + f + '"').join(' or ') + ')' ;
}
});
var headers = {
"Content-Type": "application/....",
"Authorization": myToken
};
let params = {
headers: headers,
url:myURL
content: fluxQuery,
method: 'POST'
};
let jresult = Resources["ContentLoaderFunctions"].PostText(params);

The issue I am facing is that the service times out. However if I were to structure my query for functions like the min, max or mean, then my service does not time out. Could the reason for this perhaps be tied to the schema of the DB. For testing purposes I only want to see 30 min - 2 hours worth of data. Once I have it working, I would like to see a month - 2 months worth of a data.

[Rocko]

Without looking into the code, wouldn't min/max/mean be just one value, while moving average would return a full table of values for the given internal? Then much  more values would have to be computed and transferred, hence the longer run-time.

BTW: When you say 30min-2hours, or 2 months of data, it doesn't mean anything if you don't also give the resolution of the data. It makes a difference if you have one record per minute or per 100msec.

For debugging, I would recommend running your queries not via thingworx, but directly against Influx. Also choose smaller time intervals to see if you got the query right. I think is more an Influx than a Thingworx question.

[cmorfin]

You may also be able to increase the timeout if you are happy to wait.

Depending on the version of ThingWorx nd Influx you are using there are some time out parameters in the Configuration of the Influx Persistence Provider, see Downloading and Installing Inflxu DB 2.x (ptc.com) .
Possibly ScriptTimeout can also be increased platform-settings.json Configuration Details (ptc.com) .

[Janicen · Accepted solution]

Thank you @cmorfin  for the suggestion.

I managed to solve the timeout issue on without increasing its value in the settings.

I did so by concatenating the json result of each query. I.e. let Combined_json_result = movingAverage_result.concat(stddev_result, basicAggregates_result);

This seems to fix the time out issue as I can now see the results without any timeout constraints.

[Janicen]

HI @Rocko 

Thanks for the suggestion. 

- I Have a resolution as input to the service.

- The min, max, and mean are not the same as they are calculated over a period of time (time based) and for a set of values whose length changes as the time goes by.

However, I will try to test the queries directly on influx.

Many thanks.