Indexed Views And The Benefits They Provide

Despite being a DBA for a few years now, only just have I come to appreciate the benefits Index Views can bring. If you have commonly aggregated data sets which are constantly requested, Index Views could be your new best friend.

Show Me What You Can Do

Let’s take a rather messy query, which has been completely and utterly fabricated by yours truly, purely for this post

USE StackOverflow2010;
GO

SELECT 
	DAY(P.CreationDate),
	MONTH(P.CreationDate),
	YEAR(P.CreationDate),
	'http://stackoverflow.com/questions/' + RTRIM(P.Id) AS URL
FROM dbo.Posts P

GROUP BY DAY(P.CreationDate),MONTH(P.CreationDate),YEAR(P.CreationDate),'http://stackoverflow.com/questions/' + RTRIM(P.Id)

GO

As you can imagine, this is hardly Mr Speedy when it comes to run time.

The plan isn’t great, we have some stinky things going on here such as implicit conversions and expensive parallel operators (of course this was intentional for the purpose of the post):

So currently, we have a bit of a stinky query which clocks up around 15,923 milliseconds CPU time and 800,000 logical reads.

Let’s say, dear reader, for explanation purposes, we have this query linked to a front end process which the end-user can call anytime they wish. Simply by clicking a button in our hypothetical application, they can call this dataset anytime they wish. Scary huh? I know it is and it recently happened to me at my place of work.

View From The Afternoon

Something which I recently implemented at work was an indexed view, for such a requirement. Of course, the query was not based on the StackOverflow database, you’re just going to have to use your imagination.

What we can do, in times like these is utilise the power of an indexed view. What indexed views give us is a ‘materialised’ version of that dataset, anytime we need it. Of course, the data will get updated to reflect any changes to the underlying datasets.

Show Me All The Things

So let’s see what difference implementing an indexed view can do for us in this situation.

Let’s take our nasty little query and create a view out of it. In order for us to put an index on it, however, we’re going to have to create it with the option ‘WITH SCHEMABINDING’ which will lock this object down and not allow us to amend any of the associated tables without dropping this view first.

CREATE VIEW dbo.MyPointOfView
WITH SCHEMABINDING
AS

SELECT 
	DAY(P.CreationDate) AS [DAY],
	MONTH(P.CreationDate) AS [MONTH],
	YEAR(P.CreationDate) AS [YEAR],
	'http://stackoverflow.com/questions/' + RTRIM(P.Id) AS URL,
	COUNT_BIG(*) AS [Big Old Count]
FROM dbo.Posts P

GROUP BY DAY(P.CreationDate),MONTH(P.CreationDate),YEAR(P.CreationDate),'http://stackoverflow.com/questions/' + RTRIM(P.Id)

GO

Now we have our view, lets add an index to the shiny new view:

CREATE UNIQUE CLUSTERED INDEX IX_MyFavouriteIndex
	ON dbo.MyPointOfView(URL);

Right, so new view and a new index. Shall we see what happens when we try and retrieve that dataset?

SELECT 
	[Day],
	[Month],
	[Year],
	[URL]
FROM dbo.MyPointOfView

Now, just check out the CPU time and logical reads!

The plan shows, we are able to simply scan our dataset directly from the view created:

The results speak for themselves, CPU time and logical reads are seriously reduced after utilising our indexed view. The before and after figures are quite impressive:

In Conclusion

As with most things in life, there are caveats. Would I blindly recommend using indexed views throughout your databases? Nope. There are quite a few gotchas with using index views. The most important ones in my opinion include:

• You cannot alter the underlying tables without dropping the view first
• Inserts, updates and deletes activity on the underlying tables will take a hit. At the end of the day, we’re adding an index.

However, when the time is right, they can be a great tool to utilise. Try them out and test, test, test.

Thanks for reading.