creating Index on a view
-
Hi Everybody.
I want to create index on a view But in my view there are some restrictions like outer joins and sub queries.
I am giving some of the code below:
select * from [case] left outer join address on [case].addressid=address.id
this is one of the part of my view.it is restricted to create an index on a view due to the outer join.
I want alternate solution for the above line code without outer join query.and i want the same result
If you know the solution Please reply back to me.
Thanking you.
-
The rules on indexed views are clearly stated in BOL, if you follow these you won't have any issues, imho attempting to use an outer join in an indexed view is a really bad idea.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Thank you so much for your reply.
Actually this is my existing database we are not created this database
we want to speed up the database.So we need to modify the database using indexed view.when i execute the queries its taking long time.
If you know alternalte solution or any modifications Please let me know
I am giving the complete View below:
==========================
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER VIEW dbo.vwCasesA
AS
SELECT [Case].Id AS Id, [Case].Created AS Created, [Case].SqFeets AS SqFeets, [Case].GraffitiType AS GraffitiClass,
(case
when [Case].GraffitiType = 0 then 'None'
when [Case].GraffitiType = 1 then 'Graffiti'
when [Case].GraffitiType = 2 then 'Tag'
when [Case].GraffitiType = 4 then 'Unknown'
when [Case].GraffitiType = 8 then 'Not Defined'
when [Case].GraffitiType = 7 then 'All Defined'
when [Case].GraffitiType = 16 then 'Multiple Image'
else 'Error' end 
AS GraffitiClassName,
[Case].SurfaceType AS SurfaceType,
(case
when [Case].SurfaceType = 0 then 'None'
when [Case].SurfaceType = 1 then 'Bridge'
when [Case].SurfaceType = 2 then 'Curb'
when [Case].SurfaceType = 4 then 'Electric Box'
when [Case].SurfaceType = 8 then 'Fence'
when [Case].SurfaceType = 16 then 'Fire Hydrant'
when [Case].SurfaceType = 32 then 'Garage Door'
when [Case].SurfaceType = 64 then 'Mailbox'
when [Case].SurfaceType = 128 then 'Pole'
when [Case].SurfaceType = 256 then 'Sidewalk'
when [Case].SurfaceType = 512 then 'Sign'
when [Case].SurfaceType = 1024 then 'Signal'
when [Case].SurfaceType = 2048 then 'Tree'
when [Case].SurfaceType = 4096 then 'Wall'
when [Case].SurfaceType = 8192 then 'Window'
when [Case].SurfaceType = 16384 then 'Other'
else 'Error' end)
AS SurfaceTypeName,
[Case].PhotoDateTime AS PhotoDateTime, [Case].Code AS Code, [Case].GpsLatitude AS GpsLatitude,
[Case].GpsLongitude AS GpsLongitude, [Case].ProcessedDateTime AS ProcessedDateTime, [Case].[Case] AS [Case],
[Case].RemovedDateTime AS RemovedDateTime,
[Case].CategoryType AS CategoryType,
(SELECT Graffiticategory.Category FROM GraffitiCategory WHERE GraffitiCategory.Id = [Case].CategoryType) AS CategoryTypeName,
[Case].ThreadType AS ThreadType,
[Case].Threatening AS Threatening,
(SELECT Team.Name FROM Team WHERE Team.Id = [Case].Threatening ) AS ThreateningName,
[Case].GraffitiClass AS GraffitiType,
(case
when [Case].GraffitiClass = 0 then 'Publicity'
when [Case].GraffitiClass = 1 then 'Roll Call'
when [Case].GraffitiClass = 2 then 'Threat'
when [Case].GraffitiClass = 4 then 'Territorial'
when [Case].GraffitiClass = 8 then 'Sympathetic'
when [Case].GraffitiClass = 16 then 'Not Defined'
else 'Error' end
 
AS GraffitiTypeName,ISNULL([Case].AbandentCrew,'' ) AS AbandentCrew,
ISNULL(Address.StreetAddress, '') AS StreetAddress, Address.StreetAddress2 AS StreetAddress2, Address.Country AS Country,
Address.State AS State, Address.City AS City, Address.ZIP AS ZIP, Address.CrossStreet AS CrossStreet,
(select Team.[Id] from Team inner join CaseTeam ON Team.[Id]=CaseTeam.GangId where Team.TeamType = 4 and CaseTeam.CaseId = [Case].Id) AS CaseDepartment
FROM [Case] INNER JOIN
Object ON [Case].Id = Object.Id LEFT OUTER JOIN
Address ON [Case].AddressId = Address.Id
WHERE (Object.Deleted IS NULL) AND [Case].GraffitiType < 9
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
============================
Thank you.
-
think you're taking the wrong approach, indexed views materialise the data and thus can be used to effectively create multiple clustered indexes on a table, however materialising the data ( in effect creating multiple copies of the data ) is actually not a good idea. If the query is complex and doesn't run well as a query then it's likely to make a poor view, bearing in mind views generally don't perform very well anyway. You might find effective indexing on the database to be the first step for performance.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Care to extend your comments on this one
?"views generally don't perform very well anyway"
-
Are you performing the left join because you don't always have a Case.addressID for a particular case row, meaning that the Case.AddressID could be NULL?
In that case, you could use a special address record with perhaps an ID of -1 and use that to mean "There is no address for this record", then you could replace the null in Case.AddressID with a -1.
Then you could use an inner join, which very well may enable the creation of an indexable view.
P.S. Kilroy was here!!!
heh
-
re: poor performing views. Well to be honest the concept of using views to speed up a database is somewhat bizare - A proc will outperform a view ( for a resonable query you can measure subjectively - so one that runs for over a minute say ) - if the underlying database structure isn't performing then putting a layer of materialised views over the top isn't really addressing the problem.
My main point about views is often they cannot be fully optimised for the queries that hit them, added to the fact often they are used to mask over complex queries which further degrade performance. The optimiser can sometimes do strange things with views too when they're involved in multi table joins.
In all cases I'm not considering simple select * from a couple of tables type views.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
That sounds more reasonable. Much like all cursors are evil... except those 2-3 cases
. -
Colin,
I'm not sure I follow this line of reasoning. It is exactly a poorly-performing query that makes a good candidate for an indexed view.
If there is a complicated bunch of transformations that need to happen to a set of data, then it would make more sense computationally to perform the transformation ONCE, materialize it and be done with it. Suppose the resultant data set needs to be queried a couple hundred thousand times a day. Would you suggest performing a computationally intensive bunch of transformations over and over again, or would it be better to do the expensive stuff once?
Of course there might be better ways, perhaps creating a daily load to anther database for analysis, or a complete redesign of a database, but that is not always a good business decision. And of course one needs to understand that a materialized view can seriously impact insert performance, but sometimes you sacrifice performance for ease of use.
Personally, I avoid using indexed views. I'll try to find a better solution, like you suggest. Sometimes, however, they are exactly the right thing.
jg
P.S. You spelt "materialized" wrong. (heh)
-
we hate 'z' in the UK - long live 's' - mind my spelling is suspect at the best of times!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply