Solved

Column size overhead

Posted on 2004-10-31
276 Views
Last Modified: 2008-03-17
I have inherited a database that I am wondering about optimizing the column sizes. There are many varchar columns that are overly generous in their sizes. For example, varchar(100) where the data is routinely 20 characters or less and probably never over 30 characters wide.

Similarly, there are other fields that are way too generous for the forseeable data.

My question is: how much of a performance penalty are we paying for column sizes that are overly generous?
0
Question by:jasimon9
    3 Comments
     
    LVL 4

    Assisted Solution

    by:chipple
    Regarding the varchar type, since the number only represents the maximum size of the data (and not the actual size the data takes in the database), I don't think a field with a too large maximum can be affecting performance.

    However, since the database system has a maximum record size, you risk reaching that maximum if more columns were to be added to the table. So it's always better to keep fields to a reasonable size.

    Good luck.
    0
     
    LVL 11

    Expert Comment

    by:rdrunner
    Hello,

    You can get the size of the various Datatypes by looking at the table definition in the EM. But there are some exceptions to this and the type you listed belongs to them.

    The VAR... Datatypes will use a the lenght of the Data they really hold. So a varchar (8000) holding "Hello" will be 5 bytes wide
    If you have a char(8000) holding "Hello" it would use 8000 bytes.

    The "external" Datatypes like Text will use 16 Byte in the table to store a pointer to the real data thats not stored in the tablerow.

    A Bit Datatype will use 1 Bit rounded up to the next Byte counting all bits... so 7 Bits will use 1 Byte and 9 will use 2 Bytes

    Hope this helps

    For more information check "Fieldlenght" under BOL
    0
     
    LVL 1

    Accepted Solution

    by:
    Good comments above.

    But assuming MSSQL.

    No performance penalty in SQL 2000 and above.  

    The usage of variable length data types "used to" cause deferred updates, page splits and consolidations possible also, which could result in performance hits.

    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
    Creating and Managing Databases with phpMyAdmin in cPanel.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    This video is in connection to the article "The case of a missing mobile phone (https://www.experts-exchange.com/articles/28474/The-Case-of-a-Missing-Mobile-Phone.html)". It will help one to understand clearly the steps to track a lost android phone.

    875 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now