Post reply

Refresh on a view with schemabinding

  • January 8, 2010 at 6:57 am

    #219087

    Hi,

    I know that a view with schemabinding improves the performance and creating an indexed view, it can be a lot better. But, my question is: how the view with schemabinding is refreshed? At the time the table(s) are updated (or has a row inserted or deleted)? How it degraded the performance if the values of the tables are a lot changed?

    Thanks

  • January 8, 2010 at 9:40 am

    #1100997

    No one?

  • January 8, 2010 at 11:14 am

    #1101079

    rafa.aborges (1/8/2010)

    Hi,

    I know that a view with schemabinding improves the performance and creating an indexed view, it can be a lot better. But, my question is: how the view with schemabinding is refreshed? At the time the table(s) are updated (or has a row inserted or deleted)? How it degraded the performance if the values of the tables are a lot changed?

    Thanks

    One way to approach it is to think of it as you would a table.

    When does a table get updated? Similar answer would be for the view since it is a view onto the table.

    As far as the indexes go, similar principle as for the tables. Performance will degrade as data is updated and inserted causing page breaks and fragmentation. The more your index changes without maintenance on it, the more performance may degrade.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • January 8, 2010 at 11:20 am

    #1101088

    Ok, but the concept of the schemabind view is different from the regular view, right? Because, as far as I know, SQL Server materialize the schemabind view, if I'm not wrong.

    Thinking that way, when the table(s) are changed, the values from the schemabind view are changed at the same time?

    When I select on the schemabind view, it does not check the actual table, right?

  • January 8, 2010 at 11:23 am

    #1101092

    rafa.aborges (1/8/2010)

    Ok, but the concept of the schemabind view is different from the regular view, right? Because, as far as I know, SQL Server materialize the schemabind view, if I'm not wrong.

    Thinking that way, when the table(s) are changed, the values from the schemabind view are changed at the same time?

    When I select on the schemabind view, it does not check the actual table, right?

    Yes - they are different. As Lowell posted in your other post, the regular view is refreshed at the moment you run it. An indexed view holds a clustered index that is updated when the data is changed. My previous post was just in reference to the indexed type.

    Lowell, posted an article that describes it in your other post on the same subject.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • January 8, 2010 at 11:30 am

    #1101096

    i didn't see this thread, and started building my response before Jason posted the cross post info.

    a Normal view is not materialized as data the way a table is;

    views get "refreshed" whenever they are called; so while a real table might get a dozen rows added, the view doesn't really have any data...it's just a saved sql that is run on demand.

    Indexed views are slightly different, because they are stored as part of the data related to the clustered index, and kept at the leaf level of the clustered index. updates to the table automatically update the leaf data of the clustered index of the indexed view.

    this article explains it better than i could:

    http://www.sqlmag.com/Articles/ArticleID/8410/pg/4/4.html

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 8, 2010 at 11:36 am

    #1101102

    Lowell (1/8/2010)

    i didn't see this thread, and started building my response before Jason posted the cross post info.

    And you explained it quite nicely - twice even.:-)

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • January 8, 2010 at 11:47 am

    #1101116

    I don't have an account on sqlmag website... :blush:

  • January 9, 2010 at 6:43 am

    #1101365

    rafa.aborges (1/8/2010)

    I know that a view with schemabinding improves the performance and creating an indexed view, it can be a lot better. But, my question is: how the view with schemabinding is refreshed? At the time the table(s) are updated (or has a row inserted or deleted)? How it degraded the performance if the values of the tables are a lot changed?

    Don't confuse schema binding with indexing a view, they are quite different.

    Schema binding simply ensures that the base tables do not change their structure. In order to add a column, or change the data type of a column on a table referenced by a view with schema binding, the view must be dropped first. Thus the schemas of the base tables are bound to the view.

    Indexing a view requires that it is first schema bound. Creating a unique clustered index on a view physically creates all the data contained in the view. This speeds many queries since the data just had to be read - any joins or other operations defined in the view need not be performed.

    Once a view is indexed, the data is kept in sync with the base tables by incorporating the changes to the view in the query plan which updates the base table. So, a query which adds a row to a table referenced by an indexed view will have additional operators added to the end of the plan to maintain the view.

    Depending on the definition of the view, and the number of indexes which must be maintained on it, the overhead can be considerable. In general, it is best to create an indexed view over tables which change relatively infrequently. That said, indexed views can be an efficient way to maintain summaries, even where data does change often...at least compared to using triggers to perform the same task. As usual, the answer is that it very much depends.

    Paul

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • January 11, 2010 at 5:47 am

    #1101722

    Great answer! That's exactly what I need to know.

    Thanks, Paul

  • January 11, 2010 at 5:51 am

    #1101726

    Thank you 🙂

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

Viewing 11 posts - 1 through 10 (of 10 total)

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