| Author |
Topic |
|
dhw
Constraint Violating Yak Guru
332 Posts |
 Posted - 2008-07-18 : 19:35:13
|
| Hello -I have a table that, I believe, has become a real drag on performance for queries, inserts, etc. The table currently has about 25 million rows and has data from 2005 to present. I am thinking that partitioning the table, probably by year, will help the queries as well as indexing (the 2005 to 2007 data doesn't need to constantly be reindexed). My question, is there any problem with creating the necessary partition functions, schemes and then partitioning an existing table?Thanks - will |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-07-19 : 00:53:22
|
| You need create a new partitioned table then move data over. |
 |
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2008-07-19 : 01:56:39
|
| Thanks for the help. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-07-19 : 01:59:13
|
| 25 million rows isn't big enough to bother with partitioning. You should be able to speed things up with indexes, rewriting queries, or redesigning the database. If you partition now and your system isn't optimized, you are only masking the problem for now.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-07-19 : 02:24:46
|
How many columns do you have for any record?Do you have SELECT's with asterisk (*)?Maybe you could do a vertical partition?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2008-07-19 : 12:54:53
|
quote:
Originally posted by tkizer
25 million rows isn't big enough to bother with partitioning. You should be able to speed things up with indexes, rewriting queries, or redesigning the database. If you partition now and your system isn't optimized, you are only masking the problem for now.
Well, I have not had enough empirical experience to deal with partitioning. But we have alot of older data (from previous years) that is rarely queried, but the data HAS to be available just in case someone wants to check it out. My original assumption is that all of the queries that use this table as part of their JOIN are going to operate slowly because of all the rows. THere are some indexes on this table already. But I have a feeling that there needs to be a major database redesign. I wasn't involved with the original design and I am only taking over responsibility for it now. I foresee a time in the near future where we will do a redesign, so I thought that partitioning might prove beneficial for the next few months until I can take the time to redesign. |
|
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2008-07-19 : 12:59:33
|
quote:
Originally posted by Peso
How many columns do you have for any record?Do you have SELECT's with asterisk (*)?Maybe you could do a vertical partition?
The table only has about 10 rows and it appears that the queries use about 7 or 8 of the rows. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-07-19 : 13:04:12
|
| I'm sure you mean columns rather than rows. SQL Server 2005 has two types of partitioning: partitioned table and partitioned views. In your scenario where you have historical data and current data, I'd suggest partitioned tables as that's exactly what SQL Server Books Online says they are used for. Partitioned views is more for when you'll be querying all of the data and not just recent data and so you split the data up to make the tables smaller. For instance, myspace.com uses partitioned views to handle the extreme amount of data that they have. They probably partition by a range of users. They also partition across federated servers which allows to scale out.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2008-07-19 : 13:13:52
|
quote:
Originally posted by tkizer
I'm sure you mean columns rather than rows. SQL Server 2005 has two types of partitioning: partitioned table and partitioned views. In your scenario where you have historical data and current data, I'd suggest partitioned tables as that's exactly what SQL Server Books Online says they are used for. Partitioned views is more for when you'll be querying all of the data and not just recent data and so you split the data up to make the tables smaller. For instance, myspace.com uses partitioned views to handle the extreme amount of data that they have. They probably partition by a range of users. They also partition across federated servers which allows to scale out.
Doh! Yes, I meant columns not rows. Sorry, no coffee yet.And yes, I was/am considering a partitioned table, not a view.Thanks. |
|
|
|
|
Partition existing table