| Author |
Topic |
|
dhw
Constraint Violating Yak Guru
332 Posts |
 Posted - 2009-10-16 : 15:58:27
|
| Hello - I am working on creating a database design based on some user and functional requirements. This is an update to an existing system, so there is a legacy app and database for me to review. We will be using SQL 2005.A few of the entities (tables) in the old database have over 50 columns and about 10 of them are in the 70 to 80 range, for number of columns.The columns represent singular types of attributes for the table/entity. What I keep feeling like doing is to take that very wide table and break it into logical categories. Not sure if there is any benefit or disadvantage to doing this or even keeping the table design as it is. I searched around and really couldn't find any articles that provide any pros/cons to this question.Does anyone here have any thoughts or experiences either way?Thanks, will |
|
|
YellowBug
Aged Yak Warrior
616 Posts |
Posted - 2009-10-17 : 06:07:05
|
| Need a bit more information on your requirement:- What is the subject/area that the database covers?- Is it a reporting or transactional system?- If transactional, does the database conform to some of the normalization rules?And why use SQL 2005 for a new project? Have you considered SQL 2008? |
 |
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2009-10-17 : 15:30:06
|
quote:
Originally posted by YellowBug
Need a bit more information on your requirement:- What is the subject/area that the database covers?- Is it a reporting or transactional system?- If transactional, does the database conform to some of the normalization rules?And why use SQL 2005 for a new project? Have you considered SQL 2008?
This is a transactional system.Yes, the db does conform to, at minimum, 3NF. We need to use SQL 2005 because current users have that version and we need to allow an easy upgrade without the requirement of forcing users to also upgrade their SQL Server licensing/version.Thanks. |
|
|
|
max123456
Starting Member
4 Posts |
Posted - 2009-10-19 : 12:06:41
|
| Hi, I've a similar problem:I've a large table that contains over 180.000 records that is continously growing, and I'd like to split it in about 100 tables containing 2000 records. A second option is to split it in 20 tables.db: sql2005In your opinion this operation does improve performance or not?Which is the better option?thx in advance |
|
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2009-10-19 : 12:23:13
|
quote:
Originally posted by max123456
Hi, I've a similar problem:I've a large table that contains over 180.000 records that is continously growing, and I'd like to split it in about 100 tables containing 2000 records. A second option is to split it in 20 tables.db: sql2005In your opinion this operation does improve performance or not?Which is the better option?thx in advance
Hi, not sure if your situation is the same. I am talking about a table that has relatively few rows, but has many columns (over 100).For your situation, it sounds like you might benefit from partitioning the table. However, 180,000 rows really isn't that much for sql server to handle. Are you encountering performance issues now? What sort of way would you segreate the data into the separate tables - datetime? |
|
|
|
max123456
Starting Member
4 Posts |
Posted - 2009-10-19 : 12:37:53
|
| not datetime, but regional criteria (20 separate tables), or by province (100 tables)180K rows will become soon over 300K/400K |
|
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2009-10-19 : 12:43:50
|
quote:
Originally posted by max123456
not datetime, but regional criteria (20 separate tables), or by province (100 tables)180K rows will become soon over 300K/400K
Even 400K rows isn't that much for sql server to handle, if things are properly indexes, stats updated, etc. What sort of operations are you concerned about - Read or Write(insert/update)? And, are these operations typically performed on only a specifc region or province? Or, do you need to select (or update/insert) from or to multiple regions or provinces? |
|
|
|
max123456
Starting Member
4 Posts |
Posted - 2009-10-19 : 12:58:46
|
| only specific region/province |
|
|
|
max123456
Starting Member
4 Posts |
Posted - 2009-10-19 : 13:53:33
|
| my idea is not to drop the main table, for productivity reasons, but use it for insert/update queries; then I would use the filtered separate tables (copied from main table) only for select queries (which are the hardest activities for cpu) |
|
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2009-10-19 : 15:47:52
|
quote:
Originally posted by max123456
my idea is not to drop the main table, for productivity reasons, but use it for insert/update queries; then I would use the filtered separate tables (copied from main table) only for select queries (which are the hardest activities for cpu)
well, if you're only needing the filtered (by province or region) tables for select queries (not for insert or udpates), you might consider (if you haven't arleady) creating them as indexed/filtered views? Not sure what the all the select requirements are, but if they are relatively straightforward, an indexed/filtered view might do the trick. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-10-19 : 16:04:00
|
| dhw,If the table is already properly normalized, then it wouldn't make sense to split it up into multiple tables unless you are running SELECT * from the application or if you wanted to reduce the size of the clustered index so that it could fit onto a particular disk. max123456,Your design idea is not worth it. I've got hundreds of millions of rows in a table and do not have any performance issues.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
|
One Wide Table vs Several Small Tables