Slow system.tag.queryTagHistory results from partitioned tag table

Ignition
1

I'm pulling back a full day of tag data at a time for a particular graph. When I run this for a day in the current month, it returns within a second. But - when I run it for a day earlier than the start of this month, it can take up to 12 seconds to return.

Our tag history database (SQLServer) is partitioned by month, with no pre-processing. The table size for June is 14GB, with 9.5GB of index. (For this month so far its only 4 and 2.7). I suspect this may have something to do with the partitioning, as that's the only thing that changes between the two cases.

Has anyone else run into this? Or has thoughts on what to do?

data = system.tag.queryTagHistory(
			paths=[temperature_tag, humidity_tag], 
			startDate=start_dt, 
			endDate=end_dt, 
			intervalMinutes=1, 
			includeBoundingValues=True, 
			noInterpolation=False,
			ignoreBadQuality=True,
			aggregationMode='Average', 
			returnFormat='Wide',
			columnNames=['temperature','humidity'],
		)
2

At first I was going to say 1 minute interval for 1 month of data is alot of data...but, you are only switching from 9 days to 10 days of data and seeing the major increase in processing time. It must be the database index. 12 seconds is insanely slow though...is that database server using spinning drives instead of SSD? Have you tried just querying the same 10 days but in June only...then test another month like May? If you see the increase only when querying across the partition, then for sure it is the indexing. Or...go query the database itself for these tags...see how many instances of the tags exist...(i.e. does the tag have multiple expired versions in the sqlth_te table). This happened to me one time when the datatype of the tag was switching from integer to float (I was entering tag history values via scripting and the value was not forced to a float type...the system would expire the old tag and recreate an entry of type integer when the value happened to be a whole number...you get the idea)

3

It's just one day at a time (eg, the 6th of June or the 6th of July). And the same tags both times. And I checked the tags, only one instance of the first tag, and two of the second.

But! That did get me to test each tag individually, and it turns out that calling system.tag.queryTagHistory with two tags runs much, much slower than calling it twice with one tag each time (but only for that earlier time period).

No idea why this would be, doesn't really make much sense. But gives me a way to work around the problem for now anyway, so thanks very much! :slight_smile: