SQL Server 2014 Checklist for Performance

  • jeffem

    Ten Centuries

    Points: 1222

    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: 2858

    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: 1222

    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: 1004484

    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: 2858

    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: 1222

    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!

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

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