• lyates (cs)

    select first from comments;

  • Arkady Bogdanov (unregistered)

    What The Fuuuuuhhhggggg

  • Crash Magnet (unregistered)

    For those of us who don't speak sql, what the Fuuuuuhhhggggg is wrong with this?

  • Anonymous Coward (unregistered) in reply to Crash Magnet
    Crash Magnet:
    For those of us who don't speak sql, what the Fuuuuuhhhggggg is wrong with this?

    It's multiple select statements when a single select will work just as well, and without all the declared variables, too.

  • rocksinger (unregistered) in reply to Crash Magnet
    Comment held for moderation.
  • Bri (unregistered)

    Also note that isnull is not index-friendly, so it is probably a bit slower than it should be.

  • rocksinger (unregistered) in reply to rocksinger

    eeek, I meant

    The TSQL function ISNULL(YourFieldName,'') returns '' if YourFieldName is null.

  • Huh? (unregistered)
    Comment held for moderation.
  • jonsjava (cs)
    declare @lnFirstName varchar(255) declare @lnLastName varchar(255) declare @lnEmailaddr varchar(255) declare @lnTitle varchar(255) declare @lnCompany varchar(255) declare @lnAddress varchar(255) declare @lnCity varchar(255) declare @lnState varchar(255) declare @lnZip varchar(255) declare @lnCountry varchar(255) declare @lnPhone varchar(255) declare @lnFax varchar(255)
    Ok, I know that the USPS isn't the most efficient, but I can't think of the last time I saw a zip code longer than 10 (5didgit, dash, +4). Phone number being 255? wow. I'd hate to see the phone bill associated with that long distance call.....

    I was going to go all witty with this, but there are too many WTFs associated with this.

  • Sandor (unregistered) in reply to Anonymous Coward
    Anonymous Coward:
    Crash Magnet:
    For those of us who don't speak sql, what the Fuuuuuhhhggggg is wrong with this?

    It's multiple select statements when a single select will work just as well, and without all the declared variables, too.

    Yes, but then any non-developer who looks at the code thinks that writing code is easy.

    BTW anyone who names tables "Sitr0110" should be shot.

    CAPTCHA: ********

  • tin (cs) in reply to jonsjava
    jonsjava:
    Phone number being 255? wow.

    A pre-scripted phone banking session could get that long... Though I can't think of a legal reason to store that in a database with people's names and addresses...

  • Aaron (cs) in reply to Bri
    Bri:
    Also note that isnull is not index-friendly, so it is probably a bit slower than it should be.
    Um, no?

    There is actually no non-trivial function that is "index-friendly" if used as part of a WHERE clause - only the columns themselves are indexed, and if you chuck it into any function, the optimizer has to scan the whole index. But applying a function to the query's output has almost no effect on performance unless the function itself is extremely slow, which ISNULL definitely isn't.

    This entire hunk of junk could have been written as one SELECT statement with a bunch of ISNULLs, and would run in about 1/50th of the time. Of course, the fact that nulls are being turned into empty strings suggests an even worse WTF somewhere in the code, unless this is being directly used to feed a report (and nothing else). And then you have all the varchar(255) columns, but that's the least of our worries here...

  • Ron Moses (unregistered)

    To put it in simpler terms, let's say you want to buy a dozen donuts. You tell the clerk which donuts you want; he takes each donut from the case and places it in a separate bag. Once he's got all twelve bags lined up on the counter in front of him, each with a single donut in it, he opens each bag in turn and puts the donut into a dozen box. Once all twelve donuts are in the box, he closes it and hands it to you.

    Then his co-worker, noticing your puzzled expression, comes along and explains that it's better to do it this way in case you change your mind about one of the donuts and he has to swap it out. And you look at him, and the clerk, and realize the two of them have been doing crack all morning. The end.

  • operagost (cs) in reply to Ron Moses
    Ron Moses:
    And you look at him, and the clerk, and realize the two of them have been doing crack all morning. The end.
    WIN
  • A Nonny Mouse (cs) in reply to jonsjava
    jonsjava:
    I was going to go all witty with this, but there are too many WTFs associated with this.
    yeah exactly. besides the many many things wrong with this, what's with all the unnecessary begins and ends?
  • campkev (cs) in reply to Crash Magnet
    Crash Magnet:
    For those of us who don't speak sql, what the Fuuuuuhhhggggg is wrong with this?

    Um, where to start? Let's see, there's this mess

    select
    @lnSitr0100Id      = max([Sitr0100Id])
    from Sitr0110 where [Sitr0110Id] = @lnSitr0110Id

    which does absolutely nothing.

    But the major WTF is that this whole page long crapfest could be written as:

    select @lnSitr0110Id as Response,
    IsNull(FirstName,'') as First,
    IsNull(LastName,'') as Last,
    IsNull(EmailAddress,'') as Email,
    IsNull(Title,'') as Title,
    IsNull(Company,'') as Company, 
    IsNull(Address1,'') as Address, 
    IsNull(City,'') as City, 
    IsNull(State,'') as State,
    IsNull(Zip,'') as Zip, 
    IsNull(Country,'') as Country, 
    IsNull(Phone,'') as Phone, 
    IsNull(Fax,'') as Fax, 
    @lnNew as New, 
    @lnSitr0100Id as Sitr0100Id
    where Sitr0100Id = @lnSitr0100Id
  • Bodestone (cs) in reply to A Nonny Mouse
    A Nonny Mouse:
    yeah exactly. besides the many many things wrong with this, what's with all the unnecessary begins and ends?

    I always wrap my condiational code in BEGIN and END in SQL (and other where you only need it for multi-liners), even if it's a one liner.

    There is no performance issue and it means you are using the same convention for all condition statements.

    Not to mention the cut and paste issues I've seen with people who don't.

  • SR (unregistered) in reply to campkev
    Comment held for moderation.
  • Engival (unregistered) in reply to campkev
    Comment held for moderation.
  • ubersoldat (cs)

    I like donuts... and crack...

  • Anonymous (unregistered) in reply to Ron Moses
    Ron Moses:
    To put it in simpler terms, let's say you want to buy a dozen donuts. You tell the clerk which donuts you want; he takes each donut from the case and places it in a separate bag. Once he's got all twelve bags lined up on the counter in front of him, each with a single donut in it, he opens each bag in turn and puts the donut into a dozen box. Once all twelve donuts are in the box, he closes it and hands it to you.

    Then his co-worker, noticing your puzzled expression, comes along and explains that it's better to do it this way in case you change your mind about one of the donuts and he has to swap it out. And you look at him, and the clerk, and realize the two of them have been doing crack all morning. The end.

    So can I swap one of the donuts for a big bag of crack? Sounds like they're set up to accomodate such a situation and I don't really need all twelve donuts. Eleven donuts and a big bag of crack should keep me occupied just fine for the whole rest of the day.

  • RonMexico (unregistered) in reply to Bodestone
    Bodestone:
    A Nonny Mouse:
    yeah exactly. besides the many many things wrong with this, what's with all the unnecessary begins and ends?

    I always wrap my condiational code in BEGIN and END in SQL (and other where you only need it for multi-liners), even if it's a one liner.

    There is no performance issue and it means you are using the same convention for all condition statements.

    Not to mention the cut and paste issues I've seen with people who don't.

    Ditto. I use braces in C#/Javascript for single-line if and loop clauses also. Clarity, maintainability, all that.

  • Mike Dimmick (unregistered)
    Comment held for moderation.
  • campkev (cs) in reply to Engival
    Engival:
    campkev:
    Crash Magnet:
    For those of us who don't speak sql, what the Fuuuuuhhhggggg is wrong with this?

    Um, where to start? Let's see, there's this mess

    select
    @lnSitr0100Id      = max([Sitr0100Id])
    from Sitr0110 where [Sitr0110Id] = @lnSitr0110Id

    which does absolutely nothing.

    You're missing the subtle difference between 0110 and 0100. Any interaction between those tables must be pure joy to work with.

    Crap, you're right. Add dumbass table names to the list of WTF's

  • Bodestone (cs)

    And why do all the variables start @ln, or is it @In?

    That's dafter than tables starting tbl.

  • Dana (unregistered) in reply to Bodestone
    Comment held for moderation.
  • Patrick (unregistered)

    $sp=' '; $comment1='this'.$sp; $comment2='way'.$sp; $comment3='you'.$sp; $comment4='can'.$sp; $comment5='swap'.$sp; $comment6='words'.$sp; $comment7='around'; $comment=$comment1.$comment2.$comment3.$comment4.$comment5.$comment6.$comment7.'.'; echo $comment;

  • snoofle (cs) in reply to jonsjava
    jonsjava:
    declare @lnFirstName varchar(255) declare @lnLastName varchar(255) declare @lnEmailaddr varchar(255) declare @lnTitle varchar(255) declare @lnCompany varchar(255) declare @lnAddress varchar(255) declare @lnCity varchar(255) declare @lnState varchar(255) declare @lnZip varchar(255) declare @lnCountry varchar(255) declare @lnPhone varchar(255) declare @lnFax varchar(255)
    Ok, I know that the USPS isn't the most efficient, but I can't think of the last time I saw a zip code longer than 10 (5didgit, dash, +4). Phone number being 255? wow. I'd hate to see the phone bill associated with that long distance call.....

    I was going to go all witty with this, but there are too many WTFs associated with this.

    You should see the phone bills we get here at StarFleet!

  • Junkie (cs) in reply to ubersoldat
    ubersoldat:
    I like donuts... and crack...
    Donuts with powdered crack - yummy!
  • Junkie (cs) in reply to Mike Dimmick
    Mike Dimmick:
    It's rather like your partner giving you a shopping list and instead of driving to the shop, buying everything on the list and returning home, you instead drive to the shop, buy the first item, drive home, drop it off, drive back to the shop, buy the second item, drive home, etc.

    If you're using SQL you should aim to retrieve all the values you need in one statement. It helps if you understand that the database has to work pretty hard to find any particular row, but virtually no extra effort to retrieve additional values associated with that row.

    This particular query could have been rewritten as:

    SELECT TOP 1
       Sitr0100Id AS Response,
       FirstName AS First,
       LastName AS Last,
       EmailAddress AS Email,
       Title,
       Company,
       Address1 AS Address,
       City,
       State,
       Country,
       Phone,
       NULL AS New, -- @lnNew isn't set
       Sitr0100Id AS Sitr0100Id
    FROM
       Sitr0100
    ORDER BY Sitr0100Id DESC

    (ordering by the ID in reverse and taking the top result is the same as selecting MAX(Sitr0100Id))

    Not to mention the chance that the data in the row may change between calls.

  • Beaker (unregistered) in reply to Bodestone

    I originally thought it could be some sort of Hungarian Notation (some find that to be a bad practice too), but there's no rhyme or reason to the prefix.

    As for the isNull function, the code base that is calling the SQL statement should be handling those nulls. It's one thing if you have to concatenate values in the query (concatenating a string and a null in SQL yields a null), but that's not the case here.

  • SR (unregistered) in reply to Dana
    Comment held for moderation.
  • Patrick (unregistered) in reply to Bodestone
    Comment held for moderation.
  • Ken B (unregistered) in reply to Ron Moses
    Ron Moses:
    To put it in simpler terms, let's say you want to buy a dozen donuts. You tell the clerk which donuts you want; he takes each donut from the case and places it in a separate bag. Once he's got all twelve bags lined up on the counter in front of him, each with a single donut in it, he opens each bag in turn and puts the donut into a dozen box. Once all twelve donuts are in the box, he closes it and hands it to you.
    Close, but not quite.

    It's more like the clerk takes a bag, goes to the donuts, places one in the bag, and then puts the bag in a table in back. He then gets another bag, goes to the donuts, places one in the bag, and then puts the bag in a table in back. Repeat.

    (ie: Your version could be done by taking twelve bags and placing one donut in each, whereas the entire "take a bag, put a donut in it, and put the bag in a temporary storage area" needs to be completely done, from start to finish, twelve separate times.)

  • akatherder (cs)

    I don't know if this is better or worse than:

    $rs = $myconn.execute("Select * from sitr0100");
    
    for ($i=0; $<$rs.length(); $i++)
    {
      if ($rs["id"]==$myId)
      {
        $firstname = $rs["FirstName"];
        $lastname = $rs["LastName"];
        $email = $rs["EmailAddress"];
        etc..
      }
    }
    
  • Ken B (unregistered) in reply to ubersoldat
    ubersoldat:
    I like donuts... and crack...
    Then you probably love plumbers on their lunch break. :-)
  • Ken B (unregistered) in reply to akatherder
    akatherder:
    I don't know if this is better or worse than:
    $rs = $myconn.execute("Select * from sitr0100");
    
    for ($i=0; $<$rs.length(); $i++)
    {
      if ($rs["id"]==$myId)
      {
        $firstname = $rs["FirstName"];
        $lastname = $rs["LastName"];
        $email = $rs["EmailAddress"];
        etc..
      }
    }
    
    "WHERE clause"? What's a "WHERE clause"?
  • rfsmit (cs) in reply to RonMexico
    RonMexico:
    Bodestone:
    A Nonny Mouse:
    yeah exactly. besides the many many things wrong with this, what's with all the unnecessary begins and ends?

    I always wrap my condiational code in BEGIN and END in SQL (and other where you only need it for multi-liners), even if it's a one liner.

    There is no performance issue and it means you are using the same convention for all condition statements.

    Not to mention the cut and paste issues I've seen with people who don't.

    Ditto. I use braces in C#/Javascript for single-line if and loop clauses also. Clarity, maintainability, all that.
    And for the same reason, when I'm done testing with
    [[ $? -ne 0 ]] && failed || success
    I unroll that into
    if [ $? -ne 0 ]; then
    failed
    else
    success
    fi
    It's only polite.

  • Yazeran (unregistered) in reply to Mike Dimmick
    Mike Dimmick:
    (ordering by the ID in reverse and taking the top result is the same as selecting MAX(Sitr0100Id))

    Fail!

    In output yes, but in performance NO! Max() / Min() is O(n) whereas sort (ORDER) is at best O(n log(n))

    For small n it doesn't matter, but for large n.....

    In this particular case it likely doesn't matter (as the application will likely be slow as molasses anyways considering that SQL) :-)

    Yours Yazeran.

    Plan: To go to Mars one day with a hammer.

  • JoeKu (unregistered) in reply to campkev
    Comment held for moderation.
  • A DBA (unregistered) in reply to Sandor
    Sandor:
    BTW anyone who names tables "Sitr0110" should be shot.

    Okay! Okay! I'll change the name of the table to "babysitters_born_jan_10"! Don't shoot! Please!

  • jasmine2501 (cs) in reply to Bri

    Isnull doesn't matter a whole lot unless it's used in the WHERE clause. In the SELECT clause, it only adds a "compute scalar" to the query plan.

    But still... this is why we need to actually test people who SAY they know SQL... because usually, they don't. In an interview I was given once, I was told I was the only person who ever passed their SQL test, and it was ridiculously easy, but I wasn't surprised that nobody did well on it because they were looking for C# programmers, who also "know SQL"

  • ppp (unregistered) in reply to jonsjava
    Comment held for moderation.
  • Ibi-Wan Kentobi (unregistered) in reply to Yazeran
    Yazeran:
    Mike Dimmick:
    (ordering by the ID in reverse and taking the top result is the same as selecting MAX(Sitr0100Id))

    Fail!

    In output yes, but in performance NO! Max() / Min() is O(n) whereas sort (ORDER) is at best O(n log(n))

    For small n it doesn't matter, but for large n.....

    If you were searching through a flat file yes, but in a database NO!

    Max(), Min(), and SELECT TOP 1 FROM ... ORDER BY are all O(1) in a table indexed by the field in question -- which this table certainly should be, given this usage.

  • Salami (cs)

    The best part is that the coworker had no problem with it.

  • jgreen (cs) in reply to Anonymous
    Anonymous:
    Ron Moses:
    To put it in simpler terms, let's say you want to buy a dozen donuts. You tell the clerk which donuts you want; he takes each donut from the case and places it in a separate bag. Once he's got all twelve bags lined up on the counter in front of him, each with a single donut in it, he opens each bag in turn and puts the donut into a dozen box. Once all twelve donuts are in the box, he closes it and hands it to you.

    Then his co-worker, noticing your puzzled expression, comes along and explains that it's better to do it this way in case you change your mind about one of the donuts and he has to swap it out. And you look at him, and the clerk, and realize the two of them have been doing crack all morning. The end.

    So can I swap one of the donuts for a big bag of crack? Sounds like they're set up to accomodate such a situation and I don't really need all twelve donuts. Eleven donuts and a big bag of crack should keep me occupied just fine for the whole rest of the day.

    DOUBLE WIN

  • Ibi-Wan Kentobi (unregistered) in reply to ppp
    ppp:
    As for me, I insist on using NUMBER(0, 1) for my booleans. Sure, it's another version, but at least it's right.

    And the nominee for The New Real WTF is...

    (You don't think the first four people all thought "at least MINE is right"?)

  • Coward (unregistered) in reply to ppp
    ppp:
    (snip) As for me, I insist on using NUMBER(0, 1) for my booleans. Sure, it's another version, but at least it's right.

    Why would you use a decimal?

    Wouldn't it make more sense to use a NUMBER(1)?

  • Sandor (unregistered) in reply to A DBA
    A DBA:
    Sandor:
    BTW anyone who names tables "Sitr0110" should be shot.

    Okay! Okay! I'll change the name of the table to "babysitters_born_jan_10"! Don't shoot! Please!

    Ow great! now i can use SELECT * FROM babysitters_born_jan_10 in stead of:

    SELECT h.* FROM HUMANS INNER JOIN JobDescriptions j on j.ID = h.JobDescriptionID WHERE j.Description = 'Babysitter' AND DATEPART (m, h.BirthDay) = 1 AND DATEPART (d, h.BirthDay) = 10

    which allows for much faster coding :) and a lot of tables yeah

  • Code Dependent (cs) in reply to ubersoldat
    ubersoldat:
    I like donuts... and crack...
    Here ya go. [image]

Leave a comment on “A Confusing SELECTion”

Log In or post as a guest

Replying to comment #:

« Return to Article