Indexed views

Hi,
(You need the adventureworks 2008R2 database for this post).

One of the interesting things about an SQL-server is how to get the best performance, when you have complex views. Imagine a view with the following definition

— CODE SNIPPET START —

CREATE VIEW IndexedTest
as
SELECT  t1.SalesOrderNumber, t1.SalesOrderLineNumber, t3.FirstName, t2.EnglishProductName, t1.OrderDateKey, t1.OrderQuantity
FROM dbo.FactInternetSales AS T1
        INNER JOIN dbo.DimProduct AS T2 ON t1.ProductKey=t2.ProductKey
        INNER JOIN dbo.DimCustomer AS t3 ON t1.CustomerKey=t3.CustomerKey

— CODE SNIPPET END —

if you run this code:

— CODE SNIPPET START —

SET STATISTICS IO ON

SELECT *
FROM indexedTest

— CODE SNIPPET END —

 

then you will see, that the query will have to do 1030 reads in the FactInternetSales, 255 in Dim Products and 978 in dim customers. That might be ok, but what if we could do it better? We can, by creating an indexed view. A feature that should only be available in the enterprise version, but by knowing your SQL Server you can also get it to work in a standard edition of SQL 2008.

We need to alter the view, so that it is schemabound, and we do that with the following code:

— CODE SNIPPET START —

 ALTER  VIEW IndexedTest
WITH schemabinding
as
SELECT t1.SalesOrderNumber, t1.SalesOrderLineNumber, t3.FirstName, t2.EnglishProductName, t1.OrderDateKey, t1.OrderQuantity
FROM dbo.FactInternetSales AS T1
        INNER JOIN dbo.DimProduct AS T2 ON t1.ProductKey=t2.ProductKey
        INNER JOIN dbo.DimCustomer AS t3 ON t1.CustomerKey=t3.CustomerKey

— CODE SNIPPET END —

Now we can add a unique clustered index to the view, with the following code.

— CODE SNIPPET START —

USE [AdventureWorksDW2008R2]
go
CREATE UNIQUE CLUSTERED INDEX [OrderNoOrderLineNo] ON [dbo].[IndexedTest]
(
    [SalesOrderNumber] ASC,
    [SalesOrderLineNumber] ASC
)

— CODE SNIPPET END —

Now we are ready to use the indexed view, and when we write our code to select from the view, then we just need to add a tablehint.

— CODE SNIPPET START —

SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT *
FROM indexedtest WITH (NOEXPAND)

— CODE SNIPPET END —

And now, instead of having to read approx 2250 pages, we now only need to read 669 pages. that is much better, and faster.

If we look at the executionplans, then we can use the nice feature of SQL Server to compare two queries. Simply write both queries in the same window, and then show executionplan.

As you might see, then the executionplan were we use the indexed view is much faster and simpler than the one without (15% of the total executiontime).

What if we want to search on the productname? Well, then we can add a nonclustered index to the indexed view (eventhough it will not be a good index). So if we want to get the best “Select performance” from the following query, without us thinking about space or writeperformance:

— CODE SNIPPET BEGIN —

SELECT *
FROM indexedtest WITH (NOEXPAND)
WHERE EnglishProductName LIKE ‘Road%’

— CODE SNIPPET END —

 

Then we could try to add the following index:

— CODE SNIPPET START —

CREATE NONCLUSTERED INDEX [ProductName]
ON [dbo].[IndexedTest] ([EnglishProductName])
INCLUDE ([SalesOrderNumber],[SalesOrderLineNumber],[FirstName],[OrderDateKey],[OrderQuantity])
— CODE SNIPPET END —

If we then compare the selectstatement to the one without the tablehint (NOEXPAND), then we will see that it is only using 4% of the time, which means that it is incredible faster!!!!!!

I can’t say if indexed views is the right choice for you in the search for better performance, but it is worth looking at. BUT!!! There is a lot of limitations, and I suggest that you read the following article from Microsoft before you start working with indexed view:

http://msdn.microsoft.com/en-us/library/dd171921%28v=sql.100%29.aspx

Soren

 

 

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