Improving indexes and providing a performance increase when running reports

Table

Index

ACCTRANS

USE [ACCESSCONTROL]

GO

CREATE NONCLUSTERED INDEX UDO_Report_ACCTRANS_By_Object_Time

ON [dbo].[ACCTRANS] ([ACCTRANS_OBJECTID],[TIMESTAMP])

INCLUDE ([USERID],[ACCTRANS_OPERATIONID],[ENTERVALUE],[COMPUTERN],[ACCROWID],[LNL_DBID],[USER_NOTES],[UTC_OFFSET])

GO

EVENTS

USE [ACCESSCONTROL]

GO

CREATE NONCLUSTERED INDEX UDO_Report_EventInfo_By_CardNum_Type_Time

ON [dbo].[EVENTS] ([CARDNUM],[EVENTTYPE],[EVENT_TIME_UTC])

INCLUDE ([SERIALNUM],[DEVID],[INPUTDEVID],[EVENTID],[MACHINE],[EMPID],[ISSUECODE],[CARD_EXTENDED_ID])

GO

LASTLOCATION

USE [ACCESSCONTROL]

GO

CREATE NONCLUSTERED INDEX UDO_Report_LastLocation_By_Flag_Time

ON [dbo].[LASTLOCATION] ([ACCESS_FLAG],[EVENT_TIME_UTC])

INCLUDE ([PANELID],[READERID],[CARDNUM],[EVENTID],[EVENTTYPE],[EMPID])

GO

BADGE

USE [ACCESSCONTROL]

GO

CREATE NONCLUSTERED INDEX UDO_Report_Badge_By_Status

ON [dbo].[BADGE] ([STATUS])

INCLUDE ([ID],[BADGEKEY])

GO

ACCLVLINK

USE [ACCESSCONTROL]

GO

CREATE NONCLUSTERED INDEX UDO_Report_Accvlink_By_Panel_Reader

ON [dbo].[ACCLVLINK] ([PANELID],[READERID])

INCLUDE ([ACCESSLVID],[TZID],[FLOORID])

GO