September 21, 2010 at 1:55 pm
Views are very slower than tables while querying and also the execution plans are choosen differently, how can we trick views so that it performs like a table.
September 21, 2010 at 2:21 pm
A view is not a table. Views can be complicated, joining multiple tables with 'where' clauses. They need to be well written just as a query does.
September 21, 2010 at 3:42 pm
Tara-1044200 (9/21/2010)
Views are very slower than tables while querying and also the execution plans are choosen differently, how can we trick views so that it performs like a table.
I'm sure if you could trick the views so the performed like tables, then MS would have done it a long time ago.
I try avoid views if I can and use them carefully when I don't have any other option.
If you must create views, make sure they are optimised just like any other SQL query.
It's also a really good idea to use a naming convention that indicates its a view, as one of the worst parts of later debugging is when you (someone else who doesn't know your code) see a select that has some strange behaviour, and after digging through 2 or 3 layers you see that half the objects are views not tables.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
September 21, 2010 at 4:05 pm
Tara-1044200 (9/21/2010)
Views are very slower than tables while querying and also the execution plans are choosen differently, how can we trick views so that it performs like a table.
A view is nothing but a stored query. You do not want to use a view when you can use a table, use views only when needed.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 21, 2010 at 8:42 pm
Tune a view like you would tune a query.
Depending on the circumstance, your view may be a candidate for a clustered index on the view.
I only use views when necessary (as the others have said).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 22, 2010 at 1:38 am
Tara-1044200 (9/21/2010)
the execution plans are choosen differently.
No , view has same exec plan as it has for inner incorporated query.sql optmizer treats both view and query same while analyzing the resources like cpu, io, memory.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 22, 2010 at 6:20 am
Bhuvnesh (9/22/2010)
Tara-1044200 (9/21/2010)
the execution plans are choosen differently.No , view has same exec plan as it has for inner incorporated query.sql optmizer treats both view and query same while analyzing the resources like cpu, io, memory.
Not quite Bhuvnesh. It really depends on the query against the view and how the view is being used in that query. SQL Server is pretty smart. For example, you can have a view that joins together three tables. Then you can write a query against that view that only access data from one table. Depending on the query, the optimizer will very astutely ignore the other two tables and only reference the single table. That's a positive example. You can also see negative examples when views are used in a query where a view is joined to a view is joined to a view,etc. Then, what frequently happens is that, regardless of the tables being accessed, SQL Server doesn't have time to break down the execution plan to the point where it can eliminate tables, so it includes everything, whether it's needed or not.
For the original question, views are meant as a mechanism for masking structure, either to simplify access or provide a security layer. If you're not using the view for one of these two mechanisms, you really shouldn't be using them. If you are using them for those mechanisms, you need to make the view as simplistic as possible in order to help the optimizer when it makes decisions. Since a view is just a query against a table (or tables), there's no reason why it should be appreciably slower than any other query against a table or tables, when used appropriately. If you're seeing radically different performance, you're probably moving outside the realm where a view ought to be used.
"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
September 22, 2010 at 6:42 am
Thanks grant, can you brief about the execution plan difference between these two. can we have the different plan for a view and query ( included into the view ) ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 22, 2010 at 6:48 am
Bhuvnesh (9/22/2010)
Thanks grant, can you brief about the execution plan difference between these two. can we have the different plan for a view and query ( included into the view ) ?
Briefest possible answer: It depends.
But seriously, yeah, it comes down to the optimizer. If the view is complex, the optimizer has less time to pick it apart and you may end up with an overly complex or even horribly incorrect execution plan. But, it really does depend on the view and the query we're talking about. There isn't a hard & fast rule either way.
"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
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply