Remove subquery from view to make it indexed.
I want to create an index for full text search.
the only problem is i, m facing subquery because index views do not allow subquery.
below is my request
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
I, m using a subquery to get the ingredients as a concatenation string from the ingredients table using STUFF.
Can someone please let me know how I can remove this subquery and have the ingredients as a content string.
Please let me know
Best regards Manish
source to share
The XML portion of the query will cause problems even if you manage to remove the subselection.
However, all is not lost. You can rewrite the view to a part that can be indexed and the other is cheaper but cannot. For example, you can 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
Depending on your data model, you may not need a group. This view can be indexed
And then write another view that is not indexed but replaces the original view
CREATE VIEW [dbo].[Demo]
SELECT ...
STUFF (...)
FROM [dbo].[Demo_Part]
As a meta answer, I would add that if you need to index such a view (and use DISTINCT), chances are your data model made a big mistake with the data model, or that your data access code is very inefficient. It all smells like you're trying to get around bad coding and modeling techniques.
source to share