Hello,
This post is more of asking suggestion on optimizing views.
Does anyone have a checklist or list of things to check while tuning complex view.
Please share any good strategies you have or reading articles which can help more on this.
I am looking at below things. I know things change based on expected data so any general advice is also appreciated.
select * from [dbo].[ViewName]
set statistics time off
Source for above code.. (https://stackoverflow.com/questions/31941690/how-do-i-get-the-execution-time-of-an-view)
As well as a visual check looking for poor practices (the ones you have mentioned are not necessarily poor, by the way), you should be analysing the actual execution plan.
I'd be looking for triangular joins, non-sargable predicates, scalar UDFs, joins and predicates without supporting indexes, unnecessarily complex expressions ... the list goes on, and a lot comes down to experience.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 25, 2020 at 4:17 pm
Also it should be noted that trying to optimize a query by rearranging the join order in the vast majority of cases will not help. SQL Server's optimizer will try to re-evaluate all the potential join orders each time the query is compiled anyway, so focus join order on what makes the query more readable and logical.
June 25, 2020 at 7:55 pm
One thing I have seen a lot is using the *wrong* join...often times I see code that uses a left outer join when an inner join is appropriate. This will often cause SQL Server to ignore optimizations related to the outer table - and instead of getting a hash or merge join a nested loop join is used.
Another thing to consider is that a view is not materialized in any way - the code from the view will be incorporated into the calling query and then optimized. Any timings based on selecting directly from the view are meaningless for the queries using that view - and, depending on how the view is structured - joins and outer/cross apply's in the view can often be factored out of the calling query if none of the relevant columns from those joins/apply's are utilized in the calling query.
Cross Joins are not an issue - if the requirement for that query is to use a cross join. More important are *accidental* cross joins and incorrect joins (those not specifying the full PK when the PK is a multi-column primary key).
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
June 26, 2020 at 11:46 am
Since a view is a definition of a query, but is not the actual query that is run when the view is accessed, tuning a view is quite difficult. For example, Let's say you have a view that accesses five tables. You run a query against the view that pulls columns from only two of those tables. The optimizer does a thing it calls simplification. It tosses the tables it doesn't need to satisfy a query, so if you look at the execution plan for the query, you'll see that only the two tables needed are accessed (just an example, it could have to access three of the tables, depending on the foreign keys, etc., you get the idea).
The single most important thing you can do for views is do not, DO NOT, nest them, meaning, put views inside of views. Also, do not, DO NOT, join views, meaning, treat views as tables and start joining them together. These two common code smells lead to all sorts of major performance issues because you're demanding the optimizer to unpack increasingly complex queries (the five tables of this view, the five tables of that view, and the five tables of the third view, some of which overlap, some don't, some are being accessed, some aren't, etc., etc.).
Remember, above all when dealing with views, views are just a query. They do not define data storage. That's what tables are. Further, the view definition is not necessarily how the optimizer is going to see things.
"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
June 29, 2020 at 1:20 pm
...
I'd be looking for triangular joins...
I've not heard the phrase triangular joins before. Does it refer to the following situation?:
Table A is joined to Table B
Table A is joined to Table C
Table B is joined to Table C
It would cause extra work for the optimiser as it has to remove one of the joins from the query?
June 29, 2020 at 2:24 pm
Phil Parkin wrote:...
I'd be looking for triangular joins...
I've not heard the phrase triangular joins before. Does it refer to the following situation?:
Table A is joined to Table B
Table A is joined to Table C
Table B is joined to Table C
It would cause extra work for the optimiser as it has to remove one of the joins from the query?
I see where you are coming from, but that's not it (the situation you describe is not necessarily an issue).
Instead, the 'triangular' bit refers to the 'shape' of the data which is returned by the join. The following article makes things very clear:
https://www.sqlservercentral.com/articles/hidden-rbar-triangular-joins
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 29, 2020 at 3:02 pm
Thanks for your reply and information.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply