SQL SERVER – Index Created on View not Used Often – Limitation of the View 3

Update: Please read the summary post of all the 11 Limitations of the view SQL SERVER – The Limitations of the Views – Eleven and more…

I have heard many people saying that if they create view and index on it, this will reduce the load on original table as all the subsequent queries on view will not access the basic table. This is not true always and the view may not give you the performance optimizations which you are looking for.

In the following example, we will create the base table first. We will then create the view on top of it and create the index on the view. Subsequently, we will run the simple SELECT statement on base table and on the view. It is noticed that view does not pick up the index created on the view, but uses the base index created on the original table. It will also expand the underlying table and use the index created on base table. If you assume that after creating the index on view, the view is materialized and has no relation with original table, then this can very well be incorrect.

USE tempdb
GO
IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[SampleView]'))
DROP VIEW [dbo].[SampleView]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[mySampleTable]') AND TYPE IN (N'U'))
DROP TABLE [dbo].[mySampleTable]
GO
-- Create SampleTable
CREATE TABLE mySampleTable (ID1 INT, ID2 INT, SomeData VARCHAR(100))
INSERT INTO mySampleTable (ID1,ID2,SomeData)
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY o1.name),
ROW_NUMBER() OVER (ORDER BY o2.name),
o2.name
FROM sys.all_objects o1
CROSS JOIN sys.all_objects o2
GO
-- Create Index on Table
CREATE UNIQUE CLUSTERED INDEX [IX_OriginalTable] ON mySampleTable
(
ID1 ASC
)
GO
-- Select from table
SELECT ID1,ID2,SomeData
FROM mySampleTable
GO
-- Create View
CREATE VIEW SampleView
WITH SCHEMABINDING
AS
SELECT
ID1,ID2,SomeData
FROM dbo.mySampleTable
GO
-- Create Index on View
CREATE UNIQUE CLUSTERED INDEX [IX_ViewSample] ON [dbo].[SampleView]
(
ID2 ASC
)
GO
-- Select from view
SELECT ID1,ID2,SomeData
FROM SampleView
GO

Now let us observe the execution plan and the usage of index along with query.

Querying Original Table

Querying View

You might have noticed that the View used the same index as original table and did not use the index created on the table. The reason is as follows: the optimizer has decided that the original index will be more beneficial in query plan than the newly created index. I tried clearing the cache, but the same result is obtained every time.

If you really want your view to act as an independent table and have no relation with original underlying table, then you can use the hint [noexpand] and the view will not expand. We will see this in some other article in future.

Let me know if you want to read about any other limitation of the view.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

16 thoughts on “SQL SERVER – Index Created on View not Used Often – Limitation of the View 3

  1. Hi Pinal,

    You are right. View might get expanded so that the actual data is read from the table instead of the view. Like you said NOEXPAND prevents expanding so if you run following queries you’ll see the difference:

    SELECT ID1,ID2,SomeData
    FROM mySampleTable

    SELECT ID1,ID2,SomeData
    FROM SampleView
    WITH (NOEXPAND)

    The latter one should have lower query cost compared to the former one.

    But the example is, forgive me, pretty bad one. Indexed view does not do any good in this situation since the view is actually as big as the original table. The real benefits lies in joins and aggregations of large datasets. I urge everyone to read more from here:

    http://technet.microsoft.com/en-us/library/cc917715.aspx

    There’s lots of stuff which I didn’t know even existed, like all those SET options which must be set before creating and index to the view.

    Like

  2. Hi Pinal,

    I am working with SQL Server since 2 years, I know the difference between ‘RIGHT OUTER JOIN’ and ‘LEFT OUTER JOIN’, I know when to use ‘RIGHT OUTER JOIN’ or ‘LEFT OUTER JOIN’. but the thing is that which one is better…
    ‘SELECT * FROM A LEFT JOIN B ON (A.ID = B.ID)’ or
    ‘SELECT * FROM B LEFT JOIN A ON (A.ID = B.ID)’ …..

    Like

  3. Hi Pinal,

    Our team faced an issue in reporting services. We use stored procedures in ssrs. our stored procedure mostly executes max with in 1- 3mins( depends on customer),But in application it takes more than 30 mins. so my manager wants me to optimize the stored procedure it might decrease in application to get the report.
    Please help me out with some kind of suggestions.

    Thanks in advance

    Like

    • Run the procedure from SSMS and see how long it takes. If it returns in a minute or two and the application is taking 30 minutes to finish then the problem must be in the application code and not in the procedure.

      Like

  4. Thanks for reply Marko,

    But when i see the data from report server database maximum amount was taken by take stored procedure to excute the data.
    i saw some reports as taken almost 2hours 10 mins in that 95% by data reterival by stored procedure and remaining else 5% and in another case 40% by stored procedure and remaining by excuting the report.

    Thanks in advance

    Like

    • Well then there’s definetely something wrong with the stored proc. Unless you have gazillion size database of course.

      I would do couple of things first. I would start with checking if there’s something fundamentally wrong, something like normal programming problems, large nested loops for example.

      Then I’d check that if the procedures are making queries that are missing indexes. Especially non-indexed NOT IN queries and datetime fields can take up a lot of time to finish. In one case I dropped 4 minute query to milliseconds just by adding index to datetime field which was used in ORDER BY.

      Then I would just go by hunch. Maybe check execution plan, try tuning advisor, go through the proc line-by-line and poke here and there. Unfortunately I can’t be very specific about this since I’d have to see the proc and have your data to test it.

      Like

  5. good morning sir

    i have 1 col in salary of employee table

    salary
    15000
    12000
    13000
    18000
    40000
    20000
    12000
    16000
    8000
    6000
    3500
    12000
    means total col of salary 200 but i want to sum only 100 col
    i knew that if i want a that sum of 200 col use select sum(salary) from table name but i want sum only for 100 col
    then i don’t know about this question plz sir solve this query
    thanks in advance

    Like

  6. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Data Warehouseing Concepts – Day 20 of 31 Journey to SQLAuthority

  7. Is there any point in creating indices on a UNION ALL view when the tables involved in the UNION already are indexed?

    In my case, I have a UNION ALL view involving SELECTs on two tables. One table contains a few hundred thousand rows. The other table contains over a million rows. Both tables are already indexed. Could I gain anything by creating indices on the view?

    Like

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #008 « SQL Server Journey with SQL Authority

  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #045 | Journey to SQL Authority with Pinal Dave

  10. Pingback: SQL SERVER – Weekly Series – Memory Lane – #048 | Journey to SQL Authority with Pinal Dave

  11. Pingback: SQL SERVER – Weekly Series – Memory Lane – #049 | Journey to SQL Authority with Pinal Dave

  12. Hello Sir,
    You have created a unique clustered index on ID2 column right?
    Will this index be valid for the underlying table also?
    If so, we can only have one clustered index per table?

    I got confused here, can you help me.

    Like

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