May 28, 2012 at 7:46 am
I suspect that I'm going to get the standard "It depends" to this, but I'll try anyway:
If I have a Select statement that does several joins, is it better to join to a specifically tailored view for each occurrence, or is it better to re-use a view several times, even if the view may return more fields than I need?
If I make a special view for each join, listing only the fields needed in that particular case, I will have no excess ballast, but SQL Server will have to realize each view separately.
If I use the same view several times in a complex join, I may dredge up more data than I need, but possibly SQL Server can realize the view once, conserving processing power, and extract only the fields it needs each time the view is utilized in the final query.
Is this information present in the execution plan, whether a view is reused, or must I look at overall performance, with no clue as to HOW the components are assembled?
And in a probably vain attempt to head pedants off at the pass, this is a question about general principles, not about a specific query, so please spare me the 'Post your SQL' spankings. I do not yet have specific queries that use one method or the other, and I am not looking for tuning tips with a particular query. I am trying to gain a better understanding of how SQL Server assembles components, so that I may have a better chance of writing them correctly in the first place.
May 28, 2012 at 7:58 am
so please spare me the 'Post your SQL' spankings
Not a good way to ask for help or advice!! 😉
Just my two cents but I really don't like nesting views in a query, I would much prefer to write the SQL from scratch rather than do this..
I've come across nightmares where devs have started doing this and over time it has just got out of hand and have actually exceeded the nested view limit!
Also becomes very hard to maintain.
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
May 28, 2012 at 8:19 am
Andy Hyslop (5/28/2012)
so please spare me the 'Post your SQL' spankings
Not a good way to ask for help or advice!! 😉
Maybe not, but I often get that in response to a question like this. I get the impression that the responder hasn't read my question at all, just skims it, recognizes a few key words and immediately slams me for what he thinks is a breach in netiquette. When I have a specific question about a specific query, I certainly do post it, but here I'm looking for advice on general principles. Probably you're right, though, I should not start off with this - rather, do as I have in the past: just ignore the people who pointlessly berate me but have nothing helpful to say.
Just my two cents but I really don't like nesting views in a query, I would much prefer to write the SQL from scratch rather than do this..
I've come across nightmares where devs have started doing this and over time it has just got out of hand and have actually exceeded the nested view limit!
Also becomes very hard to maintain.
You don't use views in your statements at all? Everything directly from the tables? Doesn't that get extremely messy sometimes? I thought that views were a good way to simplify complex expressions, and I think I've read that SQL Server can cache parts of frequently used views. If you code everything from scratch every time through, don't you give up such benefits?
How do you deal with complicated queries? CTEs? If so, I suppose the same question applies there: make a separate CTE for each instance in the final query, or reuse a CTE, even if it has more fields than needed in a particular clause?
I can certainly see the point about nesting complexity getting out of hand, but I am the only developer on this project, so that shouldn't be an issue for me. One of the things I have done here is to make ALL my application code go through views - nothing touches a table directly. Seems to cut down on problems when I make a design change in a table.
May 28, 2012 at 8:53 am
Don't get me wrong we do use views where appropriate I suppose I'm just a little more cautious these days (and maybe a little jaded ;-)) when it comes to using / nesting after trying to debug some SQL that had too many
Over time they had been changed bit by bit and no longer represented the original spec and as a consequence everything was incorrect..
As a general rule I use them as a presentation layer for the business to report on and not much more, so in essence they are a one stop shop and I don't nest them within another query.
If I find that we are constantly using the same complex calculations I will write these into reporting tables or the Data Warehouse and have these performed within the ETL's (which may not be appropriate in your case as it sounds like its from an application point of view)
I agree that Views can certainly have performance benefits for complex queries especially if they are indexed ect
Not saying that this way is theright way at all and I'm sure others will offer other solutions / opinions based on their experiences.
I would just say don't create the monstrosity that I had to debug for yourself :w00t:
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
May 28, 2012 at 8:58 am
pdanes (5/28/2012)
If I make a special view for each join, listing only the fields needed in that particular case, I will have no excess ballast, but SQL Server will have to realize each view separately.
What you mean by 'realize the 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
May 28, 2012 at 9:00 am
GilaMonster (5/28/2012)
pdanes (5/28/2012)
If I make a special view for each join, listing only the fields needed in that particular case, I will have no excess ballast, but SQL Server will have to realize each view separately.What you mean by 'realize the view'?
I mean assemble the 'table' that results from executing the code in a view.
May 28, 2012 at 9:06 am
The term you're looking for is 'materialise'
SQL doesn't materialise views when running queries. Views don't have execution plans and are never executed alone. As part of the early parsing phase, SQL will replace a view's name with it's definition and then bind, optimise and execute the resultant query.
So this:
CREATE VIEW MyView AS SELECT a,b,c FROM MyTable
GO
SELECT * FROM MyView Where C > 10
becomes during early parsing phase
SELECT * FROM (SELECT a,b,c FROM MyTable) AS MyView WHERE C > 10
and gets bound, optimised and executed as such
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
May 28, 2012 at 9:08 am
pdanes (5/28/2012)
I suspect that I'm going to get the standard "It depends" to this, but I'll try anyway:If I have a Select statement that does several joins, is it better to join to a specifically tailored view for each occurrence, or is it better to re-use a view several times, even if the view may return more fields than I need?
If I make a special view for each join, listing only the fields needed in that particular case, I will have no excess ballast, but SQL Server will have to realize each view separately.
If I use the same view several times in a complex join, I may dredge up more data than I need, but possibly SQL Server can realize the view once, conserving processing power, and extract only the fields it needs each time the view is utilized in the final query.
Is this information present in the execution plan, whether a view is reused, or must I look at overall performance, with no clue as to HOW the components are assembled?
And in a probably vain attempt to head pedants off at the pass, this is a question about general principles, not about a specific query, so please spare me the 'Post your SQL' spankings. I do not yet have specific queries that use one method or the other, and I am not looking for tuning tips with a particular query. I am trying to gain a better understanding of how SQL Server assembles components, so that I may have a better chance of writing them correctly in the first place.
SQL Server does not materialize views - they are more like in-line macros. When you reference a view - it will be expanded into the query and then optimized.
Creating a view to limit the number of columns available to the query is rather useless - and if that is the only reason for creating the view then I wouldn't bother.
If I am creating a stored procedure, I won't use a view unless that view has complex logic involved that I wouldn't want to repeat in my procedure. For example, complex case statements in the select - complex join requirements between tables, etc...
Views are good when you need to present a specific set of data to an application, developer, report writer, etc... This allows them to simplify their coding and you manage the logic in the view. However, that could also be done using a stored procedure - which is generally my preference.
In some systems, I have end users that access the system with Access, Excel, etc... For those users, we build the views to hide the join logic to simplify their queries.
The real problem that you see with views is that they get nested. Someone creates a view for a purpose, and someone else decides the view works well for their purpose and creates another view, which then is used by the next person, etc... The original view is then modified because the original purpose has changed - and all downstream views could be negatively impacted. Not to mention the performance hit of joining views to views, etc...
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 28, 2012 at 9:14 am
Andy Hyslop (5/28/2012)
Don't get me wrong we do use views where appropriate I suppose I'm just a little more cautious these days (and maybe a little jaded ;-)) when it comes to using / nesting after trying to debug some SQL that had too many
Okay, that makes more sense - I misunderstood your earlier statement.
If I find that we are constantly using the same complex calculations I will write these into reporting tables or the Data Warehouse and have these performed within the ETL's (which may not be appropriate in your case as it sounds like its from an application point of view)
Yes, these are all fairly simple inventory-style databases, used for tracking collection items in a museum. Most of the activity is simple searching and viewing, almost everything else is inserting new records, with very occasional updates and even less frequent reports (like once per year, for some obscure government requirement).
I agree that Views can certainly have performance benefits for complex queries especially if they are indexed ect
That's a relief - I thought I might be drifting down some dead-end path, that's easy to code but dreadful on performance.
I would just say don't create the monstrosity that I had to debug for yourself :w00t:
Yeah, thanks. I -have- created a few such Goldbergisms over the years, and suffered the consequences - now I try to be very careful from day one, about just how complex something is becoming. When I start having difficulty visualizing the entire process, end to end, it's time for a review.
May 28, 2012 at 9:18 am
Sorry, need to set something straight...
Indexed views can have performance benefits. Normal views cannot.
They can make queries easier to read, easier to write, but they cannot improve performance because they are just saved select statements and they are parsed out of the queries well before optimisation occurs so the optimiser never even sees the 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
May 28, 2012 at 9:18 am
Andy Hyslop (5/28/2012)
so please spare me the 'Post your SQL' spankings
Not a good way to ask for help or advice!! 😉
...
Yes indeed! Nothing forestalls a good spanking from us arrogant Illuminati like a snarky opening poke in the face. Good show, chap! 😛
[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]
May 28, 2012 at 9:33 am
GilaMonster (5/28/2012)
The term you're looking for is 'materialise'
Sorry, sloppy terminology. I do try to use correct wording, but I slipped on this one.
SQL doesn't materialise views when running queries. Views don't have execution plans and are never executed alone. As part of the early parsing phase, SQL will replace a view's name with it's definition and then bind, optimise and execute the resultant query.
Okay, so it seems that for readability's sake, I would be well served by using views, rather than repeating the definition of a view in my own TSQL, as long as the view is just a straight select. But what about views that contain logic? If a view contains a computed field, but the computed field is[/b] used in one clause, but not used in another clause in the 'master' query that calls the view, is SQL Server smart enough to NOT execute the field computations in the instance where they are not needed? Or should I build a separate view that doesn't contain the computed field?
For instance, to tweak your example a little, suppose I coded it this way:SELECT a,b FROM (SELECT a,b,c,d FROM MyTable) AS MyView WHERE C > 10Will SQL Server not even materialize the field [d], since it is not used anywhere? And in particular, if [d] is the result of a computation instead of a field name, will it refrain from doing the computations?
Edit: How did you get the 'greater than' sign into your posted SQL? Everything I try gets turned into an ampersand-gt-semicolon.
May 28, 2012 at 10:56 am
pdanes (5/28/2012)
For instance, to tweak your example a little, suppose I coded it this way:SELECT a,b FROM (SELECT a,b,c,d FROM MyTable) AS MyView WHERE C > 10Will SQL Server not even materialize the field [d], since it is not used anywhere? And in particular, if [d] is the result of a computation instead of a field name, will it refrain from doing the computations?
No, it won't, because during simplification (also part of parsing) that query would be further reduced to just
SELECT a,b FROM MyTable WHERE C > 10
You can see that if you run that query and look at the execution plan
However that simplification itself can take time. Nest your views too much, and you'll see parsing and optimisation time go through the roof as SQL works out what it does and does not need from the views.
http://www.scarydba.com/2009/04/24/unpacking-the-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
May 28, 2012 at 12:45 pm
GilaMonster (5/28/2012)
pdanes (5/28/2012)
No, it won't, because during simplification (also part of parsing) that query would be further reduced to just...You can see that if you run that query and look at the execution plan
However that simplification itself can take time. Nest your views too much, and you'll see parsing and optimisation time go through the roof as SQL works out what it does and does not need from the views.
Great, thank you Gail. I think it's clear enough now that I can proceed with a reasonable degree of confidence.
BTW, you certainly know this, but I just discovered that SSMS, when tasked with displaying the execution plan, also suggests indexes that it thinks will help. Took a couple of iterations, since when I implemented one suggestion and ran the query again, it immediately suggested another, different index. Each round was a considerable improvement over the previous, but the final suggested index took the execution time from almost 30 seconds to zero. I wish I was as smart as the optimizer ... 🙂
May 28, 2012 at 12:49 pm
Please, please don't trust those index suggestions. They're made on the basis of a single query and if you follow them blindly without testing or considering the rest of the workload you can get so many near duplicate indexes that it's not funny.
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
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply