How to break up a table holding 100mil+ number of records?

Question

We're currently storing answers for 52 predefined questions for our clients in our matchmaking site.

we have over 30million unique users summing up for worst case of a 52x30million rows. Of these 52 questions, 11 are required and always answered.

Our previous solution was to open an answer table for each question. This solution distributed our answer rows for faster insert/delete/update. But it also caused us an unconventional programming such as dynamically opening a table each time a question is added/updated, or removing an answer table if it was to be destroyed permanently.

We want to come up with a better solution for our third version but could't get very far yet.

Any ideas to accomplish this in any other, perhaps a more conventional, way?

Answer 1

Why do you have bad performance now? Do you know if you are IO bound or suffer short page life? Prior to changing the design your company needs to determine why you have bad perfornace.

I will guess your hardware is

• less than 2 gig of memory for SQL
• run other apps on the server hosting SQL
• have a Raid 5 hosting your db
• have the log files on the same disks as your db
• have the temp db on the same drives as the db

I will guess your SQL tables have

• no indexes or indexes on every column
• every column is the same datatype and length (varchar(256))
  • allows null in every column

The best db design is one which will satisify your business rules. Do your business rules define a reporting system or an OLAP? Do you business rules define a SLA for the application?

My advice is to hire a firm which has a proven track record of identifing poor SQL performance. Implement their recomendations first then redesign to meet your SLA. 1.5 billion rows for SQL Server is not that many in one table if one know how it will be used, OLAP or Reporting.

I really do not want this to sound harsh but without an investigation of all performance aspects of the application any suggestions would be a waste of your time.