Partition View

  • I have created a partition view for testing.  When updating 1500 rows it takes over 4 minutes using the view.  When updating using the base table it takes less than 30 seconds.  Any ideas?  How can anyone be using these for distributed large databases if the updates run significantly longer?  I am currently using these as local but plan on setting up a Federated database environment.  Based upon my initial testing there is no way I will be able to do this.  What other options are there for scaling a large OLTP system? 

     

    thanks

  • While I have not implemented partitioned views in porduction I have done some experimenting with them.  I believe that if you are using an Identity field in your tables that affects how they work when part of a partitioned view.  What I read in BOL and elsewhere is that you should not use and Identity field in the base tables and that your partitioning column, in you case client id, should be the first column in a composite primary key.

    I have always used stored procedures to do updates and you could do that using the client id as a parameter to determine which base table to update.  Then you could still use the partitioned view for reporting.  The code would be something like

    If client_id = 1

    Begin

    Update tableA

    End

    etc...

    You could even have separate sp's per base table that are called from a central sp, which would keep the sp from recompiling when you passd in a different client id.

    Jack

     

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • Thanks for the reply.  I do have the correct check constraints and have the indexes setup correctly.  I am trying to find a way to take a large OLTP database to be able to process more faster.  Scale out based upon BOL indicates that this is the way to go.  From my testing over the last 3-4 weeks I see this as a way to go if you are creating a reporting type database instead of OLTP.  I haven't even tried across servers yet because I figure if it doesn't perform locally then across servers will really be bad. 

     

    thanks

Viewing 3 posts - 1 through 3 (of 3 total)

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