Index Computed Columns to Help Performance

A great way to improve performance without having to make any application changes is to add a computed column and index it.

This can be demonstrated with the help of an example, create a session log table and insert some data


SET ANSI_NULLS ON;
 SET QUOTED_IDENTIFIER ON;
 SET ANSI_PADDING ON;
 SET NOCOUNT ON;

IF EXISTS ( SELECT 1
 FROM sys.tables
 WHERE [object_id] = OBJECT_ID(N'dbo.SessionLog') )
 BEGIN;
 DROP TABLE dbo.SessionLog;
 END;

CREATE TABLE dbo.SessionLog
 (
 SessionID INT IDENTITY(1, 1)
 NOT NULL ,
 SessionStart DATETIME NOT NULL ,
 SessionEnd DATETIME NOT NULL ,
 CONSTRAINT CK_SessionLog_SessionStart_SessionEnd CHECK ( SessionEnd >= SessionStart )
 );

ALTER TABLE dbo.SessionLog ADD CONSTRAINT pkSessionLog PRIMARY KEY CLUSTERED (SessionID) WITH FILLFACTOR = 100;
 GO

INSERT INTO dbo.SessionLog
 ( SessionStart ,
 SessionEnd
 )
 SELECT DATEADD(MINUTE, ABS(CHECKSUM(NEWID())) % 721,
 CAST(CAST(CURRENT_TIMESTAMP AS DATE) AS DATETIME)) ,
 DATEADD(MINUTE, 720 + ( ABS(CHECKSUM(NEWID())) % 721 ),
 CAST(CAST(CURRENT_TIMESTAMP AS DATE) AS DATETIME))
 FROM dbo.Numbers;

A query for sessions lasting under 60 minutes


SET STATISTICS IO ON; SELECT SessionID, SessionStart, SessionEnd
FROM dbo.SessionLog
WHERE DATEDIFF(MINUTE,SessionStart, SessionEnd) < 60;

results in the following query plan

Index computed column index scan.JPG

However, if we add a computed column and index it like so


ALTER TABLE dbo.SessionLog ADD Duration AS DATEDIFF(MINUTE,SessionStart, SessionEnd);
CREATE NONCLUSTERED INDEX IX_SessionLog_Duration ON dbo.SessionLog(Duration) INCLUDE(SessionStart, SessionEnd);

The scan is replaced by a seek

Index computed column index seek.JPG

And logical reads reduced from 237 to 2.

This is with no change to the query. Both query plans result from running the same query where the computed column Duration is not referenced explicitly


SET STATISTICS IO ON; SELECT SessionID, SessionStart, SessionEnd
FROM dbo.SessionLog
WHERE DATEDIFF(MINUTE,SessionStart, SessionEnd) < 60;

The query optimizer just knows that our where clause predicate matches the computed column definition and uses the index. This means we can potentially vastly improve performance with no application code changes required.

There are some gotchas you have to be careful with

  • SELECT * FROM dbo.SessionLog with now bring back the additional Duration column potentially breaking front end code. This is the reason you should never use SELECT * and always specify an explicit column list.
  • 6 set options must be set correctly when performing the following actions see the Indexes on Computed Columns MSDN article.
    • Creating the index on the computed column
    • Performing any DML (INSERT/UPDATE/DELETE) on the table.
    • If you want the index on the computed column to be used when performing selects.
  • NUMERIC_ROUNDABORT | OFF
  • ANSI_NULLS | ON
  • ANSI_PADDING | ON
  • ANSI_WARNINGS | ON
  • ARITHABORT | ON
  • CONCAT_NULL_YIELDS_NULL | ON
  • QUOTED_IDENTIFIER | ON

Here are some examples of errors

Create the index with SET QUOTED_IDENTIFIER OFF;

Msg 1934, Level 16, State 1, Line 40
CREATE INDEX failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’.

Delete data with SET QUOTED_IDENTIFIER OFF;

Msg 1934, Level 16, State 1, Line 43
DELETE failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’.

Perform the same select that can make use of the index but the SET ANSI_PADDING OFF, this causes the query plan to revert to using an index scan i.e. the index on the computed column is now ignored.

Index computed column index scan

The important point here is that you could break existing code by adding an index on a computed column if all connections don’t have the correct set options specified as required.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s