Breaking a large table into smaller tables
-
I am working on a database that has a main table that contains around 50 fields of many different data types. Should I consider breaking it into multiple tables with a One to One relationship? A couple of things to note; 1) the front end will have the data broken into 4 sections which will require different roles to update. 2) I am almost done with the front end and it would probably take 1-2 days to split the data and get everything working.
I would appreciate any thoughts or advantages/disadvantages of splitting this table.
fryere
-
Can you post the table design?? hard to tell.. Sometimes a 5 columns table is too large and a 200 columns is not large enough. what's the max size of one row?
-
Column Name Type Length Proposed Split PK Project_Key int 4 Each Table Org_Key smallint 2 TblCore P1 char 1 TblCore MSR char 1 TblCore CI char 1 TblCore ME char 1 TblCore ORM char 1 TblCore Design_Funded_FY varchar 15 TblCore DSN_Const char 1 TblCore Project_Num char 9 TblCore RMC varchar 50 TblCore MTF varchar 50 TblCore Project_Title varchar 100 TblCore Est_Cost smallint 2 TblCore Project_Desc text 16 TblCore Justification text 16 TblCore RCI text 16 TblCore PDI text 16 TblCore MEI text 16 TblCore PFI char 1 TblCore MTF_Priority smallint 2 tblRMC RMC_Priority smallint 2 tblRMC Rmc_Notes varchar 200 tblRMC Cancelled_Flag bit 1 tblRMC FCI_Impact char 1 TblExt Failures_Num tinyint 1 TblExt Equipment_Age tinyint 1 TblExt Master_Plan char 1 TblExt Next_Jacho smallint 2 TblExt Facility_Num char 5 TblExt Current_FCI numeric 5 TblExt ISR_Rating char 1 TblExt Facility_Type char 1 TblExt New_FootPrint char 1 TblExt Project_Class char 1 TblExt Contracting_Office varchar 50 TblExt Working_Cost smallint 2 TblCorp SOW_4283 bit 1 TblCorp Const_Amount_L smallint 2 TblCorp Const_Amount_K smallint 2 TblCorp RS_Date datetime 8 TblCorp Award_Date datetime 8 TblCorp RS_Award_Days smallint 2 TblCorp Ready bit 1 TblCorp MEDCOM_Priority smallint 2 TblCorp Priority_FY smallint 2 TblCorp MEDCOM_Notes varchar 200 TblCorp Last_Update datetime 8 Each Table Updated_By smallint 2 Each Table fryere
-
It's hard to comment on that without knowing what the fields really mean. Can you explain why you think you should split the table like that (if at all)?
-
Another developer made a comment about reducing the size of that table for performance reasons. The data inside the table makes up a 'Project', but is kind of made of 4 sections which have their own Update roles. For the most part the data will be pulled to the front end by these sections. (The Front end will have 4 tabs that allow the user to view a section at a time.) For now the only time all of the data will be pulled in together is in a few Detail reports.
I guess the main question is would the performance be enhanced by moving to the smaller tables instead of selecting the appropriate fields from a larger table. Also, Would there be a performance hit when I had to link the tables together to get all of the data compared to just selecting all the columns of the large table.
I hope this make sense.
Thanks
fryere
-
Performance hit to join : yes. But if it rarely happens it might not be a factor. The big table problems is that the server must read all the data even if you fetch only a few columns. So that could give some performance benefit if the table is really large 1M+. I think I would design this more to make sure that the model makes sens relation wise than for performance.
-
My i suggest maybe using Index's on those fields that are most offen use... that will increase some performance
Moe C -
Indexing strategy is far more complexe than just putting indexes. Maintaining them also has a cost and it can be pretty high.
-
The answer is: it depends.
If most of your queries are using just a subset of columns then it makes sense to split the table into several smaller tables. But if after the split you will use mainly joins then I wouldn't split.
An another solution for performance improvement could be covered index or even several indexed view.
BUt if the size of your table is not too big (max several thousends of records) then don't spend too many time on it
Bye
Gabor -
Thanks for the responses. As it turned out I will have to reference fields for WHERE conditions in most of the tables that I would be split. Therefore, I have abandoned the splitting idea since I will have to link 3 or 4 tables together for just about every view/stored procedure.
Thanks again.
fryere
-
A parting thoguht: if the data that could be stored in one row is split into one row in each of four tables, how do you ensure that a "full set" of data is in fact present in the database? You could have X in three tables and be missing it in the fourth. Foreign key constraints (or views with wildly complex "instead of insert" triggers) could lower the chance of the problem, but not entirely prevent it.
Assuming that your application will always ensure that the data is populated in all four tables is precisely that: an assumption. What if another application uses the table? What if the application changes? What if the table structures change (say, by adding table #5)? What if someone with direct access to the database executes some poorly thought-out code... or simply wasn't conversant with the underlying requirements of the database? (This last also applies to future application development.)
There are valid reasons for vertical partitioning, but always weigh the costs with the benefits.
Philip
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply