Hi, I was trying to create a cumulative total table for our company (advertising). This table contains cumulative totals for impressions, clicks, etc. From 7/1/05 to 2/31/06, this cumulative total table already has over 22 million records (the growth rate is very high, it can be over 200K records per day). Create this table is not hard, all I did was crfeating a Stored Procedure to generate cumulative total based on the previous day's data. However, when it comes to SELECT from this table, it gives me unpresent respond time. For example, this table has primary key on a columns delivery_date, datasource_id, site_id, placement_id. When I say: SELECT delivery_date, site_id, placement_id,cum_impressions FROM cumulative WHERE delivery_date BETWEEN '20050701' AND '20051031' AND site_id=2837 and datasource_id=9 and site_id=213293. This query took the clustered index seek, however, it uses pararellism to perform its task. And it takes a long long time to get me back the results. I guess because the amount of records that this query has to process and return. I used the index tuning wizard, and it recommanded me to create a non-clustered index, the performance of this query became very good, however, this non-clustered index takes about 2 GB of space. And it takes forever for me to insert records to this table. My question is, is anyone out there can help me how to handle such situation? Does partition view or other features that I can use to improve the performance of having a very large table by selecting and as well as inserting / updating? Thanks!

Software/Hardware used: