large tables

[SQLScholar]

Hi all,

We have a table - which is starting to get big, quickly. Someone in the team i work in had the idea to split the table in bits (so maybe 2005,2006,2007)

It was said that if the data was split (rather then just leaving it in one table, and forcing the end user to chose a year - so that less data was returned) the speed would be quicker, as the index would be smaller?

Is this true?

Thanks

Dan

----------------------------------------
Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[gmmastros]

What you are describing is called horizontal partitioning. It can (if implemented correctly) speed up your queries.

Here's a quick article that explains it:

http://en.wikipedia.org/wiki/Partition_(database)

Of course, a more in depth google search will help your understanding and make it easier to implement.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mrdenny]

If you have SQL 2005 you can partition the data within the table accross multiple file groups.

How many records are you talking about?

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

 

[SQLScholar]

We are talking 338858 records in the largest current table - i know this isnt much, but i am more concerned with the rate of growth. If i remember correctly before december it was in the early 100,000s.

Dan

----------------------------------------
Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[mrdenny]

200,000 records in 3 months. That a bit, but that's not that extrem.

Properly indexes that growth shouldn't be a problem. I've had tables with over a billion records in the table that didn't take long to query; and that was without a good hard drive setup and without much indexing.

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