Ways to handle Very Large Table

0 pts.
Tags:
SQL Server
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!
ASKED: June 20, 2006  5:54 PM
UPDATED: June 21, 2006  11:15 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

If you have SQL Server 2000 Enterprise Edition, then you can create partitioned views that you can separate by dates or IDs.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following