Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Posts
    33

    Unanswered: Too many Columns in the table issue

    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!

    JQiu827

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Cool, a 1 table application...

    What's the row length now?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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.

  4. #4
    Join Date
    Mar 2004
    Posts
    33

    Too many fields in one table issue

    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?

    Thanks!

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    The same key as the one that exists in the original table. There is a key there, isn't it?

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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?

  7. #7
    Join Date
    Mar 2004
    Posts
    33
    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?

    Thanks!

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    All FK's need to be re-added and all SP's recompiled. Other than that, - no biggie.

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •