Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2015
    Posts
    2

    Angry Answered: Type minimization for performance?

    At my work, I am in a battle for sanity with our database administrator.

    Is there any reason that having more unique data types used in a MSSQL database would degrade performance???

    //BEGIN ANGRY RANT
    Our DBA is in love with the varchar(50). Nearly EVERYTHING in the database is a varchar(50). Need a boolean value, varchar(50). An integer, varchar(50). Some binary data, a varchar(max) packed with space separated hex byte strings. The real kicker is that he isn't even consistent with how these are represented from column to column. Some boolean values are '1' and '2' (with the odd '0' for good measure ), others are 'True' and 'False', and if he doesn't think a field needs a value, empty string.

    Every time we have a meeting where we are adding columns to the database, I ask for a stronger type. The response from the DBA is always the same "A database designer must always seek to have the smallest number of unique types in a database. Too many different types will cause performance to degrade.". My boss always sides with the DBA because he has 20+ years of experience, and I only have 2 .
    //END ANGRY RANT
    Last edited by Jerm128; 09-18-15 at 21:30.

  2. Best Answer
    Posted by Pat Phelan

    "I'd need to discuss this with your DBA to be sure that I understood what they're trying to say, but what you've quoted makes no sense to me. If you accurately understood and repeated the quotation then I'd disagree with the choice to use VARCHAR(50) to store int, boolean, and date data.

    There were once (pre Y2K) some exceptional cases on specific products that tended to force the Data Architect to use text storage, but those were exceptions even then.

    -PatP"


  3. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd need to discuss this with your DBA to be sure that I understood what they're trying to say, but what you've quoted makes no sense to me. If you accurately understood and repeated the quotation then I'd disagree with the choice to use VARCHAR(50) to store int, boolean, and date data.

    There were once (pre Y2K) some exceptional cases on specific products that tended to force the Data Architect to use text storage, but those were exceptions even then.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #3
    Join Date
    Sep 2015
    Posts
    2
    I don't really know how else to put it, he thinks that we will take a performance hit if there are too many unique datatypes used in the database.

    To be honest, it doesn't really matter, nothing that I do or say is going to change anything. This is my first "real" development job after collage, and it has been good, but I think it is nearly time for me to move on.

    Thanks for the response.

  5. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yeah, if you got the quote right then moving on is probably the best thing that you can do in that situation.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Ask him, in front of someone that matters for a technical reference that is not some blog post from another idiot on the internet. Anytime anyone comes at me with some probably made up mumbo jumbo, and there is a lot of that in the database world for whatever reason, I ask for technical references preferably from the vendor that produced the platform in question.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    minimization does help performance, but that means using a one byte field instead of a 50 byte field. Not minimizing does cost you.
    Dave

  8. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by dav1mo View Post
    minimization does help performance, but that means using a one byte field instead of a 50 byte field. Not minimizing does cost you.
    I am not arguing that. I am telling him to ask the DBA for a technical reference to back up the DBAs assertion that havning too many data types causes performance issues. The DBA is saying everything needs to be a varchar(50) for performance reasons.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  9. #8
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I know, I have seen a couple of bad applications that used that same type logic. They didn't last long from what I remember and they didn't perform well either. The other type excuse I like from someone like this, is "I did extensive testing of this back in 1992!" Its a good thing nothing has changed in computers/software and databases since 1992 otherwise we might have to revisit some antiquated ideas and get some new benchmarks.
    Dave

  10. #9
    Join Date
    Oct 2015
    Posts
    4
    Now, this probably isn't exact (in term of number of bits) as I am writing this off the top of my head without confirming, but:

    1 byte = 8bits
    character datatype of length 1 will consume 8 bits
    character datatype of length 50 will consume 400 bits (50*8)

    in the example of boolean datatype:

    if you use type bit database will consume exactly that - one bit. In computer language 0 or 1

    if you use type character(1) and even if you type in 0 or 1 it will consume 8 bits. In computer language 00000000 or 00000001


    I think that will help to explain the logic behind the use of different data type in minimization when you try to explain this to your boss (just confirm my numbers are correct first ).

    And if they are still stuck on this, leave - it would appear that you have more knowledge and sense than that bloke of 20+ years of experience who has read something somewhere 20 years ago and is stuck in his ways. Minimization and optimization is one thing, but there is a reason why there are so many different datatype available in any DB system. Using chars for everything is pure idiocy, and if that's the case why bother with database, just use plain text file or csv's.

    As far as performance goes: indexing a whole bunch of varchar(50) fields will take longer than indexing a whole bunch of integer fields.


    And just for the fun of it, here is number 1 stored in a char(50) field:
    Code:
    0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001
    Last edited by psuplat; 10-09-15 at 11:16.

Tags for this Thread

Posting Permissions

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