Hi, I have a question for everyone here. Lately, I was asked to add additional 58 new fields into one of the table which has 75 fields with over 2.5 million records in it already. This is the main table that drives our web application going in term of front-end pages views and backend stored procedures generation. My question here is what might be a good solution to deal with that table. I thought about to break it up based on the functionalities but it would have to touch the entire current app both from front-end and back end and I might not have enough time to get it done on time. If I add these new 58 fields into the table, what is going to be the impact to the application? How to increase the performance with the change? Thank you in advance!
If those 58 fields are of char(1) or tinyint (that was a heated discussion just yesterday), then at a minimum you'll have over (2.5M * 58) increase in disk space, unless there are nulls there. And what would happen to old records? Would they have just nulls for those 58 fields?
In terms of performance, - if you're going to include them in SELECTs, your IO will be seriously affected. If they are goings to be used in WHERE or JOIN clauses, - your memory and CPU, along with IO are going to start suffering.
Before it's too late I'd take those 58 and dump them into a different table with the key from the current one. Yes, it's going to be 1-to-1 between the two, but it's a new functionality and you won't have to touch the old code.
Consider not allowing nulls on them if they will be part of WHERE or JOIN. You'll lose on effects of indexing if you do.
If, I include all 58 new fields into the table, the historical data with 2.5 million records will be NULL.
Most of those 58 fields are either ‘Y’/’N’ or single digit numerical number. But there are couples of DATE type fields.
Since the main table fields are coming from three different sources (we used a sourceTypeID in the table to distinguish them), only one third of fields in the main table along with this new 58 fields belong to one sourceType. If as suggested to create a different table to hold these new fields, what kind of Key you talked here to link these two tables?
With this design he has apparently inheirited, there may not be a key. It might be time to just bite the bullet and redesign the application. This is similar to having to fix the foundation of your house. Very easy when there is no house on top of it, but rather difficult when there is a house on top of it.
Is this just the web application, or does this table feed or devour other applications?
You’re right. I inherited this app from a guy who left this team. This app is relatively complex and it has been there for two years. It is a bank related Web app. It will be easier for me to stick these 58 new fields into the table so that I don’t have to worry about the existing code (Stored Procs/ DTS package/Front-end source code and etc.) but it might cause the performance. If I go through the Enterprise Manager -> Design View the table, I can not even add a new field into that table which has 75 columns with 2.5 million rows already. It was just inserting … forever. I would have to go through SQL Query Analyzer to run “ALTER TABLE TableName ADD ColumnName”. Since this table let’s called A is the master table to other three other tables, can I create a new table called B based upon the fields in A table then load all the data from A table. I want to drop A table first then rename the B to A? How will be the impact to those three slave tables?
Before you do that, take a peek around the code and see if the ex-developer was good enough to leave you all sorts of select * and insert into A values(...) queries that will have to be changed around.