May 6, 2013 at 6:50 am
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
SELECTProductNr,
Price1 [Price2],
Price2 [Price3],
Price3 [Price4],
Price4 [Price5],
Price5 [Price0]
FROMdbo.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.
May 6, 2013 at 12:03 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 6, 2013 at 12:16 pm
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.
May 7, 2013 at 1:59 am
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. 🙂
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
INSERTProducts
SELECTDISTINCT 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
FROMsys.all_columns C1
CROSS APPLY sys.all_columns C2
GO
CREATE VIEW vwProducts
WITH SCHEMABINDING
AS
SELECTProductNr, Price1, Price1 AS Price2, Price2 AS Price3, Price4, Price5, Price5 AS Price6
FROMdbo.Products
GO
CREATE UNIQUE CLUSTERED INDEX UCI_Products_ProdNr ON vwProducts (ProductNr)
GO
-- Parameters
DECLARE@InputValChar(1),
@ProdNrInt
SELECT@InputVal= '1',
@ProdNr= 10
-- Variables
DECLARE@PriceMoney
-- 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 =
(
SELECTCASE @InputVal
WHEN '1' THEN Price1
WHEN '2' THEN Price2
WHEN '3' THEN Price3
WHEN '4' THEN Price4
ELSE Price5
END
FROMProducts
WHEREProductNr = @ProdNr
)
DROP VIEW vwProducts
DROP TABLE Products
Cheers
May 7, 2013 at 3:48 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 7, 2013 at 5:38 am
May 7, 2013 at 7:06 am
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.
May 7, 2013 at 7:32 am
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply