我怎样才能加速这个索引视图? [英] How can i speed up this Indexed View?

问题描述

我有一个简单的索引视图.当我查询它时，它很慢.首先，我向您展示架构和索引.然后是简单的查询.最后是查询计划屏幕.

更新:本文底部的解决方案证明.

架构

这是它的样子:-

使用 SCHEMABINDING AS 创建视图 [dbo].[PostsCleanSubjectView]选择 PostId、PostTypeId、[dbo].[ToUriCleanText]([Subject]) AS CleanedSubject来自 [dbo].[帖子]

我的 udf ToUriCleanText 只是用空字符替换了各种字符.例如.用 '' 替换所有 '#' 字符.

然后我为此添加了两个索引:-

索引

主键索引(即聚集索引)

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

和非聚集索引

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

现在，它有大约 25K 行.没什么大不了的.

当我执行以下查询时，它们都需要大约 4 秒.跆拳道?这应该......基本上是即时的！

查询 1

SELECT a.PostIdFROM PostsCleanSubjectView aWHERE a.CleanedSubject = 'Just-out-of-town'

查询 2(添加另一个 where 子句项)

SELECT a.PostIdFROM PostsCleanSubjectView aWHERE a.CleanedSubject = 'Just-out-of-town' AND a.PostTypeId = 1

我做错了什么?UDF 搞砸了吗?我认为，因为我已经索引了这个视图，所以它会被实现.因此，它不必计算该字符串列.

这是查询计划的屏幕截图，如果有帮助的话:-

另外，注意它使用的索引吗?为什么使用该索引?

那个索引是...

在 [dbo].[Posts] 上创建非聚集索引 [IX_Posts_PostTypeId_Subject]([PostTypeId] ASC，[主题] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,SORT_IN_TEMPDB = 关闭，IGNORE_DUP_KEY = 关闭，DROP_EXISTING = 关闭，ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]走

是的，大家有什么想法吗?

更新 1:为 udf 添加了架构.

创建函数 [dbo].[ToUriCleanText](@Subject NVARCHAR(300))使用架构绑定返回 NVARCHAR(350)作为开始<剪断>//这里没有什么有趣的.//只是很多 SET @foo = REPLACE(@foo, '$', '') 等结尾

更新 2:解决方案

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

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

解决方案

SQL Server 是什么版本?我相信只有企业版和开发者版会自动使用索引视图，而其他版本则支持使用查询提示.

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

来自 MSDN 上的查询提示 (Transact SQL)::><块引用>

仅当在查询的 SELECT 部分直接引用视图并指定 WITH (NOEXPAND) 或 WITH (NOEXPAND, INDEX( index_value [ ,...n ] ) ) 时，索引视图不会展开.

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.

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 ''.

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.

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

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.

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)

Thank you all for helping me solve this problem :)

解决方案

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.

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:

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.

这篇关于我怎样才能加速这个索引视图?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！