Issues with Indexed Views in SQL Server 2008r2 on a table with persisted columns and with low DML activity

Question

I am issues with using indexed views. I have a product catalog and a stored proc reads the data in the rows according to various filters on the data. At the same time, the data needs to remain in XML because of a CMS application that runs on top of it. The data in this table has low DML activity, updates/inserts only happen when we get a new item in or need to add or remove quantity.

In my code below, which is somewhat redacted for brevity and privacy, I the promote the meta_detail XML into various persisted columns for fast searching. I wanted to use an indexed view because of extensive joins (original has 4 joins or meta data tables), and being able to compute data from promoted columns and source columns.

My issue is that when I read from the view I create, it does not seem to be materialized because it does not take the persisted values and save them, it seems to be loading for a good 6 minutes, where as if I select from the table with no calculations, I get the results back in .05 seconds for 3000 rows.

What should I be doing differently?

As a stop gap, I have resorted to dumping the table into another "buffer" table every day at a certain low activity time, and pushing updates to the financial detail quantity/price every time to BOTH tables to keep them in synch. The search procedure uses the buffer table. Of course working with one table is better than two....so? Also with that said, I had to create a mechanism for the front-end to know the results are being updated for those 4am shoppers, so they could check back in a couple of minutes.

Thanks

 -- function
 CREATE FUNCTION dbo.fn_compute_inventory_describe(@item int, @meta xml) RETURNS varchar(MAX) WITH SCHEMABINDING 
 AS BEGIN
 DECLARE @name varchar(MAX) = ''
 DECLARE @brand varchar(MAX) = COALESCE(NULLIF(@meta.value('(/metadata/information/name/brand/value)[1]', 'varchar(MAX)'), 'None'),''), 
 @sub varchar(MAX) = COALESCE(NULLIF(@meta.value('(/metadata/information/name/sub_brand/value)[1]', 'varchar(MAX)'), 'None'),''), 
 @product varchar(MAX) = COALESCE(NULLIF(@meta.value('(/metadata/information/name/product/value)[1]', 'varchar(MAX)'), 'None'),''),
 @description varchar(MAX) = COALESCE(NULLIF(@meta.value('(/metadata/categorization/search/description/value)[1]', 'varchar(MAX)'), 'None'),'No Details Available')
 IF (@item = 1)
 BEGIN
 SELECT @name = REPLACE(@brand + ' ' + @sub + ' ' + @product, ' ', ' ') 
 END
 ELSE IF (@item = 2)
 BEGIN
 SELECT @name = REPLACE(@brand + ' ' + @sub + ' ' + @product, ' ', ' ')
 IF (LEN(@name) > 75) SELECT @name = REPLACE(@sub + ' ' + @product, ' ', ' ')
 IF (LEN(@name) > 75) SELECT @name = REPLACE(@product, ' ', ' ') 
 END
 ELSE IF (@item = 3)
 BEGIN
 SELECT @name = REPLACE(@description, ' ', ' ') 
 END 
 RETURN @name
 END
 GO
 -- table
 CREATE TABLE [dbo].[tbl_products]
 (
 [uiid] [int] NOT NULL IDENTITY(1,1),
 [ukey] [varchar](11) NULL,
 [meta_type] [int] NOT NULL DEFAULT 17,
 [pname] AS ([dbo].[fn_compute_inventory_describe](1,meta_detail)) PERSISTED,
 [publish] AS ([dbo].[fn_compute_inventory_describe](2,meta_detail)) PERSISTED,
 [pdescription] AS ([dbo].[fn_compute_inventory_describe](3,meta_detail)) PERSISTED, 
 [cOnHand] AS ([dbo].[fn_compute_inventory_quantity](1, meta_detail)) PERSISTED, 
 [cDistressed] AS ([dbo].[fn_compute_inventory_quantity](5, meta_detail)) PERSISTED,
 [cOpenBox] AS ([dbo].[fn_compute_inventory_quantity](6, meta_detail)) PERSISTED,
 [meta_detail] [xml] (DOCUMENT [dbo].[xmls_inventory_metadata]) NOT NULL DEFAULT 
 N'<?xml version="1.0"?>
 <metadata>
 <information>
 <name>
 <brand><friendly/><render/><restrict/><value>None</value></brand>
 <sub_brand><friendly/><render/><restrict/><value>None</value></sub_brand>
 <product><friendly/><render/><restrict/><value>None</value></product>
 <model><friendly/><render/><restrict/><value>None</value></model>
 </name> 
 </information> 
 <financial>
 <quantities>
 <onhand><friendly/><render/><restrict/><value>0</value></onhand>
 <extend> 
 <openbox><friendly/><render/><restrict/><value>0</value></openbox>
 <distressed><friendly/><render/><restrict/><value>0</value></distressed> 
 </extend>
 </quantities> 
 </financial> 
 </metadata>', -- parts redacted for breavity/source control
 CONSTRAINT pk_inventory_base PRIMARY KEY (uiid),
 CONSTRAINT fk_inventory_base_type FOREIGN KEY (meta_type) REFERENCES dbo.tbl_types(refid), 
 )
 GO
 -- view
 CREATE VIEW [dbo].[vw_products] WITH SCHEMABINDING AS
 SELECT uiid AS pid, ukey AS pkey, pname, pdescription,
 t.refid AS typeid, t.shortname AS typeurl, 
 t.display AS typedisplay,
 cOnHand, cDistressed, cOpenBox,
 (cOnHand) AS cQTY9,
 (cDistressed + cOpenBox) AS cQTY0
 FROM dbo.tbl_products p INNER JOIN
 dbo.tbl_types t ON p.meta_type = t.refid
 GO
 -- index
 CREATE UNIQUE CLUSTERED INDEX un_products_view ON [dbo].[vw_products] (pid) WITH (IGNORE_DUP_KEY = ON)
 GO

• mrdenny: It should be pretty easy to see what object you are querying from. What does the output from SET STATISTICS IO ON say? I see you creating a function, but I don't see the function being used anywhere in the view, is it?
• Alex Erwin: It is used in the computed columns in tbl_products table
• mrdenny: Ah, there it is. What's the output from Statistics IO say? What's the wait type reported when you from the query the first time?
• Magier: What SQL Edition are you using?

Answer 1

If you are using SQL Standard Edition, you have to use the WITH (NOEXPAND) table hint. If you are using Enterprise Edition, you might want to use that hint anyway, to get statistics on the view, and to prevent the optimizer choosing not to use it (see the second link below).

You may also want to assess the benefits of adding other nonclustered indexes to your view.

Read more:

• SQL Server Indexed Views: The Basics
• Indexed Views and Statistics