Full-text index population performance on a SQL 2005 indexed view

Question

I have created an indexed view:

CREATE VIEW LogValueTexts WITH SCHEMABINDING AS
SELECT ISNULL(LRVS_SLOG_ID*256+LRVS_IDX,0) AS ID,LRVS_VALUE AS Value
FROM dbo.LRVS_LogRecordedValues WHERE LEN(LRVS_VALUE)>4

CREATE UNIQUE CLUSTERED INDEX IX_LogValueTexts ON LogValueTexts (ID)

On SQL 2005 Standard SP3 it takes forever to populate a full-text index on that view because the full-text indexing executes the following query for every row in the view:

SELECT COLUMN FULLTEXTALL FROM[dbo].[LogValueTexts] WHERE COLUMN FULLTEXTKEY = @p1

I assume that COLUMN FULLTEXTALL and COLUMN FULLTEXTKEY are actually Value and ID, but that's what SQL Server Profiler shows. The problem is that the query plan uses a clustered index scan over about 11M rows/1GB of data because it doesn't use the index on the view. I have tried creating a plan guide for that query, but since it's not a standard T-SQL query it doesn't allow it (Incorrect syntax near the keyword 'FULLTEXTKEY').

Is there a way to get this full-text index to work other than:

• upgrading to SQL 2008 (or SQL 2005 Enterprise) where it works fine.
• creating a unique ID and a covering index on the underlying table.

Upgrading would require downtime on the server and probably new SQL Server licences while creating the unique ID and a covering index would waste a lot of space because only a subset of the 11M rows needs full-text indexing (LRVS_VALUE is often NULL or has a very short text value).

Answer 1

I dont know your data; why cant you put the full text index on the original table? You could add the calculated column into your table structure. That way you wouldnt have the index rebuild operation ( i think that is the cause of your scan)

If you can't do that then next easiest change is likely to create an lookup table populated with an sp or a triggerthat way you can change the table's indexing so they make sense for your query.

Your final option (and one you would need to spend some time on to get right) would be using partitioned tables. You could have a partition that covers the data filtered on the view. Full text index the entire table; your query at run time would hit the partitioned table with the relevant data in it.