如何加快这个索引视图?

I have a simple Indexed View. When I query against it, it's pretty slow. First I show you the schema's and indexes. Then the simple queries. Finally a query plan screnie.

我有一个简单的索引视图。当我查询它时，它非常慢。首先，我将向您展示模式和索引。然后简单的查询。最后一个查询计划screnie。

Update: Proof of Solution at the bottom of this post.

Schema

This is what it looks like :-

这就是它的样子

CREATE view [dbo].[PostsCleanSubjectView] with SCHEMABINDING AS
    SELECT PostId, PostTypeId, 
        [dbo].[ToUriCleanText]([Subject]) AS CleanedSubject
    FROM [dbo].[Posts]

My udf ToUriCleanText just replaces various characters with an empty character. Eg. replaces all '#' chars with ''.

我的udf ToUriCleanText仅仅用一个空字符替换不同的字符。如。用“”替换所有的“#”字符。

Then i've added two indexes on this :-

那么我在这上面增加了两个索引:-

Indexes

Primary Key Index (ie. Clustered Index)

主键索引(即。聚集索引)

CREATE UNIQUE CLUSTERED INDEX [PK_PostCleanSubjectView] ON 
    [dbo].[PostsCleanSubjectView] 
(
    [PostId] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
      SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
      ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

And a Non-Clustered Index

和一个集群指数

CREATE NONCLUSTERED INDEX [IX_PostCleanSubjectView_PostTypeId_Subject] ON 
    [dbo].[PostsCleanSubjectView] 
(
    [CleanedSubject] ASC,
    [PostTypeId] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
      SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
      ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Now, this has around 25K rows. Nothing big at all.

这个大约有25K行。没有什么大的。

When i do the following queries, they both take around 4 odd seconds. WTF? This should be.. basically instant!

当我执行以下查询时，它们都需要大约4秒。WTF ?这应该是. .基本上瞬间!

Query 1

SELECT a.PostId
FROM PostsCleanSubjectView a 
WHERE a.CleanedSubject = 'Just-out-of-town'

Query 2 (added another where clause item)

SELECT a.PostId
FROM PostsCleanSubjectView a 
WHERE a.CleanedSubject = 'Just-out-of-town' AND a.PostTypeId = 1

What have I done wrong? Is the UDF screwing things up? I thought that, because i have index'd this view, it would be materialised. As such, it would not have to calculate that string column.

我做错了什么?UDF搞砸了吗?我认为，因为我索引了这个视图，它将被实现。因此，它不必计算字符串列。

Here's a screenie of the query plan, if this helps :- 如何加快这个索引视图?

这是一个屏幕的查询计划，如果这有助于:-

Also, notice the index it's using? Why is it using that index?

还有，注意它使用的索引?为什么要用这个指数呢?

That index is...

这一指数是……

CREATE NONCLUSTERED INDEX [IX_Posts_PostTypeId_Subject] ON [dbo].[Posts] 
(
    [PostTypeId] ASC,
    [Subject] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
      SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, 
      ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

So yeah, any ideas folks?

大家有什么想法吗?

Update 1: Added schema for the udf.

CREATE FUNCTION [dbo].[ToUriCleanText]
(
    @Subject NVARCHAR(300)
)
RETURNS NVARCHAR(350) WITH SCHEMABINDING
AS 
BEGIN
   <snip>
   // Nothing insteresting in here. 
   //Just lots of SET @foo = REPLACE(@foo, '$', ''), etc.
END

Update 2: Solution

Yep, it was because i wasn't using the index on the view and had to manually make sure i didn't expand the view. The server is Sql Server 2008 Standard Edition. The full answer is below. Here's the proof, WITH (NOEXPAND) 如何加快这个索引视图?

是的，因为我没有在视图上使用索引，所以必须手动确保没有展开视图。服务器是Sql server 2008标准版。完整的答案如下。这是证明(NOEXPAND)

Thank you all for helping me solve this problem :)

谢谢大家帮我解决这个问题

6 个解决方案

#1

What edition of SQL Server? I believe that only Enterprise and Developer Edition will use indexed views automatically, while the others support it using query hints.

什么版本的SQL Server?我相信只有Enterprise和Developer Edition才会自动使用索引视图，而其他版本则使用查询提示来支持它。

SELECT a.PostId
FROM PostsCleanSubjectView a WITH (NOEXPAND)
WHERE a.CleanedSubject = 'Just-out-of-town' AND a.PostTypeId = 1

From Query Hints (Transact SQL) on MSDN:

来自MSDN上的查询提示(Transact SQL):

The indexed view is not expanded only if the view is directly referenced in the SELECT part of the query and WITH (NOEXPAND) or WITH (NOEXPAND, INDEX( index_value [ ,...n ] ) ) is specified.

只有在查询的SELECT部分和WITH (NOEXPAND)或WITH (NOEXPAND, INDEX, index_value[，…](n])指定。

#2

I see an @ sign in the query code in your execution plan. There's a string variable involved.

我在您的执行计划的查询代码中看到一个@符号。有一个字符串变量。

Sql Server has a NASTY behavior if the string variable's type does not match the type of the string column in the index. Sql Server will ... convert the whole column to that type, perform the fast lookup, and then throw away the converted index so it can do the whole thing again next query.

如果字符串变量的类型与索引中的字符串列的类型不匹配，那么Sql Server会有一个糟糕的行为。Sql Server将…将整个列转换为该类型，执行快速查找，然后丢弃转换后的索引，以便它可以再次执行下一个查询。

Simon figured it out - but here's more helpful detail: http://msdn.microsoft.com/en-us/library/ms187373.aspx

西蒙找到了答案——但是这里有更有用的细节:http://msdn.microsoft.com/en-us/library/ms187373.aspx

If a query contains references to columns that are present both in an indexed view and base tables, and the query optimizer determines that using the indexed view provides the best method for executing the query, the query optimizer uses the index on the view. This function is called indexed view matching, and is supported only in the SQL Server Enterprise and Developer editions.

如果查询包含对同时出现在索引视图和基表中的列的引用，并且查询优化器确定使用索引视图提供执行查询的最佳方法，那么查询优化器将使用视图上的索引。此函数称为索引视图匹配，仅在SQL Server企业版和开发人员版中支持。

However, for the optimizer to consider indexed views for matching or use an indexed view that is referenced with the NOEXPAND hint, the following SET options must be set to ON:

但是，为了优化器考虑索引视图以匹配或使用与no展开提示引用的索引视图，必须将以下设置选项设置为ON:

So, what's happening here is that indexed view matching is not working. Make sure you're using Enterprise or Developer editions of Sql Server (pretty likely). Then check your SET options according to the article.

这里所发生的是，索引视图匹配不起作用。确保您使用的是企业版或开发版本的Sql Server(很有可能)。然后根据文章检查你的设置选项。

#3

I recently built a large database containing hundreds of millions of call detail records and there are some functions I was using in queries and views that I turned into persisted computed columns. This worked out much better because I could index on the computed column.

最近，我构建了一个包含数亿个调用细节记录的大型数据库，并且在查询和视图中使用了一些函数，我将它们转换为持久计算的列。这样做会更好，因为我可以在计算列上建立索引。

I wasn't using SQL Enterprise though so I didn't get the opportunity to use indexed views. Is the indexed view supposed to be able to index the deterministic results of the UDF?

我没有使用SQL Enterprise，所以没有机会使用索引视图。索引视图是否应该能够索引UDF的确定性结果?

#4

I suspect it has to call that function for every row before it can do the comparison in your where clause. I'd expose subject, run the query checking against that directly and see how the times work out. I've generally seen a lot of slowness whenever I modify a value using a function and then use it in the where clause...

我怀疑它必须为每一行调用该函数，然后才能在where子句中进行比较。我将暴露主题，直接运行查询，查看时间如何计算。当我使用一个函数修改一个值，然后在where子句中使用它时，我通常会看到很多的延迟。

#5

What benefit are you looking for by using an indexed view? Is it not possible to properly index the table(s) themselves? Without good justification, you're adding complexity and asking the optimizer to deal with more database objects with less flexibility.

您希望通过使用索引视图获得什么好处?是否不可能对表本身进行适当的索引?如果没有充分的理由，您就增加了复杂性，并要求优化器处理更多具有更低灵活性的数据库对象。

Have you evaluated the same query logic with standard indexes?

您是否使用标准索引评估了相同的查询逻辑?

Mixing in UDF logic muddies things even more.

在UDF逻辑中混合会使事情变得更加复杂。

#6

If all you want is to persist the return value of a UDF, consider a persisted computed column rather than an indexed view.

如果您只想持久化UDF的返回值，那么考虑持久化计算列而不是索引视图。

#1