locked
One table has a lot fields RRS feed

  • Question

  • In my database file has a lot of tables. And one of the tables has 100 fields. Now I want to add more fields to it. Do you think It'll make my database slow? Anybody has any idea?

    Thanks you in advance

    Tuesday, November 2, 2010 8:30 AM

Answers

  • If your data fields which you want to add in table is with one on one mapping then you should have it in same table because if in this case you are creating a new table then whenever you want to access data you need to join it so it will increase a overhead.

    • Marked as answer by New_CBH Friday, November 5, 2010 8:01 AM
    Tuesday, November 2, 2010 1:05 PM
  • The number of the fields (assuming you don't use select *) do not affect database performance directly . Although there is the question about row size. Bigger row -> less rows per data page -> more data pages -> more IO operations during SCANS -> performance degradation. One of the ways to improve the situation is to do the vertical partitioning: http://aboutsqlserver.com/2010/09/15/vertical-partitioning-as-the-way-to-reduce-io/ .

    Be careful with that technique though - it introduces the complexity and beneficial only if:

    1. You have a lot of data

    2. You have a lot of queries that use SCANS that require just subset of the data columns.

     

     

     


    Thank you!

    My blog: http://aboutsqlserver.com

    • Marked as answer by New_CBH Friday, November 5, 2010 8:01 AM
    Tuesday, November 2, 2010 2:20 PM
  • Hi New_CBH,

    In that case, I'd advise you to start with normalising the database
    design. In most cases, a normalised design results in better
    performance.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    • Marked as answer by New_CBH Friday, November 5, 2010 8:01 AM
    Wednesday, November 3, 2010 7:51 AM

All replies

  • If you specify in SELECT statement only needed columns instead of '*'  then performance should be ok, BUT it depends on queries  you ran againts and indexes defined on the table


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, November 2, 2010 8:55 AM
  • Hi New_CBH,

    Possibly, but not necessarily.

    However, I do question any design that involves tables with that amount of rows. Are you sure the design is fully normalized?
     -- Hugo Kornelis
    SQL Server MVP

    Tuesday, November 2, 2010 9:02 AM
  • If your data fields which you want to add in table is with one on one mapping then you should have it in same table because if in this case you are creating a new table then whenever you want to access data you need to join it so it will increase a overhead.

    • Marked as answer by New_CBH Friday, November 5, 2010 8:01 AM
    Tuesday, November 2, 2010 1:05 PM
  • The number of the fields (assuming you don't use select *) do not affect database performance directly . Although there is the question about row size. Bigger row -> less rows per data page -> more data pages -> more IO operations during SCANS -> performance degradation. One of the ways to improve the situation is to do the vertical partitioning: http://aboutsqlserver.com/2010/09/15/vertical-partitioning-as-the-way-to-reduce-io/ .

    Be careful with that technique though - it introduces the complexity and beneficial only if:

    1. You have a lot of data

    2. You have a lot of queries that use SCANS that require just subset of the data columns.

     

     

     


    Thank you!

    My blog: http://aboutsqlserver.com

    • Marked as answer by New_CBH Friday, November 5, 2010 8:01 AM
    Tuesday, November 2, 2010 2:20 PM
  • I'm not sure the design is fully normalized.
    Wednesday, November 3, 2010 6:33 AM
  • Hi New_CBH,

    In that case, I'd advise you to start with normalising the database
    design. In most cases, a normalised design results in better
    performance.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    • Marked as answer by New_CBH Friday, November 5, 2010 8:01 AM
    Wednesday, November 3, 2010 7:51 AM