views based on other views...performance suffers?

Question

views based on other views...performance suffers?

Lowell

SSC Guru

Points: 323518
More actions
January 10, 2007 at 7:06 am

#117055

Don't need specific fixes, this is more of a theory question.
I thought it would be a great idea to create a view that pulls in lots of information together, and then base other views off of that. I was trying not to have the same structure of joins/subselects copied multiple times, so i thought centralizing the code would be helpful.
I created the view, and a view based on that view to join other information...I've found that the outer view is slower than if i had left the sub select in place...
Create view BASEVIEW AS select ....
select * from some table left outer join BASEVIEW on .....is slower than
select * from some table left outer join (select....) AS BASEVIEW on ....
I'l be fiddling with adding indexes and performance tuning it further, as I saw a index tuning recommendation to add an additional index when i ran this thru, but i thought the view, being compiled, would at least not penalize me, if not improve performance.
I can give examples and query plans and all that, but my question is has anyone else seen a similar issue with views based on other views?

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

using views to mask underlying tables/complexity is valid although rarely performs very well, adding a hiearchy of views over that will rarely perform well at all. No doubt you'll get a suggestion about using indexed views - this is sort of ok but physically duplicates data so can be counter productive. I've found taking queries againt views and converting them to sp calls gets tremendous performance improvements ( on complex views of 6 tables or more )

Personally I hate views and would actually prefer to create physical data structures to satisify such queries, not always possible, I've seen so much manipulation of data ( usually for reporting and within staging databases ) using views on joined views and joined views where one user could bring a stonking 8 way box to it's knees.

Sorry not very helpful!

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Lowell SSC Guru Points: 323518 More actions · Answer 2

actually very helpful Colin thanks for the response; I had suspected views of views sucked, glad to hear a confirmation.

I was hoping to simply prune the complexity of some of the SQL's that grab some of the data here; Unfortunately, the views in question are rollups of information, with sub selects, so they don't lend themselves to an indexed view;

I'll try a stored procedure against a subselect, and a stored procedure against the data as a view and see if ther's much of a performance difference.

Thanks!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!