SQL Server 2014 Checklist for Performance

Question

SQL Server 2014 Checklist for Performance

Viewing 6 posts - 46 through 50 (of 50 total)

You must be logged in to reply to this topic. Login to reply

jeffem Ten Centuries Points: 1232 More actions · Answer 1

pkapas (12/29/2014)
Quick question. I read this in the article: "Don't nest the views and join views to views"
If you have to rely on getting data from a previous view in order to proceed with a query in a following view, is there a better way of doing that? I'm new to SQL Server, so I'm still learning. I have a query that relies on a couple of other views, and its about a 5-6 level nested view.

Obviously, I can't speak to why the OP posted this.

I advise my developers against it (but it's not a rule), because often multiple views can use some of the same tables. What can happen is the same table(s) being joined in multiple times, potentially significantly decreasing performance.

Stylistically, some developers dislike keeping too much logic in views because they have to dig into views every time they want to fix a query, just to understand the logic of the views. I don't personally have a problem with them at all, but I mostly only use them as a convenience in a highly normalized environment or as a layer of abstraction in an application back-end.

Paul Brewer SSCrazy Points: 2955 More actions · Answer 2

Have a look at the views in a microsoft dynamics CRM database. Views upon views upon views. Users create new ones dynamically too just to make it a bit more interesting. The application suffers from deadlocks which had me totally baffled until I read Gail's deadlock article on simple Talk. One of the best (possibly the best) and most useful articles I've seen about sql server.

jeffem Ten Centuries Points: 1232 More actions · Answer 3

Paul Brewer (1/8/2015)
Have a look at the views in a microsoft dynamics CRM database. Views upon views upon views. Users create new ones dynamically too just to make it a bit more interesting. The application suffers from deadlocks which had me totally baffled until I read Gail's deadlock article on simple Talk. One of the best (possibly the best) and most useful articles I've seen about sql server.

Ah. The deadlocks. That's also a good point, which made me realize a point I should have said - using multiple views is very often going to include tables that aren't at all necessary to the core logic of what you're trying to accomplish.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

jeffem (1/8/2015)
using multiple views is very often going to include tables that aren't at all necessary to the core logic of what you're trying to accomplish.

Depending on the view (especially on whether there's aggregation), the T-SQL parser can eliminate unnecessary views as part of it's simplification process. However that takes time and it's easily possible for nested views to have parse times > execution times if the parser has to do a lot of repeated view inlining and simplification.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Paul Brewer SSCrazy Points: 2955 More actions · Answer 5

Microsoft Dynamics CRM used to suffer (7 years ago, not sure if it still does) from a lot of 'Key Lookup' deadlocks related to excessive use of complex views built upon views built upon views built upon...... I remember sitting there confused wondering how a reader and writer could deadlock each other. Just reread the article on simple-talk to remind myself of the issue.

jeffem Ten Centuries Points: 1232 More actions · Answer 6

GilaMonster (1/9/2015)
jeffem (1/8/2015)
using multiple views is very often going to include tables that aren't at all necessary to the core logic of what you're trying to accomplish.
Depending on the view (especially on whether there's aggregation), the T-SQL parser can eliminate unnecessary views as part of it's simplification process. However that takes time and it's easily possible for nested views to have parse times > execution times if the parser has to do a lot of repeated view inlining and simplification.

Thanks, Gail! Very good to know. I was initially only thinking of a scenario using portions of all views involved but some tables from each view unnecessarily included and hadn't considered unnecessary views being eliminated. Smart!