CODEWITHSUNDEEP
sql-server-2008t-sql
Randy Minder
Randy Minder

Reputation: 48402

Confusion about an Indexed View

I have the following indexed view:

ALTER View [CIC].[vwCoaterC473Heat] WITH SCHEMABINDING
AS

Select
    id as ID,
    DATEADD(ms, -DATEPART(ms, read_time), read_time) as ReadTime,
    equipment_id as EquipmentID,
    ...
    ...
From dbo.td_coater_c473_heat
Where read_time >= Convert(dateTime,'1/1/2012',120)

CREATE UNIQUE CLUSTERED INDEX [IX_vwCoaterC473Heat_ReadTime_EquipmentID_ID] 
ON [CIC].[vwCoaterC473Heat] 
(
    [ReadTime] ASC,
    [EquipmentID] ASC,
    [ID] ASC
)
GO

And I have the following query that references the indexed view:

Select
   r.Coater,
   r.ReadTime,
   C473_left_A_actual_Temp,
   C473_right_A_actual_Temp,
   C473_left_B_actual_Temp,
   C473_right_B_actual_Temp,
   HD02A_Actual_Voltage,
   HD02A_Actual_Current,
   HD02B_Actual_Voltage,    
   HD02B_Actual_Current
From Cic.RandyTemp r
   Inner Join Cic.vwCoaterC473Heat a 
   On a.EquipmentId = r.Coater And a.ReadTime = r.ReadTime

The query plan generated from this looks as follows:

enter image description here

I'm curious why SQL Server is bypassing the persisted data from the indexed view, and querying the underlying table the view is based on.

Upvotes: 1

Views: 192

Answers (1)

anon
anon

Reputation:

Are you using SQL Server Standard Edition? If so you need to use the WITH (NOEXPAND) hint. Please try this version and see if the indexed view is used. It might just be that the optimizer has decided that accessing the index in the base table is more efficient.

SELECT
   r.Coater,
   r.ReadTime,
   C473_left_A_actual_Temp, -- why no alias prefixes from here down?
   C473_right_A_actual_Temp,
   C473_left_B_actual_Temp,
   C473_right_B_actual_Temp,
   HD02A_Actual_Voltage,
   HD02A_Actual_Current,
   HD02B_Actual_Voltage,    
   HD02B_Actual_Current
FROM Cic.RandyTemp AS r
   INNER JOIN Cic.vwCoaterC473Heat AS a WITH (NOEXPAND)
   ON a.EquipmentId = r.Coater 
   AND a.ReadTime = r.ReadTime;

Upvotes: 5

Related Questions