Updateable View

  • I am trying to scale an application by partitioning some large tables into smaller tables.  I have a view which has a union all of 16 tables partitioned by clientID.  When I do 1500 rows of updates using the view it takes more than 4 minutes to update.  When I update using the base table it takes less than 30 seconds.  The plan for the view shows all tables except the one being updated with 0 scan counts.  The base tables have indexes.  The update statement is first using the cluster and then the index based upon the clinet and id I am using to update the row.  The plan for the table has a top 1 after the seek on the cluster.  The plan for the view has a bookmark lookup.  Should updates against the table and the view be about the same if using the correct indexes?  What other items can I look at to partition this data?

     

    thanks

  • Add check constraint on smaller tables will help optimizer to search.

  • Check constraint is present.

    Anything else?  Is anyone using Partition Views to do updates or just selects?

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

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