Horizontal partioning use in application

[MrPeds]

Hi

Not sure if this post is in the correct forum as it seems to cross admin / programming, so apologies in advance.

I currently have a huge table (28 million rows) with address information for all the UK. the columns contained in it are things like house number, street, town, county and POst code (bit like a zip code).

To try and increase query response time I am going to horizontally partition the table based on post code, so there will be a table for postal address starting with an A to E, F to J and so on. Each table will have its own filegroup on a seperate disk, so fairly standard design.

My problem is this - our web application needs to take in a post code from a customer, and from this postcode the agent will select the house address relating to that post code. e.g. one postcode maps to many houses.

Basically i dont know if i should have some kind of mapping table that points to one of the partitioned tables to return the list of houses or use some other methodology - the important thing is the speed of returning the houses on a street for the given postcode.

Any thoughts are appreciated.

regards,

MrPeds

 

[mrdenny]

Put constraints on the tables to make sure that the correct data is the only data that gets into the tables. Then put a view in front of the tables. The optomizer should only query the correct table.

If you are using SQL 2005 you can use it's native table partitioning.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[gmmastros]

Take a look here.

http://www.sqlteam.com/article/partitioning-the-data-in-a-table

-George

"the screen with the little boxes in the window." - Moron