SQL Clone
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 Eights!
SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)

Group: General Forum Members
Points: 941 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
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95459 Visits: 33013
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 Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91829 Visits: 38954
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 Eights!
SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)

Group: General Forum Members
Points: 941 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
)
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
)

-- 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
)

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
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95459 Visits: 33013
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 Eights!
SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)

Group: General Forum Members
Points: 941 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 Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91829 Visits: 38954
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 Eights!
SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)SSC Eights! (941 reputation)

Group: General Forum Members
Points: 941 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