May 6, 2010 at 9:06 pm
Hi,
What is better : To join every time multiple tables or to create a view with the joins and use the view.
The question is with respect to performance what would be better?
May 6, 2010 at 9:52 pm
Probably neither - since the view will be expanded into the calling query there wouldn't be any difference in the actual execution plan.
I would create a view if the logic contained in the joins and/or where clause are used by multiple processes. If only for a single query, I wouldn't create a view.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 6, 2010 at 9:55 pm
Thanks,
" ..there wouldn't be any difference in the actual execution plan" was what I was looking for.
That means that the data or query plan is not cached for views and wont have any additional advantage. Am I right in assuming so?
May 6, 2010 at 10:00 pm
Yes, you are right in assuming that views don't have cached plans. The queries that use the views will have a cached plan - but not the view itself.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 6, 2010 at 10:14 pm
Interesting.
Thanks again.
May 6, 2010 at 10:26 pm
But if you need an indexed view, then I think you should go for proper defined view instead of joining tables everytime. I think it depends upon the situation.
May 6, 2010 at 10:34 pm
My tables in concern dont have indexes as it was causing delays because of frequent inserts and updates happening on that table.
Question 1. If I do create a view, can the view be indexed even if the tables are not indexed?
Question 2. Will the indexed view be better than the join in such case?
Thanks
May 7, 2010 at 1:14 am
Question 1. If I do create a view, can the view be indexed even if the tables are not indexed?
Yes you can.
Question 2. Will the indexed view be better than the join in such case?
Like I said before, it depends. If you are performing simple queries without aggregates and grouping then you are right. There will hardly any Performance gain. Only plus point will be that your query will look simple. But if you are creating View based on Aggregates and grouping, then it will be better for you to go for Indexed Views. This will definitely give you performance gain.
May 7, 2010 at 6:52 am
Two things, using views is fine, but if you starting joining views against views, you can completely overwhelm the ability of the optimizer to simplify your queries to only use the tables needed by the query.
The other thing, you're querying tables that don't have indexes, at all? You're going to hit major performance issues AND you're going to cause a great deal of blocking for your inserts and updates. It sounds to me as if you need to reexamine which indexes were created on your tables. A well constructed clustered index will actually aid inserts and updates, not hurt performance.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 9, 2010 at 3:05 pm
Thanks for all the feedback. I would be putting appropriate indexes and if the view is reusable then would create the view.
Thanks again.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply