June 18, 2013 at 3:55 am
Hi All,
I just wanted to know whether retrieving data from a view will be faster than directly querying from table.
Say suppose if a view is created by joining a number of fact tables(Records greater than 100 million) with dimensional tables.
Will selecting the records be faster than querying from the actual tables.
Request your views on the same.
June 18, 2013 at 4:02 am
No. The performance will remain the same as SQL Server will break the query into individual parts before executing.
If the view in indexed, you might see performance benefit as the table is actually materialized.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 18, 2013 at 4:03 am
If it is Materialised then i thought it will defenetly increase the performance.
June 18, 2013 at 4:05 am
Nope, exactly the same. The first thing the optimiser will do when you execute the query against the view is expand the underlying definition and run that.
You can however create indexes on views if they meet the stringent requirements, then it can be a lot faster, at the expense of inserts/updates to the base tables also having to update this index. If you're not using Enterprise Edition, you also need the WITH(NOEXPAND) hint to make use of indexed views.
June 18, 2013 at 4:18 am
Ananth@Sql (6/18/2013)
If it is Materialised then i thought it will defenetly increase the performance.
I wouldn't say 'definitely'. Depends on what the query does, what the view is and what's making it slow. Plus there are all sorts of potential performance effects on other queries from a materialised view and lots and lots of restrictions to the creation of indexes on a view.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 18, 2013 at 5:45 am
Thank you All for your suggestions.
If there are no performance difference between a view and querying from a fact table then What are the advantages of creating views in database?
Thanks in Advance
June 18, 2013 at 6:26 am
kk.86manu (6/18/2013)
Thank you All for your suggestions.If there are no performance difference between a view and querying from a fact table then What are the advantages of creating views in database?
Thanks in Advance
I rarely use views, but when I do, its usually for the reason to encapsulate situations that are often used. I.E. The join between Sales Order Header and Sales Order Detail.
Or, power users doing select queries to make it easier for them.
But again, its pretty rare.
Obviously I dont buy into the old DBA theory that EVERYONE must access a DB through a view, circa 1995.
June 18, 2013 at 6:28 am
It depend on the query(what you are executing?). But definitely if table is having index and view is having huge data and you run a query without index on views performance will be slow. Creating index on view improves query performance. I suggest you do side by side comparision in test environment before making any conclusion.
Well View will help to reduce the number of joins and provide consolidated result/output.
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
June 18, 2013 at 1:40 pm
free_mascot (6/18/2013)
It depend on the query(what you are executing?). But definitely if table is having index and view is having huge data and you run a query without index on views performance will be slow. Creating index on view improves query performance. I suggest you do side by side comparision in test environment before making any conclusion.Well View will help to reduce the number of joins and provide consolidated result/output.
HTH
It gives the appearance of reducing joins. In actuality it does not. It does make it easier for those less knowledgeable to access the data.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply