Are Views Slow ?
-
Hi guys,
Here are there I hear somebody saying that Views should be avoided because they hurt database performance.
Is it true?
BOL says: "When an SQL statement references a nonindexed view, the parser and query optimizer analyze the source of both the SQL statement and the view and then resolve them into a single execution plan. There is not one plan for the SQL statement and a separate plan for the view".
According to this statement, Views wouldn't hurt performance, right?
Can you guys share your insights and experiences about Views?
Thanks a lot,
Luiz.
-
It they're written badly, they can be.
Personally, I've not found views to be a problem, unless there are layers upon layers of views. If views reference other views which reference still more views, the resulting SQL statement that the optimiser has to deal with can be very complex, without it appearing so to the developer.
If you use views, stick to a single layer, so that views only reference tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
(Non-indexed) views are just a programming abstraction, made for security and convenience.
They are as good or as bad as the code in them.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url] -
GilaMonster (2/26/2008)
It they're written badly, they can be.Personally, I've not found views to be a problem, unless there are layers upon layers of views. If views reference other views which reference still more views, the resulting SQL statement that the optimiser has to deal with can be very complex, without it appearing so to the developer.
If you use views, stick to a single layer, so that views only reference tables.
Agreed - it's more often the never-ending "one view calling another view calling a third view et..." syndrome that causes most problems I've seen. Because they're often written to "save keystrokes", at some times they end up making queries worse by hiding redundant computations/data fetches, etc....
Also - the more layers introduced like this, and (from my experience) the more likely you are to see the optimizer just toss in the towel and go at it "the hard way". Again - not the fault of the views per se so much as an issue with building something which ends up having 6 derived tables and a dozen sub-queries.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
We have very good experience using views. Gathering data together in a way over many tables for eg. processing data.
If your view is becoming slow consider using indexed views. You can put indexes on views which are schema bind. More information on indexed views can be found here: indexed views
And indeed:
If you use views, stick to a single layer, so that views only reference tables.
-
I have doubt.... Since Stored Procs can have recompiles and then take bad query plans, cant view also have that same issue?
-Roy
-
Roy Ernest (2/27/2008)
I have doubt.... Since Stored Procs can have recompiles and then take bad query plans, cant view also have that same issue?Since a view doesn't take parameters, you're much less likely to get an inappropriate query plan at the second execution.
John
-
John Mitchell (2/27/2008)
Roy Ernest (2/27/2008)
I have doubt.... Since Stored Procs can have recompiles and then take bad query plans, cant view also have that same issue?Since a view doesn't take parameters, you're much less likely to get an inappropriate query plan at the second execution.
John
I beg to disagree on that. A wrong query plan does not always happen due to Paramater sniffing. It can happen when the Statistics are out of date as well
-Roy
-
Where I've seen views slow things down is where a view that pulls a large number of columns from a large number of tables is used to grab a small number of columns from closely related tables.
For example, one proc I recently worked on had a view in it that referenced six tables and over 50 columns. The proc needed two of the columns, and they were from tables with a direct PK-FK relationship. Putting the select directly into the proc dramatically cut down the work the proc had to do, since it was no longer doing five joins and pulling 48 columns of unneeded data. It was able to switch to using a couple of index seeks instead of multiple table scans.
In cases where you're using all (or almost all) of the columns in a view on a regular basis, the view is going to be convenient and won't perform any worse than building the select directly in the proc/function/script. In cases like the one I outlined above, the view killed the performance.
So, as is often the case, it depends.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Roy Ernest (2/27/2008)
I have doubt.... Since Stored Procs can have recompiles and then take bad query plans, cant view also have that same issue?Non-indexed views aren't compiled separately, so technically - no they can't. They get compiled as part of whatever other statement uses them. Now the OUTER statement is just as likely as anything else to catch a bad optimizer decision, but that's not an issue with the view itself.
Indexed views on the other hand get handled essentially like a table, so again - there's no compile.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Ok.. That clarifies it a bit more. Thx
-Roy
-
I agree with the others... it depends on two things... how they're written and how they're called.
One of the worst things you can do is create aggragate columns in a view and then have criteria that references one of those columns... whole damned view has to resolve before the criteria can be applied.
A test for whether a view may become a severe performance problem is SELECT TOP 10 * FROM someview... if it doesn't return almost immediately, it could be a bad problem... again, depends on what you're trying to do. GUI, Batch, or ???
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Roy Ernest (2/27/2008)
I beg to disagree on that. A wrong query plan does not always happen due to Paramater sniffing. It can happen when the Statistics are out of date as well
Yes. That's why I said less likely, not impossible. But a view isn't any more susceptible to this than an ad hoc query is.
John
-
Thank you for all your answers.
I agree with what you guys told. But it seems that other people don't. See, for example, the discussion in http://www.sql-server-performance.com/tips/views_general_p1.aspx. Does anybody agree with it?
Luiz.
-
He's right on one point and wrong on yet another.
There is definitely some truth to the fact that there is some very minor amount of work required to pull the "metadata" of a view (i.e. what makes up the view). Of course - his example is wrong-headed, because that's precisely the worse example of what to use a view for. So - his point about performance is entirely lost, due to "using the wrong tool in the wrong place in the wrong way".
Building a view to bring back every column and every row of a table is, well, dumb, and goes against everything recommended about creating views.
Here's a viewpoint from MS on the matter:
http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx
The following directly contradicts his "views are optimized after first use":
(dealing with the case of a non-indexed view)
When a view is referenced in the FROM clause of another query, this metadata is retrieved from the system catalog and expanded in place of the view's reference. After view expansion, the SQL Server query optimizer compiles a single execution plan for the executing query. The query optimizer searches though a set of possible execution plans for a query, and chooses the lowest-cost plan it can find, based on estimates of the actual time it will take to execute each query plan.
Meaning - the T-SQL text of the view is "expanded" (i.e. dropped in a la find and replace) to the outer query instead of the name of the view, and THEN the optimizer goes to town. The view itself doesn't get "compiled" or "optimized".
As to indexed views:
After a unique clustered index is created on the view, the view's result set is materialized immediately and persisted in physical storage in the database, saving the overhead of performing this costly operation at execution time.
In plain language, that means the OUTPUT of the query is materialized and persisted (in the form of a table, or something very closely resembling it) and then indexed just like a table would.
So - in either case - the view itself has no part in compiliing/optimizing.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply