• (disco) in reply to tarunik
    tarunik:
    INNER JOIN ON INSTR(list_of_children, child_pk) > 0
    <!--seriously-->WAT
  • (disco) in reply to tarunik

    Hey, are you arguing with me because we agree?

  • (disco) in reply to RaceProUK

    Indices don't avoid locking tables or table sections. Depending on your query, you then need to lock multiple tables or sections, because you have that JOIN. The real performance killer is the lock; the JOIN is just the method to deliver it.

  • (disco) in reply to unwesen

    If you have a JOIN and you need a lock, then you'll need that lock anyway, even if you get rid of the JOIN

    <!-- Emoji'd by MobileEmoji 0.2.0-->
  • (disco) in reply to RaceProUK

    One lock vs. multiple locks.

    I'm not even going to explain that.

  • (disco) in reply to unwesen
    unwesen:
    One lock vs. multiple lock**s**.

    So, lock absolutely everything or have the ability to long only part of the database? (The really expensive things in databases are write-transaction commits. Unless you're using SSDs…)

  • (disco) in reply to unwesen
    unwesen:
    One lock vs. multiple lock**s**.
    Yes, because obviously you need to lock *every* table in a query, not just the one or two that actually need locking… <!-- Emoji'd by MobileEmoji 0.2.0-->
  • (disco) in reply to unwesen
    unwesen:
    Indices don't avoid locking tables or table sections. Depending on your query, you then need to lock multiple tables or sections, because you have that JOIN. The real performance killer is the lock; the JOIN is just the method to deliver it.

    What, have you never heard of MVCC? Locking everything under the sun is so 90s era...

    unwesen:
    Hey, are you arguing with me because we agree?
    No -- I'm saying you're making a false assumption (that small databases don't exist outside of college).

    Bonus kicker: You know that list_of_children column? It's a CLOB, as some of those key-lists are over twice the length limit of an Oracle VARCHAR2.

  • (disco) in reply to dkf

    Yeah, that's what I thought when I first saw those columns -- it's the only way you can run joins on them though!

    You know someone botched the database design when you have a query that takes over an hour to return 500 rows...

    (We have basically 0 control over it, too -- our vendor is to blame for this abomination, partly because they believe that flat text files are a cromulent way to store relationally-modeled data, and partly because they edit those flat text files by hand on a regular basis.)

  • (disco) in reply to unwesen
    unwesen:
    Depending on your query, you then need to lock multiple tables or sections, because you have that JOIN. The real performance killer is the lock; the JOIN is just the method to deliver it.

    Maybe you could get a better DB that doesn't LOCK ALL THE THINGS?

  • (disco) in reply to tarunik
    tarunik:
    INNER JOIN ON INSTR(list_of_children, child_pk) > 0

    i suspect that the perpetrator of that will manage to do something equally ugly no matter what tech is involved.

    Also WAT

  • (disco) in reply to RaceProUK

    So... then you don't know what 3NF does? Otherwise I cannot explain WTF you're talking about.

  • (disco) in reply to dkf

    True, the really expensive things are write operations. But that's not the context of this little debate we're having. The debate is why JOIN makes locking worse.

  • (disco) in reply to unwesen
    unwesen:
    So... then you don't know what 3NF does?
    You mean I don't know all data in 3NF relies on the primary key, the whole primary key, and nothing but the primary key? No, clearly I don't. In the same way I don't know my current avatar is a pink anthropomorphic hedgehog in a wedding dress.

    If you don't know how to sort out table/page/row locking, and make sure that you only lock what needs to be locked, then either a) learn how locking works, or b) find an RDBMS that doesn't suck ass.

    <!-- Emoji'd by MobileEmoji 0.2.0-->
  • (disco) in reply to unwesen
    unwesen:
    The debate is why JOIN makes locking worse.
    Two words: `WITH (NOLOCK)`. Learn it. Use it.

    Oh, and an INB4: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED. There. No read locks taken for the query/session.

    <!-- Emoji'd by MobileEmoji 0.2.0-->
  • (disco) in reply to RaceProUK

    I admire your unwavering faith.

  • (disco) in reply to RaceProUK

    Oh sure, instead of designing your database schema well for the performance criteria, prefer returning stale data. Yeah, good one, mate. You've disqualified yourself from this conversation.

  • (disco) in reply to unwesen
    RaceProUK:
    READ UNCOMMITTED
    unwesen:
    prefer returning stale data
    OK, you clearly don't understand databases at all.

    Go learn how locking and transaction isolation works, then come back and try your bullshit; maybe you'll actually have a valid point. Until then, stay away from databases; it's clear all you'll do to them is fuck them up.

    <!-- Emoji'd by MobileEmoji 0.2.0-->
  • (disco) in reply to RaceProUK
    RaceProUK:
    find an RDBMS that doesn't suck ass.

    Hint to @unwesen : it's one of the things Oracle gets right.

    (PostgreSQL gets it right too, if you are in an Oracle-free zone)

    unwesen:
    prefer returning stale data.
    Besides, `READ UNCOMMITTED` exposes you to *dirty reads*, not stale data. (Oracle and PostgreSQL *don't even support it*.)
  • (disco) in reply to unwesen
    tarunik:
    Hint to @unwesen : it's one of the things Oracle gets right.

    (PostgreSQL gets it right too, if you are in an Oracle-free zone)

    He said this earlier:

    unwesen:
    This is from a guy working for MySQL, he really knows this stuff.

    So I think we're working with different expectations about databases than @unwesen is.

  • (disco) in reply to boomzilla

    With the crap he's spewing, I'm not even sure he is talking about databases…

    <!-- Emoji'd by MobileEmoji 0.2.0-->
  • (disco)

    Wow wow! Isn't there anything more boring than DB flamewars? Yeah, DB locks and transactions flamewars!

  • (disco) in reply to Eldelshell
    Eldelshell:
    Wow wow! Isn't there anything more boring than DB flamewars?

    We could start talking about video games?

  • (disco) in reply to boomzilla
    boomzilla:
    So I think we're working with different expectations about databases than @unwesen is.

    We're talking about ACID and databases, and he's simply on acid? Err, MySQL?

  • (disco) in reply to dkf

    Everyone by now is aware that ORM are in your future.

  • (disco) in reply to Nagesh
    Nagesh:
    ORM

    Ordinary Restful Moments? Indeed…

  • (disco) in reply to dkf
    dkf:
    Ordinary <abbr title="Not ReSTful or RESTful">Restful</abbr> Moments? Indeed…

    Ha ha! Ordinary Rectum Movement more like it, for people not eating enough radishes in diet, those are also difficult to obtain.

  • (disco) in reply to boomzilla

    So: what colour is that dress?

  • (disco) in reply to Watson

    Discopink

    <!-- Emoji'd by MobileEmoji 0.2.0-->

Leave a comment on “Consultant Designed Success”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article