February 4, 2010 at 10:23 pm
We had a table with 11 million records which is highly transactional. We had new report requirement which i need to fetch static data from this table joining other table. I had plan to create a clustered index view for fetching data. Is there will be any performance bottle neck if i create an view?
February 8, 2010 at 7:13 am
First, just to be clear, it is important to distinguish between a VIEW and an INDEXED VIEW. Despite their great similarity in name, appearance and syntactical use in T-SQL, they are about as different as it is possible to be in physical implementation, semantics and performance considerations.
A VIEW is just an alias for a table expression. In short, when the SQL compiler encounters a VIEW in a query, it just replaces that VIEW with the VIEWs definition. The VIEW itself, being just an alias, has no data of its own and takes up no data storage space in your database.
An INDEXED VIEW, however, is much more like being able to add a second Clustered Index to a table and then give it a name that makes it look like another table or a VIEW. Sql Server actually makes a copy of all of the data in your INDEXED VIEW and then magically maintains it to be in synch with its source tables. So an INDEXED VIEW can take up a substantial amount of space (depending on its column definitions and its total number of rows). Thus my point (so far): they appear very similar, but in fact are very different, so its important to be clear which you are talking about when discussing performance.
OK, so back to your question:
Is there will be any performance bottle neck if i create an view?
The answer is, if you mean VIEW than No there will be no performance impact because a VIEW is just a syntactical alias, it has the exact same performance that you would get from just writing its definition directly into every query that used it, so no performance difference.
However, if you meant INDEXED VIEW, then yes, it will likely have a substantial performance impact, however, you have not given us nearly enough information to tell if it will be a positive or a negative impact, or in what areas. For more about how to give us this information, see this article by a really smart person: http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 8, 2010 at 7:26 am
The link above is specifically for performance problems, for performance questions (i.e., less specific) or SQL questions in general, you may also want to follow the advice in this article[/url] by another really smart person.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 16, 2010 at 5:10 pm
I agree with your explanation, a very good one I might add.. and also a 64 cents comment of mine would be that the regular view is also useful in associating with the user permissions and security in order to maintain that and keep them away from the physical tables.
Cheers,
John Esraelo
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply