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

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