Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6

    Unanswered: Thoughts for speeding up a View?

    I've been reading up lately that adding clustered indexes to a view or potentially schemabinding the view to the tables are two really good ways of optimizing the performance of your view.

    Right now this thing is massive, so I am a little leery that any optimization is possible, but wanted to bounce these ideas against experienced DBA's.

    The underlying table structure should have very little movement if ever, so the schema being bound shouldn't hinder anyone.

    Just was curious what your thoughts were? Is it a waste of time, the articles I have been reading seem to be pro schema binding.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You can create a materialized view which allows you to create the clustered indexes on it. This probably has a very different behavior than what you are imagining, and it can consume a lot of disk if you aren't VERY careful.

    A view is basically a "canned" SELECT statement. This is convenient, and sometimes allows the SQL Server to do some special optimizations that help performance. With that said, the view is still a SELECT statement and almost anything that will optimize a SELECT statement will also optimize a view built from that SELECT statement. The same strategies and tactics that you use to optimize a SELECT statement can and should be applied to the view.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    You know Pat I am a little nervous.

    This view is dependent upon 3 other views. Maybe more.......

    I ran the Statistics IO function on the query and saved the results. I was going to add the schemabinding to the view but it kept saying there were other views that weren't schema bound. I just feel outside my element adding schemabinding on these views upon views.

    I'm not sure if I schemabind all the views and drop an Index on the master view will help. I was going to run some statistics on it with IO and the Execution Plan. I am in a test environment so.........
    Last edited by VLOOKUP; 01-23-15 at 16:07.

  4. #4
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    You say be careful, it looks like all the DBA sites are saying you should always bind your views. None of our views are bound, but we also have a view with several views joined to it. I'm just not sure if this makes sense to schema bind all the views. Maybe creating a job and dumping the view into a physical table nightly makes sense. But there are 40 million records so that makes me nervous too, it might interfere with other jobs running like some of our ETL's we have scheduled at night.

    I read locking can occur with bound views with indexes.

    We do have an OLAP set up but for some reports sometimes the MDX isn't the best method for reporting.
    Last edited by VLOOKUP; 01-23-15 at 16:24.

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by VLOOKUP View Post
    You say be careful, it looks like all the DBA sites are saying you should always bind your views. None of our views are bound, but we also have a view with several views joined to it. I'm just not sure if this makes sense to schema bind all the views. Maybe creating a job and dumping the view into a physical table nightly makes sense. But there are 40 million records so that makes me nervous too, it might interfere with other jobs running like some of our ETL's we have scheduled at night.

    I read locking can occur with bound views with indexes.

    We do have an OLAP set up but for some reports sometimes the MDX isn't the best method for reporting.
    Indexed views have a lot of gotcha's. I highly suggest that you read the documentation on the subject in SQL Server Books Online. I have only successfully implemented them in read-only databases, and if your database is read only and these materialized views consume disk space you might as well manifest another table. The first time I tried this was in a large and complex legacy system, and it turns out that if your tables are not created with certain ANSI settings turned a certain way, and if the processes that do any DML on your underlying base tables do not have certain configuration set correctly, there can be lots of new errors.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •