• kiasyn (unregistered)

    sigh

  • Anon (unregistered)

    Its amazing how many 'database developers' don't know anything about little things like indexes and such.

    (oh, and hopefully this is the very first comment, but i'm going to break tradition and not say...it)

  • Apostle (unregistered)

    I think they should call Luke in to do a code review

  • (cs)
    Bruce Johnson:
    "They referenced a 'new_id' field. A field which, true to its name, hadn't existed in the schema until just this past weekend"
    There. FTFY.
  • (cs)

    Lsat?

  • C10B (unregistered)

    ZZZZZZZZzzzzzzzzzzzzzzzzzzzzzzzzzzz Boring. A fields wasn't indexed, and you made a whole frikkin story about it. This site is officially no longer entertaining.

  • Martin (unregistered)

    This is not the WTF, this is just daily routine

  • annon (unregistered)

    He should have left it for John to fix. After all, it's not like he was going to get any credit for the fix, and the comedy value of Johns next fix may have been worth it.

  • (cs) in reply to C10B
    C10B:
    ZZZZZZZZzzzzzzzzzzzzzzzzzzzzzzzzzzz Boring. A fields wasn't indexed, and you made a whole frikkin story about it. This site is officially no longer entertaining.

    Gotta agree - that was way longer than it should have been - shoulda just stayed in the Side Bar. Not enough new story fodder coming through?

  • (cs)

    On one hand side not using source control should be a strong signal of lack of quality on the side of developer as it is on the side of company (software team) as in The Joel Test

    On the other hand side version control (source control) of databases is much harded than of code... though solutions such as Sqitch exist to help with this.

  • (cs)

    Just imagine the kudos John is going to get when Matt gets bored enough to wander through the DB indexing everything else that should be indexed!

  • (cs) in reply to annon
    annon:
    He should have left it for John to fix. After all, it's not like he was going to get any credit for the fix, and the comedy value of Johns next fix may have been worth it.

    True. Right up until the point that join plunges the company into bankruptcy.

  • Anonymous coward (unregistered) in reply to eViLegion

    A join that plunges a company into bankruptcy must be some really bad SQL...

  • drake (unregistered) in reply to faoileag
    faoileag:
    Bruce Johnson:
    "They referenced a 'new_id' field. A field which, true to its name, hadn't existed in the schema until just this past weekend"
    There. FTFY.

    Clearly you haven't worked that much with developers like John here - I would expect 'new_id' to be neither new nor an id.

  • EvilSnack (unregistered)

    Sounds like the Daily Dogbert to me.

    Captcha 'consequat': What you eat after a kumquat.

  • (cs) in reply to drake
    drake:
    faoileag:
    Bruce Johnson:
    "They referenced a 'new_id' field. A field which, true to its name, hadn't existed in the schema until just this past weekend"
    There. FTFY.

    Clearly you haven't worked that much with developers like John here - I would expect 'new_id' to be neither new nor an id.

    Oh, I just assumed an editorial glitch on Bruce's side there.

    But having re-read the story now... how can reversing the table can make thinks faster in a (assumingly relational) database?

    On, say 10 million records, is "SELECT id, Name FROM foo WHERE (ID > 8000000 AND ID < 9000000);" really slower than "SELECT id, Name FROM foo WHERE (ID > 1000000 AND ID < 2000000);"??

    And is reversing the table even possible? Wouldn't John have to reverse the table after every new insert again?

    This story sounds weird...

  • (cs)

    This is trivial. I once outsourced. The site I got back just about worked (although quality assurance would fine a hundred bugs in an hour or two), but as people started to sign up started to slow very quickly. I took a look at the slow log and there's this 20 line query on two tables with only a hundred rows each full of unions, several joins and so on that returned thousands of rows. What was this for? To check if a user existed in a group. It actually worked, but incredibly slowly.

  • Andrew (unregistered)

    I'm wondering why there had to be a "new_id" in the frist place. Since that's not explained, I want to think that was TRWTF.

  • (cs) in reply to faoileag
    faoileag:
    And is reversing the table even possible? Wouldn't John have to reverse the table after every new insert again?
    While that strictly depends on what the database chooses to do by default, I think you'll find that that's a large part of the point. Whatever happens to be the case at the moment with the current order of the rows, it's bound to not stay right for the particular queries to be quick and the fix — that everyone who knows the slightest thing about a real DB system will be able to tell you — is to add an index.

    Which means that John's actually a noob (or a nitwit) ant TRWTF is how he is idolized.

  • (cs) in reply to Anonymous coward
    Anonymous coward:
    A join that plunges a company into bankruptcy must be some *really* bad SQL...

    Heh... I would go back and correct the typo, but what you said is much better, so I'll leave it.

  • Mike (unregistered) in reply to Anon

    "Its amazing how many 'database developers' don't know anything about little things like indexes and such. " The data goes into the database and comes back out right? No one said it needs to be fast :)

  • moving through space (unregistered) in reply to faoileag
    faoileag:
    drake:
    faoileag:
    Bruce Johnson:
    "They referenced a 'new_id' field. A field which, true to its name, hadn't existed in the schema until just this past weekend"
    There. FTFY.

    Clearly you haven't worked that much with developers like John here - I would expect 'new_id' to be neither new nor an id.

    Oh, I just assumed an editorial glitch on Bruce's side there.

    But having re-read the story now... how can reversing the table can make thinks faster in a (assumingly relational) database?

    On, say 10 million records, is "SELECT id, Name FROM foo WHERE (ID > 8000000 AND ID < 9000000);" really slower than "SELECT id, Name FROM foo WHERE (ID > 1000000 AND ID < 2000000);"??

    And is reversing the table even possible? Wouldn't John have to reverse the table after every new insert again?

    This story sounds weird...

    yeah, unless the 'new_id' field were indexed, it would result in a table scan either way i'd imagine. And, if the data were ordered by the 'new_id' field and then stored, wouldn't that make it a clustered index? If not, still a table scan.

  • (cs)
    it was taking too long to get to the bottom of the data

    Anyone who says this shouldn't be near a database.

  • moving through space (unregistered) in reply to moving through space
    moving through space:
    faoileag:
    drake:
    faoileag:
    Bruce Johnson:
    "They referenced a 'new_id' field. A field which, true to its name, hadn't existed in the schema until just this past weekend"
    There. FTFY.

    Clearly you haven't worked that much with developers like John here - I would expect 'new_id' to be neither new nor an id.

    Oh, I just assumed an editorial glitch on Bruce's side there.

    But having re-read the story now... how can reversing the table can make thinks faster in a (assumingly relational) database?

    On, say 10 million records, is "SELECT id, Name FROM foo WHERE (ID > 8000000 AND ID < 9000000);" really slower than "SELECT id, Name FROM foo WHERE (ID > 1000000 AND ID < 2000000);"??

    And is reversing the table even possible? Wouldn't John have to reverse the table after every new insert again?

    This story sounds weird...

    yeah, unless the 'new_id' field were indexed, it would result in a table scan either way i'd imagine. And, if the data were ordered by the 'new_id' field and then stored, wouldn't that make it a clustered index? If not, still a table scan.
    Unless he's doing something really hideous like using a cursor to search and stopping the cursor when the record he wants is found...

  • (cs) in reply to moving through space
    moving through space:
    yeah, unless the 'new_id' field were indexed, it would result in a table scan either way i'd imagine. And, if the data were ordered by the 'new_id' field and then stored, wouldn't that make it a clustered index? If not, still a table scan.

    Or the query was a "select top 1 where new_id=@value"... then yes, a table scan but one which terminates after scanning only a small percentage of the table if he value is in a row near the beginning...

  • ¯\(°_o)/¯ I DUNNO LOL (unregistered)

    I'm going to guess that John's last name was "Crapper", as evidenced by how easily he put performance into the toilet.

  • Anom anom anom (unregistered) in reply to C10B
    C10B:
    ZZZZZZZZzzzzzzzzzzzzzzzzzzzzzzzzzzz Boring. A fields wasn't indexed, and you made a whole frikkin story about it. This site is officially no longer entertaining.

    Quoted For Undeniable Truth

  • (cs) in reply to annon
    annon:
    He should have left it for John to fix. After all, it's not like he was going to get any credit for the fix, and the comedy value of Johns next fix may have been worth it.
    Exactly. John had already reversed the table once, what would he do this time? Turn it inside out? I'd much rather know John's next fix than know Matthew's obvious one, and the inevitable outcome.
  • geebag (unregistered) in reply to moving through space
    moving through space:
    faoileag:
    drake:
    faoileag:
    Bruce Johnson:
    "They referenced a 'new_id' field. A field which, true to its name, hadn't existed in the schema until just this past weekend"
    There. FTFY.

    Clearly you haven't worked that much with developers like John here - I would expect 'new_id' to be neither new nor an id.

    Oh, I just assumed an editorial glitch on Bruce's side there.

    But having re-read the story now... how can reversing the table can make thinks faster in a (assumingly relational) database?

    On, say 10 million records, is "SELECT id, Name FROM foo WHERE (ID > 8000000 AND ID < 9000000);" really slower than "SELECT id, Name FROM foo WHERE (ID > 1000000 AND ID < 2000000);"??

    And is reversing the table even possible? Wouldn't John have to reverse the table after every new insert again?

    This story sounds weird...

    yeah, unless the 'new_id' field were indexed, it would result in a table scan either way i'd imagine. And, if the data were ordered by the 'new_id' field and then stored, wouldn't that make it a clustered index? If not, still a table scan.

    More recently-added data was probably being accessed more often, so the value for new_id would be higher for new records. He probably reversed the whole table, ordering it by new_id desc. Or something. Only way I can think of that a table scan would be faster.

  • (cs) in reply to flabdablet
    flabdablet:
    Just imagine the kudos John is going to get when Matt gets bored enough to wander through the DB indexing everything else that should be indexed!
    The real reason John hates version control is that everyone would see the actual fixes were all committed by Matt while the "fixes" that broke things were all committed by John.
  • (cs)
    "Great news. I found the problem and have already taken care of it", he said.

    Right, thought Matt. Because patching directly into production is exactly what should be done. <SNIP> On Monday, Matt received a call that the application was still running slowly. With a gentle sigh, Matt asked them to hold on for a couple of moments. A quick fiddle with some DDL, and the 'new_id' field was indexed.

    "How is it now?"

    Asked Matt, after patching directly into production himself.

  • C-Derb (unregistered) in reply to Some Damn Yank
    Some Damn Yank:
    "Great news. I found the problem and have already taken care of it", he said.

    Right, thought Matt. Because patching directly into production is exactly what should be done. <SNIP> On Monday, Matt received a call that the application was still running slowly. With a gentle sigh, Matt asked them to hold on for a couple of moments. A quick fiddle with some DDL, and the 'new_id' field was indexed.

    "How is it now?"

    Asked Matt, after patching directly into production himself.
    Relative to what John was doing over the weekend, Matt's patch into production was very low risk. But good luck explaining the difference to John and his sheep.

  • (cs) in reply to moving through space
    moving through space:
    faoileag:
    <<snippage>> Oh, I just assumed an editorial glitch on Bruce's side there.

    But having re-read the story now... how can reversing the table can make thinks faster in a (assumingly relational) database?

    On, say 10 million records, is "SELECT id, Name FROM foo WHERE (ID > 8000000 AND ID < 9000000);" really slower than "SELECT id, Name FROM foo WHERE (ID > 1000000 AND ID < 2000000);"??

    And is reversing the table even possible? Wouldn't John have to reverse the table after every new insert again?

    This story sounds weird...

    yeah, unless the 'new_id' field were indexed, it would result in a table scan either way i'd imagine. And, if the data were ordered by the 'new_id' field and then stored, wouldn't that make it a clustered index? If not, still a table scan.
    <John>Hey, don't use big words like "table scan"!</John>

    I'm betting 100 quatloos that John had no idea what a table scan is.

  • El Guaco (unregistered) in reply to geebag

    A table scan in the order of the column being searched would indeed find the lowest or highest numbers first depending on the natural sort order.

    That said, Oracle, MSSQL and MySql will create a clustered index on the primary key by default. If he's added an additional column, he would have to explicitly add a non-clustered index to the new column. I'm not even sure it's possible to "sort" a table on anything but the primary key using the clustered index.

    Matt should not have added the index without sending an explanation of why his fix worked to John. Give the guy the benefit of the doubt and a little education. If he won't listen, Matt needs to start looking for his next job.

  • (cs)

    A third-party developer with access to production?

    Any place that I've worked, a third-party developer needs a background check to even work in an isolated development environment.

  • (cs) in reply to El Guaco
    El Guaco:
    I'm not even sure it's possible to "sort" a table on anything but the primary key using the clustered index.

    Unless I don't understand you - a possibility, I grant you - what do you think non-clustered indexes are for?

  • RFoxmich (unregistered)

    "an industry veteran and was held in very high regard" was TRWTF

  • reverser (unregistered)

    new_id = max(id) + 1 - id

    Gets the reverse order. But it still doesn't make sense to me.

    If another table references the id then its all gone to heck. If the id isn't referenced, then how does it have any effect.

  • moving through space (unregistered) in reply to TheCPUWizard
    TheCPUWizard:
    moving through space:
    yeah, unless the 'new_id' field were indexed, it would result in a table scan either way i'd imagine. And, if the data were ordered by the 'new_id' field and then stored, wouldn't that make it a clustered index? If not, still a table scan.

    Or the query was a "select top 1 where new_id=@value"... then yes, a table scan but one which terminates after scanning only a small percentage of the table if he value is in a row near the beginning...

    True enough for existing records and if no 'ORDER BY' clause is present. Probably why it ran fine until Monday; presumably the new records went to the end and caused a near-full table scan again.

  • (cs)
    "Wow, this is really fast. And fixed quickly too. That John guy is amazing!"

    What's amazing is that "that John guy" will probably be allowed to live to produce offspring.

  • moving through space (unregistered) in reply to El Guaco
    El Guaco:
    A table scan in the order of the column being searched would indeed find the lowest or highest numbers first depending on the natural sort order.

    That said, Oracle, MSSQL and MySql will create a clustered index on the primary key by default. If he's added an additional column, he would have to explicitly add a non-clustered index to the new column. I'm not even sure it's possible to "sort" a table on anything but the primary key using the clustered index.

    Matt should not have added the index without sending an explanation of why his fix worked to John. Give the guy the benefit of the doubt and a little education. If he won't listen, Matt needs to start looking for his next job.

    Yes. Yes, you can. You may only have one clustered index, but it does not have to be the primary key.
  • rcombs (unregistered)

    ♫ We don't need no git/subversion ♫ We don't need no source control ♫

  • Anon (unregistered) in reply to C10B

    You notified the Office of No Longer Entertaining Sites, did you?

  • (cs)

    Can somebody explain to me why database people are just plain weird? It crops up in the stories here, and it just takes a few lines and you find out that:

    1. The problem is with the database.
    2. The administrator probably setup the whole thing wrong.
    3. The users of the database usually want to "select *"
    4. If you suggest a change (usually for efficiency) they get all flustered, and reject it out of hand.
    5. The "process" dictates some silly constraint.
    6. Stored procedures somewhere used in the wrong way.
    7. In the end they are trying to use Access, or some unscalable pile of dung.

    Is there any hope?

  • trtrwtf (unregistered) in reply to herby
    herby:
    Is there any hope?

    No, there is not. We continue on from force of habit, nothing more. Now go read some Beckett, it'll all make sense.

  • dilligaf (unregistered) in reply to RFoxmich
    RFoxmich:
    "an industry veteran and was held in very high regard" was TRWTF

    Yes and no ... these people are all over the place. I can't begin to count how many Johns (hilarious name in this context) I've run into who have their hooks way into my client and the great challenge is loosening their grip without causing them to go postal and do some real damage to the customer before I can reign them in.

  • (cs) in reply to Some Damn Yank
    Some Damn Yank:
    annon:
    He should have left it for John to fix. After all, it's not like he was going to get any credit for the fix, and the comedy value of Johns next fix may have been worth it.
    Exactly. John had already reversed the table once, what would he do this time? Turn it inside out? I'd much rather know John's next fix than know Matthew's obvious one, and the inevitable outcome.

    Duh, it's obvious.

    No one asked to search the middle. So stagger a beginning and end entry towards the middle.

    1,10,2,9,3,8,4,6,5

    Then doing a search for large values or small values will be just as fast.

  • s73v3r (unregistered) in reply to faoileag
    faoileag:
    drake:
    faoileag:
    Bruce Johnson:
    "They referenced a 'new_id' field. A field which, true to its name, hadn't existed in the schema until just this past weekend"
    There. FTFY.

    Clearly you haven't worked that much with developers like John here - I would expect 'new_id' to be neither new nor an id.

    Oh, I just assumed an editorial glitch on Bruce's side there.

    But having re-read the story now... how can reversing the table can make thinks faster in a (assumingly relational) database?

    On, say 10 million records, is "SELECT id, Name FROM foo WHERE (ID > 8000000 AND ID < 9000000);" really slower than "SELECT id, Name FROM foo WHERE (ID > 1000000 AND ID < 2000000);"??

    And is reversing the table even possible? Wouldn't John have to reverse the table after every new insert again?

    This story sounds weird...

    It's scary to think about, but he might have been iterating over each and every 'new_id', looking for the one he needed. In that way, yes, higher numbers would be slower than lower ones.

  • (cs) in reply to Some Damn Yank
    Some Damn Yank:
    annon:
    He should have left it for John to fix. After all, it's not like he was going to get any credit for the fix, and the comedy value of Johns next fix may have been worth it.
    Exactly. John had already reversed the table once, what would he do this time? Turn it inside out? I'd much rather know John's next fix than know Matthew's obvious one, and the inevitable outcome.
    Well that option is still on the table for Matt. Just drop the index, then tell John his wonderful crapsmanship is required again!

    Bonus is that John has no idea why his cantations first got so many positive reviews, but now are failing again.

  • (cs) in reply to moving through space
    moving through space:
    Unless he's doing something really hideous like using a cursor to search and stopping the cursor when the record he wants is found...

    Do you think he'd be smart enough to stop the cursor? :)

Leave a comment on “Welcome to the New Order”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article