Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
richardps
Starting Member
33 Posts |
 Posted - 2007-05-01 : 06:10:37
|
| I don't fully understand the new partitioning features of 2005 but I think I may have a use for them. I have one table which contains millions of rows of data which dates back over 6 months. I have a date field which I currently use to create a smaller fact table of the last 28 days, using a simple select * into t where date > etc...This select into can take a long time and I can't help thinking there must be an alternative to having to replicate the data in this way.Would partitioning help me and if so how? |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-01 : 10:12:55
|
| Partitioning may not help in this case. |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2007-05-01 : 14:55:15
|
| Partitioning probably won't help for accessing the last 28 days. However, you might want to investigate (or try out) partitioning depending on how you data is accessed. If your data is generally accessed via a date range, then you might be able to break that "main" table up into yearly partitions (or what ever makes sense). But, know that although 2005 partitioning is a step in the right direction, it really just handles some of the view manipulation that we had to do manually before.Cheers,-Ryan |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-05-01 : 15:12:09
|
| Starting with SQL Server 2005, there are two types of partitioning: partitioned views and partitioned tables. Here is some information that I sent to one of our developers regarding the two types:According to the SQL Server 2005 documentation, the preferred method for partitioning data locally is through partitioned tables. In our case, it would be done locally. So I started researching partitioned tables, which I didn't even know existed in SQL Server 2005. Partitioned tables are used when parts of the table are being read from and the other parts are being modified. Here's how it is explained in SQL Server Books Online:For example, if a current month of data is primarily used for INSERT, UPDATE, and DELETE operations while previous months are used primarily for SELECT queries, managing this table may be easier if it is partitioned by month. This benefit can be especially true if regular maintenance operations on the table only have to target a subset of the data. If the table is not partitioned, these operations can consume lots of resources on an entire data set. With partitioning, maintenance operations, such as index rebuilds and defragmentations, can be performed on a single month of write-only data, for example, while the read-only data is still available for online access.Will your customers be primarily reading from the old data (let's say 2-4 weeks minimum) or from all parts of the table? If they will be reading from all parts of the table, then we should consider partitioned views. Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2007-05-01 : 16:09:16
|
| Well, I was doing some Research on the Partition table feature in SQL SERVER 2005, but I didnt find any performance gain though and as per yours (TARA's) explaination, in my case the previous month data is only for read only. I had a table in which there are 15 million records, with 5 million in each month, I did the partition on the table basis of month, with 5 million in each block. Then i applied the index on the Datecolumn. Then when i did the query on the records, it takes some time as it was taking previously. Am I missing something???Chiraghttp://chirikworld.blogspot.com/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-05-01 : 16:15:45
|
| Chirag, I don't think partitioning data should be done on a table with only 15 million rows. I doubt you would see much of a performance difference between a table with 5 million rows or 15 million rows. I'd suggest posting a new thread on your problem. Show us the DDL for the tables involved in the query, including indexes, triggers, and constraints. Also provide the query and any other relevant information we may need to help you.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
|
|
Partitioning