Guidelines for importing, inserting, and updating history data

Use the following guidelines to help you decide the best way to import or insert data into history. Each method has its strengths for certain applications, and often you will need to balance the need for speed against some limitations.

For a standard CSV import (as opposed to a "fast load" CSV import), the CSV file format and the format of the data contained within the file is very flexible. However, this flexibility requires the system to perform a large amount of processing on the data before it can be imported. Thus, there is an inverse relationship between amount of data to process and import speed. The time required to process a file is at least exponentially related to the number of values contained in the file.

Additional factors for a normal import are:

• If multiple files are to be processed at the same time, the total size of the CSV file is limited to less than 4 MB.

• The CSV file cannot contain more than 100,000 data values.

• The number of tags represented in the file cannot exceed 1024.

• Single files of up to 6 MB will be processed, provided that it does not exceed the file data and tag limits.

Performing a non-real-time insert with a Transact-SQL statement also requires a large amount of data processing.

The fastest way to insert/import data into the system is to use one of the methods that employs the real-time storage service to get the data into the history blocks. These include real-time inserts by Transact-SQL statements and "fast load" CSV imports. Real-time inserts can occur at a fairly high speed, so use this method when possible. Performing a "fast load" CSV import is also a high-speed option. To do a "fast load" import, however, the data must be in time-series order.

In general, choose to use the fast load import if:

• It is not is not feasible to perform a normal CSV import.

• You need to import very large CSV files.

• You want storage rules applied to the data you are importing. A normal CSV import does not apply storage rules; everything is stored as a delta.

Also, do not import fast load data for a tag if there is existing stored data for that tag in the same time range.