Calculate Duration On of multiple tags

Ignition
, ,
1

Hello,
I want to display for a given time slot for mutilple tags (more than 2000) the start, end and duration each time the tags were on.

Currently I go through the tagPaths, query the history for the given time slot, go though the history and calculate the duration on each time the tag was true.

My problem: the calculation takes too much time (actually 18 sec) and I want to reduce this to max 5sec.

Does someone have an idea on how I could approach the problem differently?

2

The historian doesn't do a good job of this. There is no interpolation for the time period edges and if your tag doesn't change in the interval in question it may not appear in the query results at all!

You should create a table for this application with fields t_stamp, tagPath, value, quality (and anything else you might be interested in) and log these on change and on schedule to suit your report. (If you want to report by the hour then make sure you have a reading on the hour, etc.) Index the table on the t_stamp column and on the tagPath for speedy lookup.

To do it properly you would create a separate table containing id, tagPath, status (where status is active / retired) and store the id in the first table rather than the tagPath. The integer id will be faster.

3

We wanted to spare ressources because we do not only have those 2000 tags, but about 2000 tags for each plant and we have more than 10 plants.

4

Then it's definitely worth doing properly.
What resources are you worried about?

5

The resources of the sql server

6

You're going to have to store the data somewhere - in Historian or in your own table - so that will make very little difference to storage requirements.

A properly set up database for your application will reduce server load and be super-responsive. That will reduce server load.

7

Once you have that set up, the queries will use the lead() or lag() functions to get adjacent timestamps for deltas. In your case, you will need to use the PARTITION BY clause in your window functions so that the calculations are per-tagpath.

(I wouldn't store tagpaths directly in this table--make a joinable table that holds the full tag path and use that table's primary key in the event table.)

See this topic for an example:

(You would partition by event_tag_id instead of ACTOR.)

8

So there is no way to get this done faster without using a sql table.
Okay thanks

9

Well, the historian uses SQL tables, too. But the historian doesn't construct its queries to perform the calculations you need using standard SQL optimizations, where it can be done dramatically more efficiently than anywhere else. Because the historian's architecture is optimized for recording of numeric process values, not events.

2 Likes
10

Before you start changing things, perhaps post your code? Maybe what you currently have can be optimized somehow.

11

Here is the code:

start=event.source.parent.getComponent('Cal_Start').date
end=event.source.parent.getComponent('Cal_End').date
folderPath = event.source.parent.dataPath #folder to the tags
folderArchiv = folderPath + "/alarmData/alarmArchiv"     #folder containing the dataset where I store the data to be displayed in a powerTable 
modifiedHistoryDataset = system.dataset.toDataSet(["ID","description", "Start", "End", "Duration"], [])
		
tagPaths=system.tag.readBlocking(folderPath +"/alarmData/alarmList")[0].value # All Tagpaths				
for x in range(tagPaths.rowCount):   # Go through Tagpath
	identification = tagPaths.getValueAt(x,0) 
	tagPath = tagPaths.getValueAt(x,2) 
	history = system.tag.queryTagHistory([tagPath],start,end)
	description =tagPaths.getValueAt(x,1) 	
	timestamps = [history.getValueAt(i, 0) for i in range(history.rowCount)]
	tagStates = [history.getValueAt(i, 1) for i in range(history.rowCount)]
	startTimestamp = None
	endTimestamp=None
	for i in range(len(timestamps)): 
		timestamp = timestamps[i]
		tagState = tagStates[i]
		if tagState == 1:  # Check if the tag value is 1
			if startTimestamp is None:
				startTimestamp = timestamp
			if i == len(timestamps) - 1: # If it's the last timestamp and the tag is still 1, calculate duration until the current time
			    endTimestamp = system.date.now()
	   	else:
			if startTimestamp is not None:
			    endTimestamp = timestamp
		if startTimestamp is not None and endTimestamp is not None:
			formatted_duration = shared.Func_Dataset.calculate_duration(startTimestamp, endTimestamp)
			startFormatted = system.date.format(startTimestamp, "dd.MM.yyyy HH:mm:ss")
			endFormatted = system.date.format(endTimestamp, "dd.MM.yyyy HH:mm:ss")
			modifiedHistoryDataset = system.dataset.addRow(modifiedHistoryDataset, [identification, description, startFormatted, endFormatted, formatted_duration])
			startTimestamp = None  # Reset the start timestamp
			endTimestamp = None  # Reset the end timestamp
modifiedHistoryDataset=system.dataset.sort(modifiedHistoryDataset, 2, False)	#sort descending 	        
system.tag.writeBlocking(folderArchiv, modifiedHistoryDataset) # write back to dataset
12

Are you trying to get duration for alarms?

13

Yes, but using ignition alarm table is not an option