Click to graph load times

Currently each trend takes a considerable amount of time to load up, from 8 - 15 seconds. I’ve reduced the amount of data from 4,575,000 to 1,750,000 rows per monthly partition. This looks like it has reduced the load time slightly but no where near enough as I’d like. Are there any other methods/techniques I could use to reduce load times? My customers require hourly/daily/weekly/monthly reports.

Thanks
Gavin

What sort of history is this - how is it being stored? If its not SQLTags Historian, then what do the tables look like? What are the indices on the tables? What do the queries look like? Does the hourly report take the same amount of time as the monthly report? Have you used any database tools (like an EXPLAIN query) to troubleshoot why the query takes so long?

Thanks for your reply Carl. I’m storing the data using the SQL Tags Historian feature. I’m using the CTG trend module and have created 4 buttons which basically change the start date on the CTG_Chart component to an hour, day, week or month before the current time (or the users selected time). I haven’t changed any of the ctg script so the queries can be found within that script.

Since my last post I changed the poll rate within the ctg_chart component from 1 to 100 and changed the settings for the SQL Tags Historian to create a database which is partitioned by week instead of by month.

Overall the hourly and daily trends seem to appear quicker but the weekly trends are still a bit slow and in particular the monthly trends are still very sluggish.

One sure fire way that we could probably help, if it weren’t too much trouble, would be if you could make a database backup of the SQLTags History tables and send it to us, along with the window you’re using.

If you zip it up, you can upload it to inductiveautomation.com/upload

If that won’t work, we can point you toward some loggers to turn on that might give us more information as to which operations are taking time.

Regards,

Can you give us more information. It sounds like a missing index to me, but not sure. We need to know hardware info, and database settings. Also, the db dump would help.

I’ve uploaded the files as suggested, additionaly I will PM you with the address of the live system along with username and password details. Thanks for you help!