A question about sql server capabilities

Question

A question about sql server capabilities

oren koler

Right there with Babe

Points: 776
More actions
August 26, 2006 at 1:07 am

#170100

Hi

I have a dilemma about creating a big table

The table holds about 10 columns:
Exercise_number column2 column3 column4 ……….
-------------------- ---------- ----------- ----------

1. There are about 10 exercises
2. Each exercise holds about 3 million rows !!!
3. Query time is important
4. update/insert/delete time is unimportant
5. Most queries are per Exercise_number
(Example: select …. Where Exercise_number=1 )

Question:
What is better?
One huge table (30 million rows !!! ) with an index on Exercise_number ( slow query but easy upkeep )
OR
10 tables, one per each Exercise_number ( 3 million rows per table )
( faster queries but hard upkeep )

What I really need to know is how much of a time difference is there between asking a query on a
3 million table
to a
30 million table with an index on Exercise_number ??

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

salim SSChasing Mays Points: 659 More actions · Answer 1

I'd say, if you had efficient enough hardware [fast CPUs, enough memory and efficient disk system,i.e. RAID 1+0 or similar] then i would keep all in one table.

You have to do a lot of testing and prove which scenario serves you better.

And, use stored procedure for data access to reduce network traffic between DB and the application.

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 2

efficient data partitioning will always be faster, however the upkeep and complexity of partitioned tables ( or views if 2000 ) should always be considered.

To be honest 30 million rows isn't that big and with good indexing I don't see a problem on a reasonably configured server. Might depend a little on your row width and if you're planning to use triggers, text columns , images or such ( a clustered PK on a guid for example would be a very bad idea on a table this size )

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

oren koler Right there with Babe Points: 776 More actions · Answer 3

thanks for the good advice

ill build one table with a clustered index on exercise number

salim SSChasing Mays Points: 659 More actions · Answer 4

I agree with colin Leversuch-Roberts that Table Partitioning is great ,, BUT only if using SQL2005. So if you plan to move to SQL2005 then it is the solution you want definitely.

Hope your test will be fruitful.

I would also advice to visit http://www.sql-server-performance.com for greater insights into SQL performance.