CODEWITHSUNDEEP
sql-serverperformancesql-server-2008viewindexing
DooDoo
DooDoo

Reputation: 13487

Index does Not affect query performance in indexed views

I want to examine query performance on indexed views.

I create a view based on Product table in Northwind database with all columns.After create view I add a clustered index on the view (Because I couldn't create a non-clustered index without int).

Now before I add a non-clustered index I check this query execution plan and statistics :

SELECT [ProductName]
  ,[QuantityPerUnit]
  ,[UnitPrice]
  ,[UnitsInStock]
  ,[UnitsOnOrder]
  ,[ReorderLevel]
  ,[Discontinued]
FROM [Northwind].[dbo].[test_IndexedView]
WHERE UnitPrice = 21.35

and then I create an index :

CREATE NONCLUSTERED INDEX [idx_Unitp] ON [dbo].[test_IndexedView] 
(
[UnitPrice] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

and again I execute that query but nothing change.Not in exection plan not in statistics.

where is the problem? How I can increase performance with indexed view?

Edit 1) I create a view :

CREATE VIEW [dbo].[indexView]
WITH SCHEMABINDING 
AS
    SELECT     ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, Discontinued, ReorderLevel
    FROM         dbo.Products
GO

and then when I want to create a 'nonclustered index` on it Like this:

CREATE NONCLUSTERED INDEX [idx_Unitp] ON [dbo].[IndexView] 
(
    [UnitPrice] ASC
)

I get this error:

Msg 1940, Level 16, State 1, Line 1
Cannot create index on view 'dbo.IndexView'. It does not have a unique clustered index.

So I forced to create a clustered index.

both of execution plan before and after index is Index Scan

The result of SELECT @@VERSION is :

Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (Intel X86)
Sep 16 2010 20:09:22 Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Edition on Windows NT 6.1 (Build 7600: )

Upvotes: 2

Views: 2238

Answers (2)

Martin Smith
Martin Smith

Reputation: 453908

You say "I forced to create a clustered index." but don't give the definition. As clustered indexes on views need to be unique I added ProductID to the view definition and assumed

CREATE UNIQUE CLUSTERED INDEX [idx_Unitp] ON [dbo].[IndexView] 
(
    [UnitPrice] ASC, ProductID ASC
)

I then see the same as you with a scan on the base table

Scan Plan

Except if I change the query to use FROM [Northwind].[dbo].[IndexView] WITH (NOEXPAND) when the plan does show a seek on the view.

Seek Plan

The reason for this is explained here. The initial query gets a trivial plan and is costed at only 0.0033667. The optimiser chooses this plan before getting as far as indexed view matching.

This is not a good use of indexed views anyway. Creating the index directly on the base table would be better

CREATE NONCLUSTERED INDEX ixNoView
  ON dbo.Products (UnitPrice)
  INCLUDE ( ProductName, 
            QuantityPerUnit, 
            UnitsInStock, 
            UnitsOnOrder, 
            Discontinued, 
            ReorderLevel)

This is narrower than the clustered index on the view as it only has 7 columns rather than 10 as well as avoiding issues with indexed view matching and being more discoverable for other developers.

Now using a non indexed view

CREATE VIEW [dbo].[nonIndexedView]
AS
  SELECT ProductName,
         SupplierID,
         CategoryID,
         QuantityPerUnit,
         UnitPrice,
         UnitsInStock,
         UnitsOnOrder,
         Discontinued,
         ReorderLevel,
         ProductID
  FROM   dbo.Products

The query

SELECT [ProductName]
  ,[QuantityPerUnit]
  ,[UnitPrice]
  ,[UnitsInStock]
  ,[UnitsOnOrder]
  ,[ReorderLevel]
  ,[Discontinued]
FROM dbo.nonIndexedView
WHERE UnitPrice = 21.35

Shows an index seek

non indexed view

Upvotes: 1

marc_s
marc_s

Reputation: 755391

Since you're selecting 7 columns from your table, and the index only contains UnitPrice on its own, then the SQL Server query optimizer will still deem a scan more efficient than using the index to look up the price and then having to do a key lookup for each row found to get the other columns.

I would almost bet that if you included the other columns in your index like this

CREATE NONCLUSTERED INDEX [idx_Unitp] 
ON [dbo].[test_IndexedView] ([UnitPrice] ASC)
   INCLUDE(ProductName, QuantityPerUnit, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued)

then that index would be used - almost for sure.

The requirements for a non-clustered index to be actually used are quite stringent - much more so than most people will believe at first. The selectivity has to be very very high - sometimes less than 1% - for the index to be even considered. Also, the total number of rows returned (and thus the total number of key lookups required to get the rest of the data) needs to be small.

Your best bet is always to have a covering index - an index that contains all the information (all the columns needed) to satisfy a query - in that case, the likelihood of that NC index being used is increased significantly.

Upvotes: 2

Related Questions