which is better performance wise

  • SQL_Surfer

    SSCrazy Eights

    Points: 8088

    Two Scenarios

    1) A table with 200 fields. OR

    2) 200 fields spanning across multiple tables? For e.g 10 tables with 20 fields each.

    Which one is better in terms of performance and space used and why?

  • Mike John

    SSCertifiable

    Points: 7186

    I'm going to use the classic answer.

    "It Depends."

    if you ALWAYS want all columns retrieved together then a single table will be faster at getting everything compared with multiple tables.

    BUT in practice I doubt that will be true, and updating a wide table like that will be expensive, you will also have to manage lots of nulls I suspect.

    However I would suggest a table with 200 columns is very unlikely to be in anything like 3rd normal form (or better) , which means the most likely answer is to redesign it so it is.

    If you can post the table DDL, plus some examples of what you are doing you may get some more complete answers.

    HTH -

    Mike John

  • Jeff Moden

    SSC Guru

    Points: 997112

    To add to Mike's reply, if you have a lot of fixed length columns that frequently contain a NULL value, you might want to consider putting those columns in a "sister table" so that you don't actually have to take up the space by storing NULLs. You just wouldn't have rows in the "sister table" for the NULLs. It does complicate lookups and inserts a bit but a well formed view can ease that pain especially for the lookups.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • SQL_Surfer

    SSCrazy Eights

    Points: 8088

    All are varchar(50) except a pk which is an int. To clarify further, users enter/ update data through the web to this table. It is kind of necessary to show all fields through web. 45 of them are required field and rest could be blank.

  • HowardW

    One Orange Chip

    Points: 29109

    200 freetext fields and just a PK in addition?

    That sounds a pretty unusual requirement, what's it for?

  • SQL_Surfer

    SSCrazy Eights

    Points: 8088

    It stores an account. Users will either and new account or update existing account.

  • PaulB-TheOneAndOnly

    SSC Guru

    Points: 66773

    SQL_Surfer (6/16/2013)


    Two Scenarios

    1) A table with 200 fields. OR

    2) 200 fields spanning across multiple tables? For e.g 10 tables with 20 fields each.

    Which one is better in terms of performance and space used and why?

    Have you heard about data normalization?

    If OLTP I would at least go to the 3NF

    If DSS I would either go for 3NF or Dimensional modeling depending on your "datawarehouse modeling beliefs"

    The data model would tell how to group your columns, in how many tables and how they relate to each other.

    Hope this helps.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • SQL_Surfer

    SSCrazy Eights

    Points: 8088

    Each record will be unique though as it will be identified by AccountID.

  • Sean Lange

    SSC Guru

    Points: 286536

    SQL_Surfer (6/17/2013)


    Each record will be unique though as it will be identified by AccountID.

    That isn't the point of normalization. I would guess that if this is an online account you must have things like phone1, phone2, email1, email2, email3. 200 columns is a LOT of information to capture for an online account in a single table.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Mike John

    SSCertifiable

    Points: 7186

    From what you are saying these different columns sound like should be of several different data types, such things as account balances, credit limits, date last ordered, date last accessed, date last password change, password (encrypted?) etc etc come to mind.

    Precisely what those columns should be is clearly dependant on exactly what is meant by "account", but storing each of them in a varchar(50) sounds odd, and I suspect a decision that will be regretted if you actually implement that.

    Can you post more details of that table and it's relationships with others?

    Mike John

  • aaron.reese

    SSChampion

    Points: 13415

    My 2 Cents worth.

    If in doubt, create your tables to reflect the entity that it models and use the proper identity, primary and foreign keys to keep the data in line.

    As already stated, it sounds like your table is likely to contain lots of Phone1, Phone2, ... Phone(n) type fields.

    These should be in a table of contact details

    ContactMethodID (PK),CustomerID(FK to customer), ContactTypeID (FK to contactTypes),ContactDetails

    This would mean you could have 0, 1 or 100million contactMethod records for each customer. You have very little wasted space in the database and queries are simpler because you are extracting data against one table/column instead of against several.

    E.g.

    select CustomerID, ContactDetails from ContactMethods where ContactTypeID = 'Phone' and ContactDetails begins ('+44')

    Vs.

    select CustomerID, Phone1 as 'Phone' where Phone1 begins ('+44')

    UNION ALL

    select CustomerID, Phone2 as 'Phone' where Phone2 begins ('+44')

    UNION ALL

    select CustomerID, Phone3 as 'Phone' where Phone3 begins ('+44')

    ...

    select CustomerID, Phone_n as 'Phone' where Phone_n begins ('+44')

    The way the data is diaplyed on the screen for the user to fill in, and the validation done on that data may bear no reflection on the representation of that data in the database

  • Jeff Moden

    SSC Guru

    Points: 997112

    SQL_Surfer (6/17/2013)


    All are varchar(50) except a pk which is an int. To clarify further, users enter/ update data through the web to this table. It is kind of necessary to show all fields through web. 45 of them are required field and rest could be blank.

    That's a max of 10,000 characters per row... if a substantial number of the columns have near-capacity data in them, your data will go "out of row" and that will slow things down a bit.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • DiverKas

    SSCrazy

    Points: 2049

    I tend to create 3 tables for this excercise. The first table defines the "Account". A non data related PK, first name, last name. Real simple stuff that help uniquely define an account.

    The 2nd table is a list of all the other "Properties" of an account, that have no requirement to be filled in but may be present. I.E. phone 1, phone 2, phone 3, address1, address 2 etc.

    Finally, a table that contains the account PK, the property ID from the 2nd table and then the data. This allows for simple indexing, and flexibility and scalability.

    But thats just me.

Viewing 13 posts - 1 through 13 (of 13 total)

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