Partition View

Question

Partition View

karmour

Mr or Mrs. 500

Points: 525
More actions
February 4, 2004 at 5:46 am

#107621

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

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

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

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

karmour Mr or Mrs. 500 Points: 525 More actions · Answer 2

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