Results 1 to 15 of 15
  1. #1
    Join Date
    Jan 2008
    Posts
    186

    Unanswered: Performance - string vs number?

    Hi guys,

    I have 2 tables:
    Code:
    Unit( unit_id integer, unit_name varchar(20) )
    Part( part_id integer, part_name varchar(20) )
    Then I have a join table between the two
    Code:
    Unit_Part( unit_id, part_id )
    Each unit NAME and part NAME is unique. So what kind of performance would I be losing if I were to do something like:
    Code:
    PROCEDURE addPartToUnit( VARCHAR(20) sPartName, VARCHAR(20) sUnitName )
    -- Now in this procedure i'll just get the id's via the names from a select statement
    Versus
    Code:
    PROCEDURE addPartToUnit( nPartId INTEGER, nUnitId INTEGER )
    -- In this proc, i directly input the primary keys of the table so no lookup is necessary
    The reason is that I would like for developers to be able to write code without having to know the id's.

    What do you think?

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    So what kind of performance would I be losing if I were to do something like:
    Unless you are having hundreds of queries per second, probably not enough that you would notice.

    The reason is that I would like for developers to be able to write code without having to know the id's.
    Why? Do they fervently believe that people walk into the store and say "I want UnitID #158" as opposed to "I want a Wankel Rotary Engine"?

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    why would the developer be hard coding IDs in the code anywho?
    “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.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The usual reasons include, but are not limited to:

    1) Arrogance
    2) Ignorance

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by MCrowley
    The usual reasons include, but are not limited to:

    1) Arrogance
    2) Ignorance
    nothing that a little best practice b*tch slapping would not cure.
    “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.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Developers should never code ids or names, that is just asking for trouble.

    Users should never even realize that id values exist, so they should never be tempted to change the id values. Users often see names, so sooner or later they will want to change them, whether the code gives them a convenient way to do that or they have to throw temper tantrums with management to get IT to make the changes for them. Based on this argument alone, if you have to code anything, code the id instead of the name.

    One more time for emphasis: Developers should never code id or name values as constants in anything, that is a recipe for trouble at some point in the future.

    -PatP

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What do they code then Pat?

    Code:
    INSERT INTO widgets (ID, widg_name, is_a_doodah)
    SELECT 769882, 'doodah', 1
    Code:
    SELECT *
    FROM widgets
    WHERE doodah = 1
    ??
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you uhhhh pass parameters to queries right? Or do you have a bunch of stored procs that go

    Code:
    Create Proc GetRecord1
    as
    SELECT * FROM MyTable WHERE ID = 1
    
    GO
    
    Create Proc GetRecord2
    as
    SELECT * FROM MyTable WHERE ID = 2
    
    GO
    
    etc...
    “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. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Pootle: Code reviews ought to catch and eliminate errors like coding constant id values into applications. I see that as either sloppy or outright dangerous as a coding practice.

    Scripts used by a DBA might need to contain id values, for example when they need to change the name or description associated with that id.

    Programs should always try to operate on data in ways that the user can't contaminate, such as via some attribute that has only a few carefully controlled values. As an example, if you want to control what products get charged VAT your code should refer to a column for VAT (boolean, flag, percentage doesn't matter for this discussion) and not hardcode a list of either id values or product descriptions.

    -PatP

  10. #10
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    One point to make: constant subqueries ought to be easily optimized.

    For example, say I have a table with ID and City, and I want to search for Philadelphia:

    SELECT foo FROM Addresses WHERE CityID = (SELECT ID From Cities WHERE City = 'Philadelphia')

    That can be recast as a join:

    SELECT foo FROM Addresses INNER JOIN Cities ON Address.CityID = Cities.ID WHERE Cities.City = 'Philadelphia'

    And if there's a UNIQUE index on Cities.City, the optimizer can eliminate the join and replace it with a filter:

    SELECT foo FROM Addresses WHERE CityID = 1234

    I don't have a DBMS handy with a good statement analyzer (for shame!) but I'd like to see if any perform these optimizations.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Pat Phelan
    Pootle: Code reviews ought to catch and eliminate errors like coding constant id values into applications.
    I wasn't querying that.

    I was querying this:
    Quote Originally Posted by Pat Phelan
    Developers should never code ids or names
    Quote Originally Posted by Thrasymachus
    you uhhhh pass parameters to queries right?
    Obviously I'm not talking about a query where the value would vary. That's why it's called a constant
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    What do they code then Pat?
    They reference either a header file or a configuration file or even a configuration table, depending on what's appropriate. Either way, the "SELECT foo, bar FROM MOOOOOOO WHERE doodah = $magic_number" now references a documented value. And if you decide to change that value, it's easy to change it everywhere in the app.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I use config files for constants but not for something like we are discussing.
    Code:
    CREATE VIEW doodahs
    AS
    
    SELECT *
    FROM WIDGETS
    WHERE widget_name = (SELECT doodah_text FROM sys_params)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just curious, but when was the last time you actually coded an ID value in a production application?

    While I code ID values into administration scripts almost every day, it has been at least a decade since I've hardcoded an ID value into an executable that was distributed to users. In general, if I show something to a user, I expect them to change it at some point in time. If the current management tells me that "Values 1-10 are yours, 11 on are for users" then I'm pretty confident that a subsequent generation of management will decide "All values are the user's". I take almost any management pronouncement about data and add the suffix "for now" automatically.

    I am a very, VERY cynical soul when it comes to system management. If the user can see it, they will eventually want to change it. This applies to management just as well as any other user. If I need "majik values" in a table, I include a column that I can query to find those majik values. Since we often have a column that reflects "active / inactive" to indicate status for a row, I often allow "S" for system too. That way I can have values in ordinary tables, the users can't see or change them, but I can query or code for them easily.

    -PatP

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Pat Phelan
    Just curious, but when was the last time you actually coded an ID value in a production application?
    Doooo--oooode - do you read my posts???//??/

    I never ever hard code IDs**. (Assuming by ID you mean surrogates) should never be hard coded. A surrogate is, however, a surrogate for a natural key.
    Quote Originally Posted by Par Phelan
    Developers should never code ids or names, that is just asking for trouble.
    That's the bit I am querying. Never hard code the natural key? I just think that this is one of those rules that should end "... except when it is appropriate to do so".

    ** EDIT - in prod code.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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