Search

Adding indexes to improve data summarization performance

Surveyor 6.x

Knowledge Base

Note: Use this SQL script to create database indexes that improve the summarization process in Surveyor versions 6.0 - 6.0.2xx.

The script adds indexes to three tables used by the summarization process (DataSummarization.exe). These indexes can reduce the summarization process time by up to 90%.

Download the script file and add the indexes

  1. Extract the file AddSummarizeIndex.sql from your Surveyor installation package.
  2. On the database server, open SQL Server Management Studio, and click New Query.

    Open a blank page for an SQL script

  3. From the list of databases, select the Surveyor database.

    The default name is EnterprisePowerManagementDB.

  4. Drag the AddSummarizeIndex.sql file from the extracted location in Windows Explorer to the new query tab in SQL Server Management Studio.
  5. Execute the script.

    SQL Server Management Studio runs the script and displays a success message when the indexing process is complete.

    NOTE: Depending on the size of your database, determined by the number of devices and the length of time your Surveyor deployment has been running, this process will take several minutes or even hours.

Script from AddSummarizeIndex.sql

This script file contains the following code:

IF EXISTS (SELECT name FROM sysindexes WHERE
name = 'IDX_DeviceEventUpload_InsertDate')
   drop index IDX_DeviceEventUpload_InsertDate on
DeviceEventUpload
go
 -- add an index on insert date so rollup can quickly find the most
recently inserted events
create index
IDX_DeviceEventUpload_InsertDate
 on DeviceEventUpload (insertDate)
go
IF EXISTS (SELECT name FROM sysindexes WHERE
name = 'IDX_DeviceSample_InsertSampleDeviceIntervalPowerBaselineEnergy')
drop index
IDX_DeviceSample_InsertSampleDeviceIntervalPowerBaselineEnergy on
DeviceSample 
go
 -- creating a covering index for this, allowing the primary key 
clustered index to be something used more frequently
create index
IDX_DeviceSample_InsertSampleDeviceIntervalPowerBaselineEnergy
on DeviceSample (InsertDate, SampleTimeLocal, DeviceId, 
SampleIntervalMs, PowerDrawWatts, BaselineDrawWatts, EnergyLevel, 
DeviceFamilyId)
go
IF EXISTS (SELECT name FROM sysindexes WHERE
name =
'IDX_DeviceSample_SampleTimeInsertDeviceIntervalPowerBaselineEnergy')
drop index
IDX_DeviceSample_SampleTimeInsertDeviceIntervalPowerBaselineEnergy
on DeviceSample
go
create index 
IDX_DeviceSample_SampleTimeInsertDeviceIntervalPowerBaselineEnergy
on DeviceSample (SampleTimeLocal, InsertDate, DeviceId,
SampleIntervalMs, PowerDrawWatts, BaselineDrawWatts, EnergyLevel,
DeviceFamilyId)
go

See also

About the data summarization process

Applies to

Product

  • Surveyor versions 6.0 - 6.0.2xx

 

 

data summarization