Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Indexed view CI chosen of table CI. Why? Expand / Collapse
Author
Message
Posted Monday, May 6, 2013 6:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 6, 2014 1:35 AM
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
Post #1449699
Posted Monday, May 6, 2013 12:03 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 14,002, Visits: 28,377
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1449829
Posted Monday, May 6, 2013 12:16 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:25 AM
Points: 20,795, Visits: 32,710
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.



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)
Post #1449832
Posted Tuesday, May 7, 2013 1:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 6, 2014 1:35 AM
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....
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
Post #1450018
Posted Tuesday, May 7, 2013 3:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 14,002, Visits: 28,377
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1450044
Posted Tuesday, May 7, 2013 5:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 6, 2014 1:35 AM
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
Post #1450075
Posted Tuesday, May 7, 2013 7:06 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:25 AM
Points: 20,795, Visits: 32,710
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.



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)
Post #1450118
Posted Tuesday, May 7, 2013 7:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 6, 2014 1:35 AM
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
Post #1450136
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse