从视图中删除子查询以使其成为索引视图 [英] remove subquery from view to make it Indexed view

问题描述

我想为全文搜索创建一个索引视图.

i want to create a Index View for full text search.

我面临子查询的唯一问题，因为索引视图不允许子查询.

the only problem i,m facing with subquery, because index views does not allow subquery.

下面是我的查询

ALTER VIEW [dbo].[Demo] with SCHEMABINDING  AS
select distinct a.ID,a.Title, a.Description ,b.Name as Recipe, c.Name as Taste , d.Name as CuisineType,
STUFF((SELECT ',' + Name FROM dbo.Ingredients where ID in (select IngredientID from dbo.listingIngredients 
where listingid = a.ID ) FOR XML PATH('')), 1, 1, '') as Ingredients
from dbo.Listing as a 
inner join dbo.RecipeType b on a.RecipeTypeID = b.ID
inner join dbo.taste c on a.tasteID = c.ID
inner join dbo.CuisineType d on a.CuisineTypeID = d.ID
inner join dbo.listingIngredients e on a.ID = e.listingID
GO

我使用子查询从使用 STUFF 的成分表中获取成分作为连接字符串.

I,m using subquery to get ingredients as concatenate string from Ingredients table using STUFF.

有人可以让我知道如何删除此子查询并将成分作为内容字符串.

can some one please let me know how can i remove this subquery and have ingredients as contented string.

请告诉我

问候男子气概

推荐答案

查询的 XML 部分会导致问题，即使您确实设法删除了子选择.

The XML part of the query will cause problems, even if you did manage to remove the sub-selected.

然而，一切都没有丢失.您可以将视图重写为可以编入索引的部分和另一个更便宜但不能编入索引的部分.例如，您可以这样写:

However, all is not lost. You could rewrite the view into a part that can be indexed and another part that is cheaper, but can't. For example, you could write:

ALTER VIEW [dbo].[Demo_Part] with SCHEMABINDING  AS
select a.ID,a.Title
, a.Description 
, b.Name as Recipe
, c.Name as Taste 
, d.Name as CuisineType
, e.name
from dbo.Listing as a 
inner join dbo.RecipeType b on a.RecipeTypeID = b.ID
inner join dbo.taste c on a.tasteID = c.ID
inner join dbo.CuisineType d on a.CuisineTypeID = d.ID
inner join dbo.listingIngredients e on a.ID = e.listingID
GROUP BY a.ID,a.Title
, a.Description 
, b.Name as Recipe
, c.Name as Taste 
, d.Name as CuisineType
, e.name

根据您的数据模型，您甚至可能不需要 group by.此视图可以编入索引

Depending on your data model, you may not even need the group by. This view can be indexed

然后编写另一个未编入索引但替换原始视图的视图

And then write another view that is not indexed, but which replaces your original view

CREATE VIEW [dbo].[Demo]
SELECT ...
 STUFF (...)
FROM [dbo].[Demo_Part]

作为一个元答案，我想补充一点，如果您需要索引这样的视图(并使用 DISTINCT)，很可能您的数据建模者在数据模型上犯了很大的错误，或者您的数据访问代码是非常低效.关于这一切的一切都像是在努力解决糟糕的编码和建模实践.

As a meta-answer I would add that if you need to index a view like this (and use the DISTINCT), chances are that your data modeller made a pretty big mistake with the data model or that your data access code is very inefficient. Everything about this smells like you are trying to work around poor coding and modelling practices.

这篇关于从视图中删除子查询以使其成为索引视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！