Table Size/Performance | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Table Size/Performance

Hi,
We have a User table that contains close to 10 million rows of data. We expect it to grow by atleast a million rows per month. For each user, we store answers to anywhere from 200 to 2000 questions. The structure of table is as below: 1. ID – identity column
2. UserID
3. QuestionID
4. UserCountry
5. YearEntered
6. RowNum
7. Answer
…a couple of other cols.
Columns 2 to 6 form the Unique Clustered Index. My question is: is there a different way to store the data so that such large amount is not stored in the same table. With each addition of data, the queries are running slower. Any tips on how to optimize data storage in this kind of scenario would really help. Thanks a lot in advance for your time.
I think its possible. But not sure what else is in the table, but can you think of your data in this way and maybe normalize to fit it. User Information
userid
user country
anything else user specific Test information
Test specific data (including a user link) This is just from what I see above. Keep going with your data, and any data that is repeated for each row can be put into another table. Chris
Partitioned views? Brett :cool:
]]>