General testing methodology

To ensure that the number of rows of data used when testing with the different index configurations was identical, all AVEVA Enterprise SCADA processes were stopped for the duration of the testing.

Stopping the AVEVA Enterprise SCADA processes during testing also reduced the possibility that those processes would interfere with test results by running at different times. The tests were performed with only the Microsoft SQL Server instance running.

The Microsoft SQL Server profiler was configured to record the performance of the test runs. It was configured in the following manner, with the Edit Filter dialog box tailored for each specific test.

Testing consisted of repeatedly running Microsoft SQL scripts performing isolated actions against TimeSeries.dbo.collect with the profiler attached. The types of isolated actions included:

• Performance testing of reading from TimeSeries Collect using the readTimeSeriesCollect procedure. This is the procedure used by trending in the easy eXtended Operator Station (ezXOS). This stored procedure accesses the time, value, fieldTime, and dataQuality fields, performing a join with the dataqualitydefine table. It also does utilities.dbo.UtcDateToGmt calls as required to change time formats.

• Performance testing of row insertion into TimeSeries Collect, executing scripts containing insertion Microsoft SQL statements.

• Performance testing of row deletion on TimeSeries Collect, deleting according to tagId.

The data output from the profiler was saved to a temporary Microsoft SQL table, and select statements were made against the table to extract the data of interest.

Two hundred test runs were executed against the readTimeSeriesCollect stored procedure. Testing involved running 10 batches of runs for deletion and insertion of TimeSeries Collect rows containing 74000 rows in each batch.

Additional tests were performed to examine the disk space requirements before and after.