Horizontal partitioning as a database design

Question

Horizontal partitioning as a database design

ishwarjindal

SSC Journeyman

Points: 97
More actions
August 8, 2004 at 3:30 am

#360535
Introduction

We are going start the design of new database for a web-based application in SQL 2000. We have an existing application and its database in SQL 2000. The current size of this database is 30 GB.

The problem with the old database which we thought is that
1. Some portion of database (a set of tables I would say 40% of the total tables) is too normalized.
1. Data tables are too large (that is in terms of row count) as there is no data archiving policy implemented.
1. Unnecessarily duplication of same information in multiple tables i.e. data redundancy
1. Unable to scale up the database.
So all these factors (and some other also) tend us to go for a new database design. Our plan is to design a new database and then port the existing data into new database.

Queries
Since our application run in four different regions in US so our team has come up with a basic design architecture that is horizontal partitioning based on region (i.e. the portioning key will be the region in question). I have done initial research on horizontal partitioning and has found out both limitations and benefits of this architecture but still it is not clear whether we should go for this architecture or not.

Questions
1. What I think is that the horizontal partitioning is more like an optimization technique not database design architecture for an Web based ERP solution. What you say on this?
1. Can you people suggest any other suitable architecture/technique for our database?
1. Since I am doubtful on using horizontal partitioning architecture, can you guide me about any instance of real world situation where this architecture is useful?
1. What kind of drawbacks are there in terms of administration of a database if we go for horizontal partitioning architecture. for example what will the issues related backup, restoration etc.
1. There is an alternative to horizontal partitioning in which instead of creating partitioned view we will manually fire queries from our web pages to correct instance database and thus avoiding the need of partitioned views. Any idea about this technique? I am not very sure on this technique, but one of my team member suggested this idea so i am just asking for confirmation.
1. Any idea about design practices and patterns one should follow for designing a new database for large online applications.
1. Any good resource on internet and books regarding the problem.

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

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

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

I think horzintal partitioning can be a database design technique, but I'm not sure I'd recommend it in your case. I've used it in places where we had a wide table, lots of columns. However, most of the time we didn't need all this data, but incurred the penality of IO in reading and caching this data. So we partitioned the table into "frequently used" data and "rarely" used data, both of which had the same PK.

I'd be wary of using it in your case because if the regions change, or someone wants a fifth region, there is lots of data movement to clean things up.

ishwarjindal SSC Journeyman Points: 97 More actions · Answer 2

Thanks steve for your earlier reply.

I have one more query. I have a doubt regarding the INSTEAD OF trigger on partitioned view. Actually the doubt come when i saw the following statement on Online MSDN. Visit the following URL and goto the bottom of screen

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_06_17zr.asp

"A partitioned view that references partitioned tables without following all the rules (i.e. the rules which a partitioned view must follow to be a updatable partitioned view) may still be updatable if there is an INSTEAD OF trigger on the view. The query optimizer, however, may not always be able to build execution plans for a view with an INSTEAD OF trigger that are as efficient as the plans for a partitioned view that follows all of the rules."

Doubt

If it is refering to view's own execution plan (i.e. the execution plan corresponding to view's statement) or the execution plan of the stored proc where such view can be refered then what is the sense of using always.

I mean the execution plan once get created will remain in SQL Server's procedure cache unless and untill you restart the SQL Server. So what exactly the word always implies here.

One technical doubt

What could be the relationship between a view's INSTEAD OF trigger and the execution plan. Generally the execution plans are concerned in the data selection type of statements. Since the triggers are related to data modification statements so what kind of link is there bewteen the efficiency of a execution plan and trigger.

Any idea?