How to Get Property Wise data for logged properties ,

[PP_10477715]

I need  to get Property Wise data for logged properties, I have tried to use QueryNamedPropertyHistory service but seems it is too slow, is there alternatives that may be i didn't tried ?

We are using TWX Version 9.3.5.

[VladimirRosu]

HI @PP_10477715 

Can you please let us know what you mean by "Property Wise data"?

QueryPropertyHistory works, but it is true that it can be slow, depending on the data size it returns (this behavior is similar to the behavior of an SQL query that returns too much data, with some differences). I posted in the past a couple of answers related exactly to this topic.

Can you please also let us know how many rows and columns your QueryNamedPropertyHistory returns in what time, so that we understand what slow means for you?

[PP_10477715]

Hi,

We have logged properties(remote) in a thing and we want to get data stored in value stream for each property, No Of record can be more then 10L (1M) for one property,

What we are doing exactly is:

We are fetching logged properties of a thing and then iterating these logged properties to get count of rows stored for each property, since these are remote properties so it can be updated frequently.

No of rows can be more then 1M and columns are 2 (timestamp,propName).

See the below screenshot for one property.

Thanks,

[VladimirRosu · Accepted solution]

Thanks, that makes it clearer.

So, 1-2 mil rows is definitely a big number. The row count will in any case be inefficient, even if you do it in SQL. In ThingWorx, the row count is executed after ThingWorx retrieves all those rows (as seen in your example) in memory, then it calculates the row count. It will take a bit of time to get the dataset from PostgreSQL, then storing that in-memory is a memory expensive operation, even if you do it for couple of milliseconds to calculate the row count. (Even if you do it in SQL, it will still be slow).

One way to do that is to pre-calculate based on a timer hourly count of properties, then store these in a logged property. Something like: Property1_HourlyCount. The key here is to use an interval as low as possible so that it does not impact the system when used, but at the same time allowing you to execute a count for the remainder of the interval you want also without a system impact. Eg: if you want to get the count from 09:00 to 11:45, you get the count from the Property1_HourlyCount for 09:00-10:00 and 10:00 and 11:00, then the count from 11:00 and 11:45 by executing a normal QueryPropertyHistory, that will execute far faster. Key thing here is that this method can be executed infrequently, since it still incurs a processing penalty.

In which use-case you need this row count, meaning what do you use it for?