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

Community Tip - Learn all about the Community Ranking System, a fun gamification element of the PTC Community. X

Translate the entire conversation x

Calling one big QueryNamedPropertyHistory versus many smaller calls to get the exact data I want

JK_10744682
10-Marble

Calling one big QueryNamedPropertyHistory versus many smaller calls to get the exact data I want

Hi all,

 

I have a case where I want to query a lot of different property histories, do some calculations from the history, and create a report from it.

 

For my specific case, it simplifies the process of creating a clean report if I just loop through the list of properties and query each one individually (typically like 30 or less properties total so nothing crazy).

 

I am curious about whether doing this and calling several queries will cause any performance issues or database access problems. I tried doing it all in one query, but it is much simpler to get the data specific to each property and then perform my calculations in individual queries.

 

The speed of my service is fine, so really just wondering if there are possible issues I haven't considered with this approach or if this is okay practice.

 

Thanks!

ACCEPTED SOLUTION

Accepted Solutions

There is some "it depends" in this. For example, how much data you get in the queries, how often you run the report, your persistence provider, and so on.

I suppose you have the  value streams under control, i.e. you move out old data and manage the size so they don't grow endlessly.

 

You say there are currently no performance issues, so that's great. Question is will it keep being so in future. Are you expecting more users to use ThingWorx (or the report)? Are you planning to store data for more machines, properties or for longer? These are questions to ask if you consider the scalability of your approach.

From an architectural standpoint you create load in two systems, in the DB and in TWX.

The upside of one big QNPH is that the DB would only find and get the rows once, so in general I would expect this to be a tad faster than running n queries. The downside though is that you have to move a lot more data in a single action over to TWX, it will need a lot of memory at once, and the individual transaction length will be longer. This could come bite you later, when you might run into query (or service execution) timeouts.

Individual queries though will (per query) request less data, transactions will be smaller and less memory is used since you sliced the dragon and now you handle the slices sequentially, allowing garbage collection to free allocated memory if needed. The downside is that the database has to re-get the data every time. There might be some caching, but there is some base cost you have to pay over and over again.

 

30 QNPH calls to get all your properties is a lot, so I see why this got you thinking. I am leaning towards saying it is the more scalable approach though, but it's really a question on how much rows you request, how often this is done, and how much is filtered away.

 

Make sure you use the start and end date properties, eventually the tags in your query as this will enable the query to use the DB indexes, making the queries faster. Remember filter queries are NOT part of the where clause but are executed on the client, i.e. on your TWX.

 

(Caveat: I had SQL databases in mind when i wrote this, not sure how well this translates to Influx)

 

If you have doubts about  the overall load, and past data does not change, you could think about storing the pre-computed data, so the next report will consume less resources. There are several possible approaches but they would be specific to your use case.

 

View solution in original post

3 REPLIES 3

There is some "it depends" in this. For example, how much data you get in the queries, how often you run the report, your persistence provider, and so on.

I suppose you have the  value streams under control, i.e. you move out old data and manage the size so they don't grow endlessly.

 

You say there are currently no performance issues, so that's great. Question is will it keep being so in future. Are you expecting more users to use ThingWorx (or the report)? Are you planning to store data for more machines, properties or for longer? These are questions to ask if you consider the scalability of your approach.

From an architectural standpoint you create load in two systems, in the DB and in TWX.

The upside of one big QNPH is that the DB would only find and get the rows once, so in general I would expect this to be a tad faster than running n queries. The downside though is that you have to move a lot more data in a single action over to TWX, it will need a lot of memory at once, and the individual transaction length will be longer. This could come bite you later, when you might run into query (or service execution) timeouts.

Individual queries though will (per query) request less data, transactions will be smaller and less memory is used since you sliced the dragon and now you handle the slices sequentially, allowing garbage collection to free allocated memory if needed. The downside is that the database has to re-get the data every time. There might be some caching, but there is some base cost you have to pay over and over again.

 

30 QNPH calls to get all your properties is a lot, so I see why this got you thinking. I am leaning towards saying it is the more scalable approach though, but it's really a question on how much rows you request, how often this is done, and how much is filtered away.

 

Make sure you use the start and end date properties, eventually the tags in your query as this will enable the query to use the DB indexes, making the queries faster. Remember filter queries are NOT part of the where clause but are executed on the client, i.e. on your TWX.

 

(Caveat: I had SQL databases in mind when i wrote this, not sure how well this translates to Influx)

 

If you have doubts about  the overall load, and past data does not change, you could think about storing the pre-computed data, so the next report will consume less resources. There are several possible approaches but they would be specific to your use case.

 

Appreciate the detailed response, this gives me a good amount to think about.

 

A few other points which may help skew things based on the questions you asked:

 

This service is called in a subscription. The subscription is called around every 3-4ish minutes (only when a line is running) whenever a new part has passed through the machine. Currently only used for a few machines, looks like only a few more will be added later on. 

 

Each query gets specifically all the property history between the 3-4ish minute timestamp range, so not a crazy number of values.

 

Don't want to overengineer, but maybe track ratio of service execution times vs subscription intervals. Things change over time, and you wouldn't want to run into the subscription executions piling up because they're spawned faster than they can be finished.

You will find some good articles about subscriptions and timers in the community.

Announcements


Top Tags