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 |
|
Hariarul
Posting Yak Master
160 Posts |
 Posted - 2008-02-26 : 11:56:20
|
| Hi Experts,We have a very huge database that stores 12 years of data(120 Million records). But our application mainly accesses past 3 years data i.e , the queries would scan the 120 million records even when it actually has to scan 30 million records alone (for 3 years). Since few other important applications needs access to all the 12 years data, we are in a position to have 12 years data in the same database. Right now we are looking for an approach that would help us to efficiently access the 3 years data alone and boost the performance.1. Will SQL server table paritioning help in this scenario ?Or 2. Indexed views would help us ? Is it possible to create indexed views based on year range and access the views in the stored procedures ?Any help would be greatly appreciated.Thanks in advance,Hariarul |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-02-26 : 12:00:20
|
| If you have Enterprise edition, You can definitely go for Table partition.Yes, you can partition by date and spread partition accross multiple disks for performance. Moreover,if you don't need last 6 years of data, you can switch out partition . If you have 12 years of data, You can switch out partition. If you need more help, let me know. |
 |
|
|
Hariarul
Posting Yak Master
160 Posts |
Posted - 2008-02-26 : 13:34:02
|
| Thanks sodeep. But , as of now we dont have multiple disk drives to place the filgroups created for individual partitions. All the partitions are in the same drive - will this help to boost the performance ?BTW , any thoughts regarding the usage of Indexed views for this scenario ? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-02-26 : 13:37:47
|
| 1) Nope. You need multiple drives (atleast 4)2) you also need enterprise edition for index views.You can do it but not really good like Table partition which will data accross multiple drives3) Check partioned views. Maybe that helps. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-26 : 21:34:23
|
| You can partition table and put all partitions in single filegroup, or put partitions in separate filegroups on single disk. They may not boost disk performance, but still helps since sql pulls data from partition instead of whole table. |
|
|
|
|
|
|
|
|
SOS - Table partitions or Indexed views.