Ok, after some digging, I found the issue.
Apparently, it reads and writes to that table when a scan class doesn’t get updates soon enough.
We could count the number of records per scan class, and saw that most records belonged to the “fast” scan class.
When searching the sqlth_scinfo for tags with the “fast” scan class, we found a few tags that used the scan class as historian scan class. Those appeared to be the problem, as the scan class was fast, it was stale a lot sooner, and caused a lot more new records and updates.
The tags are communication bits, so they don’t change that often, but I understand some people want a quite precise timestamp on when they changed. Those tags are now redefined to evaluate on change, hopefully that’s better.
That said, the sqlth_sce table can use a better design IMO, there’s no clustered index or primary key on the table, so I guess that’s why the select and update queries took longer than expected. I guess the combination of
start_time can be used as a primary key?