I added history to a tag so that it may be used on an Easy Chart. I set up the Easy Chart and tested everything. I didn’t receive a quick response from the the query, so I checked the gateway’s Database Connections status. There was one query stuck in execution. I was able to clear it by editing the database connection and saving. I played around with the Easy Chart and found that its query would get ‘stuck’ every time the Easy Chart time frame included a time prior to the tag being historized. For example, I added history to the tag at about 3PM today. When the Easy Chart time window extended before 3PM the query would get stuck and I’d have to clear it. If I let the query continue to run I’ll get an eror pop-up box with a Gateway Error 500 stating the datasource is faulted. This also occurs on Easy Charts for other tags when I extend the time frame back prior to the tag being historized.
Is there a lot of other data that has been collected over time, or is this a fairly new installation? That is, do you have a number of data history partitions? Also, can you paste the query that is blocking here? Finally, what version of Ignition are you using, exactly?
I suspect the problem has to do with the query that is performed for the first value of the result set. Currently, there is a bit of a bug where the system will search back as far as it can- which can be a problem if you have many partitions. On the other hand, when a tag is not present in a table, the query should be very quick, but I think there are many factors that come into play for this.
If this really is the problem, there is a work around- to insert a bad quality value at the start of the partition for the tag(s) in question. I can help you with this, once we confirm that that is the real problem.
Yes, there is a lot of data being logged to the database and this installation has been running for more that a year. The version is: 7.1.9-beta2 (b6361). I’ve attached a screen shot of the Database Connections status page showing the query. Trying this today, it is not generating the error as seen yesterday. Also shown in the screen shot is a mysterious blank query that I’ve seen on other occasions.
Thanks!
Hmm… That’s interesting that you’re using 7.1.x, I seem to recall a problem at some point where these queries could cause a similar problem, but I can’t track it down exactly in the change log. It probably doesn’t matter, because in this case we clearly see that the query is getting executed against the database, and I think that problem was at a stage before it.
At any rate, I do have something that can help: I see, from the query syntax, that you’re using SQL Server. We’ve found that these types of queries can be sped up dramatically by manually tweaking the index type on the table. You might try the following (warning: I don’t have the sql server manager in front of me, so these are somewhat general steps):
Open up the SQL Server Manager
Locate the table “sqlt_data_1_2011_06”
Expand the index folder below it. Locate and edit the tstamp index.
Change its type to “clustered”.
Hit ok, SQL Server will rebuild the index
It might take a few minutes to re-build. During that time the table may not be available. Shouldn’t be a problem for Ignition, the data should buffer in the store and forward system, but you might want to wait for somewhat low-load time.
After doing this, though, you should see much better response times. There might still be a problem when expanding beyond the time the tag was added, but we can handle that next.
