Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Indexed view CI chosen of table CI. Why?


Indexed view CI chosen of table CI. Why?

Author
Message
Dennis Post
Dennis Post
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 547
Hi,

This is related to the table and query in question: http://www.sqlservercentral.com/Forums/Topic1449684-392-1.aspx
(Hope this is clear, otherwise I have to double post them).

A view on the above mentioned table aliases and shifts the column names.
CREATE VIEW vwProducts
WITH SCHEMABINDING
AS
   SELECT   ProductNr,
         Price1 [Price2],
         Price2 [Price3],
         Price3 [Price4],
         Price4 [Price5],
         Price5 [Price0]
   FROM   dbo.Products
GO
CREATE UNIQUE CLUSTERED INDEX UCI_Products_ProdNr ON vwProducts (ProductNr)



The clustered index on the Products table is the same, except for the name (PK_ProdNr).

Why does the optimizer find the views clustered index sexier than the tables?
Especially since the view is not even used in the query.



For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17569 Visits: 32254
I'm not sure which query we're talking about (i looked at and commented on the other post), but the whole idea behind creating an indexed view is to take advantage of the index and statistics created. So the optimizer thinks it can satisfy a given query using the indexed view because it recognizes some level of selectivity within the index that isn't there for base tables.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24169 Visits: 37935
I am assuming that the table dbo.Products has more columns than those defined in the indexed view you built over the table and that the query using the indexed views index is only looking at the columns defined in the indexed view.

If these assumptions are true, SQL Server is using the index on the view (and the view) to satisfy the query because it is more compact than using the underlying table.


Really can't give a better explanation as you really didn't provide enough information in either post to really give a better answer.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Dennis Post
Dennis Post
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 547
Thanks for your input guys.

@Grant,
In our case we are using an indexed view only so that we can replicate it to the products table in another database. If you know a better way, i'll start a new topic. :-)

@Lynn,
Your assumtion is correct. View = 54 columns where as the table has 57. Though not sure if is correct due to the replication....Unsure
I added more columns to the view, but the optimizer still sometimes uses the views index.

Here's the test code.
CREATE TABLE Products
   (
   ProductNr Int PRIMARY KEY,
   Price1 Money,
   Price2 Money,
   Price3 Money,
   Price4 Money,
   Price5 Money
   Wink
GO

INSERT   Products
SELECT   DISTINCT TOP 10000
      ROW_NUMBER() OVER (ORDER BY C1.Object_ID),
      ABS(CHECKSUM(NEWID()) % 100.01) + 1 Price1,
      ABS(CHECKSUM(NEWID()) % 100.01) + 1 Price2,
      ABS(CHECKSUM(NEWID()) % 100.01) + 1 Price3,
      ABS(CHECKSUM(NEWID()) % 100.01) + 1 Price4,
      ABS(CHECKSUM(NEWID()) % 100.01) + 1 Price5
FROM   sys.all_columns C1
      CROSS APPLY sys.all_columns C2
GO

CREATE VIEW vwProducts
WITH SCHEMABINDING
AS
SELECT   ProductNr, Price1, Price1 AS Price2, Price2 AS Price3, Price4, Price5, Price5 AS Price6
FROM   dbo.Products
GO
CREATE UNIQUE CLUSTERED INDEX UCI_Products_ProdNr ON vwProducts (ProductNr)
GO

-- Parameters
DECLARE   @InputVal   Char(1),
      @ProdNr      Int
SELECT   @InputVal   = '1',
      @ProdNr      = 10

-- Variables
DECLARE   @Price      Money

-- Execution plan returns multiple index seeks
SET   @Price =   
   (
   CASE   
      WHEN @InputVal = '1' THEN   (SELECT Price1 FROM Products WHERE ProductNr = @ProdNr)
      WHEN @InputVal = '2' THEN   (SELECT Price2 FROM Products WHERE ProductNr = @ProdNr)
      WHEN @InputVal = '3' THEN   (SELECT Price3 FROM Products WHERE ProductNr = @ProdNr)
      WHEN @InputVal = '4' THEN   (SELECT Price4 FROM Products WHERE ProductNr = @ProdNr)
      ELSE                  (SELECT Price5 FROM Products WHERE ProductNr = @ProdNr)
   END
   Wink

-- Execution plan returns 1 index seek
SET   @Price =   
   (
   SELECT   CASE @InputVal
            WHEN '1' THEN Price1
            WHEN '2' THEN Price2
            WHEN '3' THEN Price3
            WHEN '4' THEN Price4
            ELSE Price5
         END
   FROM   Products
   WHERE   ProductNr = @ProdNr
   Wink

DROP VIEW vwProducts
DROP TABLE Products



Cheers



For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17569 Visits: 32254
Still back to mine and Lynn's original answer. The view has fewer columns than the base tables and that alone can make the difference. Also, it's possible that the statistics are more up to date in the view, making it more accurate and therefore more useful to the optimizer.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Dennis Post
Dennis Post
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 547
Ok. Thanks for the education guys. :-)



For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24169 Visits: 37935
First, I don't think your question is really about why is the index on the view being selected as much as why the first query generates 5 index seeks while the second only one.

The reason is the first generates 5 index seeks is that SQL Server has to generate a plan that covers all possible execution paths in the first query. The CASE statement is not evaluated during this phase, it is evaluated during the execution of the query itself.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Dennis Post
Dennis Post
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 547
Lynn Pettis (5/7/2013)
First, I don't think your question is really about why is the index on the view being selected as much as why the first query generates 5 index seeks while the second only one.
That was what my other question was about :-)

Lynn Pettis (5/7/2013)

The reason is the first generates 5 index seeks is that SQL Server has to generate a plan that covers all possible execution paths in the first query. The CASE statement is not evaluated during this phase, it is evaluated during the execution of the query itself.
I am surprised that IF ELSE IF statements don't generate similar plans than CASE statements.



For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search