Factorytalk Datalogging

I have a client that is using an ODBC database to log a number of tags (110) every minute.

This data is being displayed on a graph in the Client. The user has the option to choose which tags are shown and usually only 3 tags are displayed at a time.

I am trying to add the option of displaying data for 1 hour, 1 day, 1 week and 1 month. The 1 hour works fine, the 1 day takes a minute to load up, the 1 week takes a couple of days to load up and the 1 month just doesn't seem to load up.

Whilst waiting to load data a "loading historical data" popup is displayed.

I am trying to solve the issue of these long (impossible) waiting times without changing the frequency of the log. I have tried removing data that is older than 1 month.

The file is currently 250mb in size.

I appreciate any comments.

Patrick

Edit:
I am using ODBC on MS access database to log the tags.

Have you tried switching to at least SQL Express if not SQL? Access is almost fine as an address book, maybe to manage recipies, but it isn't a database that can handle high throughput.

I should say that I'm not a Rockwell expert so I'm not sure how this is implemented at the SCADA end.

One of the problems using access is that when you query it, it retuns the entire database and then filters out the data required. If you use SQL Server/Express then you can run a stored procedure to return a recordset that you really wanted.

Recently I've been using Factory Talk Transaction Manager to log data to SQL and then used a stored procedure to return data of interrest to a spreadsheet for analisys. I'd be interrested know how you are displaying your data.

Nick

I tried setting it up in SQL and it worked for the first couple of minutes (logging every second to speed up the process).

It then started taking long to update the trend, same as MS Access database was.

I feel that using a trend will still query the entire database, anyway I can have it query just the data I require by using VBA buttons to display onto a trend?

Would using a fileset help with loading times?

Switching to SQL server (of any flavor) will not be much better than using MSaccess database unless you set up indexes in your tables. If you are attempting to select records that fall between two dates, an index on the date field will allow the SQL server to gather those records that meet your criteria and pass them to you much better than without the index.

Put the query you are using in SQL into the SQL tuning advisor and then figure out what Indexes are required. Apply these indexes and run it again. Then reexecute your query or Stored Procedure to see the difference.
I have a a few databases with millions of records and once it's properly indexed it returns data very quickly. Be sure to run maintenance on the index as well such as defragging, rebuilding or reindexing. Hope this helps.

Thank you all for your help