Relationally Divided over EAV

  • donald.parish

    Old Hand

    Points: 381

    Datomic (http://www.datomic.com)[/url] is an functional EAVT database that adds a Datalog logic query language, and separates query from storage, and well as allowing the ability to work with the database at any point in time.

  • Stephen Hirsch

    SSCommitted

    Points: 1822

    Here is my similarly themed article, albeit about a different maligned technique:

    http://www.sqlservercentral.com/articles/Advanced+Querying/2596/

    Bottom line, one always needs to know what to optimize for. More times than many people realize, raw speed of execution is not at the top of the list.

  • Jeff Moden

    SSC Guru

    Points: 994951

    Outstanding article, Tony. Well done. All too often, people will avoid the use of a tool highly appropriate for a given task just because someone says it's a "best practice" to avoid it. Hat's off and a deep bow to SQL Server MVP Peter Larsson for creating and delivering both a solution and a presentation that refutes such a misconception.

    As I say in my signature line as a play on words...

    "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Tony Davis

    SSCarpal Tunnel

    Points: 4340

    Comments posted to this topic are about the item Relationally Divided over EAV

  • SwePeso

    SSC-Dedicated

    Points: 39693

    Thank you Jeff!

    The client I was working with have 35,000 employees of which about 15,000 are using the database at any given time. And the system is, by nature, a document search store. Their current SLA includes a response time at 20 seconds. I gave them a response time of about 200 ms.

    And since it is related to medical journals nothing ever (or rarely) changes. The data is stored once and then queried every day for 10 years at an average of 30,000 times per day. It is a R/W ratio of 1:100,000,000, so it's heavily optimized for reading since writing occurs just once and read occurs 100,000,000 times during the lifespan of data.

    You can use other schemas if inserts and updates are more important than reading the data.


    N 56°04'39.16"
    E 12°55'05.25"

  • SwePeso

    SSC-Dedicated

    Points: 39693

    Here is a link to download the schema of my hybrid solution.

    It can also be found on my web page http://www.sqltopia.com


    N 56°04'39.16"
    E 12°55'05.25"

  • Robert.Sterbal

    SSCrazy

    Points: 2855

    The words "Best Practice" are an industry wide problem. We need to acknowledge them as recommended processes, which deviations from should be researched, not prohibited.

  • Gary Varga

    SSC Guru

    Points: 82166

    Robert.Sterbal (12/9/2013)


    The words "Best Practice" are an industry wide problem. We need to acknowledge them as recommended processes, which deviations from should be researched, not prohibited.

    Certainly. Never say never. Rather often a best practice to never employ a certain technique means use with extreme caution.

    I worked on a project that used an EVA design and some of us said that the performance was never going to be good enough. Perhaps correctly, the architect pushed ahead with the design but the biggest problem was that not enough consideration of effort was given to overcome the predicted performance issue.

    As usual, it depends.

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

  • Jeff Moden

    SSC Guru

    Points: 994951

    SwePeso (12/9/2013)


    Here is a link to download the schema of my hybrid solution.

    It can also be found on my web page http://www.sqltopia.com[/quote%5D

    I can't wait to read that. Thanks, ol' friend.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 994951

    Robert.Sterbal (12/9/2013)


    The words "Best Practice" are an industry wide problem. We need to acknowledge them as recommended processes, which deviations from should be researched, not prohibited.

    +1 to that. I'll also state that there are certain "Best Practices" that were born just because someone decided to call it that and they've actually not done any testing to support the "Its a best practice" claim. To wit, there are some "Best Practice" recommendations that I've run across in the past that are actually worst practices in my book the worst of which is "It's ok to use a While loop if you can't figure out another way to do it". They never identify what a While loop is actually appropriate for nor demonstrate methods to easily avoid While loops and so people don't take the time to learn the any way to avoid the While loop because it's supposedly ok to use if you can't think of a way. 🙂

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Stephen Hirsch

    SSCommitted

    Points: 1822

    If we called "Best Practices" by their true name (The One Practice To Rule Them All) people would be more skeptical...:-P

  • Matt Miller (4)

    SSC Guru

    Points: 124185

    Jeff Moden (12/10/2013)


    Robert.Sterbal (12/9/2013)


    The words "Best Practice" are an industry wide problem. We need to acknowledge them as recommended processes, which deviations from should be researched, not prohibited.

    +1 to that. I'll also state that there are certain "Best Practices" that were born just because someone decided to call it that and they've actually not done any testing to support the "Its a best practice" claim. To wit, there are some "Best Practice" recommendations that I've run across in the past that are actually worst practices in my book the worst of which is "It's ok to use a While loop if you can't figure out another way to do it". They never identify what a While loop is actually appropriate for nor demonstrate methods to easily avoid While loops and so people don't take the time to learn the any way to avoid the While loop because it's supposedly ok to use if you can't think of a way. 🙂

    Agreed. Whether adopted from some external source or developed internally, BP's should be documented to show fit within the org, including evidence that they fit the need and represent sane approaches.

    Our Best Practices are captured on an internal Wiki; each best practice gets its own page, with context as to when to when and when to NOT use, evidenced we might have developed (and links to anything external); where it's been applied; and always, some form of open discussion area should there be a challenge to the entry.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • David.Poole

    SSC Guru

    Points: 75197

    Things get badged up as best practice thus sullying the term best practice in the same way that certain practices get badged as agile while actually being bodge it and wing it thus sullying the term agile.

    The context in which something is best practice is important.

    For example, "for integers use the smallest integer data type possible" is open to abuse.

    "Use the smallest data type that covers the possible use cases" is better, though not a panacea.

    To my mind the term "best practice" is intended to be "preferred common usage" to help newbies from straying into dangerous territory without appropriate skills/awareness. Stage one is learning the rules. Stage two is learning the caveats to those rules. Stage three is discovering new rules/caveats.

  • Gary Varga

    SSC Guru

    Points: 82166

    Just as with Design Patterns (see Gamma et al), the context of use is required together with when it is both appropriate and inappropriate to apply. Examples help too. The worst "Best Practices" are those that frame advice in an authoritarian voice without and justification nor any caveats.

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

  • Jeff Moden

    SSC Guru

    Points: 994951

    David.Poole (12/11/2013)


    To my mind the term "best practice" is intended to be "preferred common usage" to help newbies from straying into dangerous territory without appropriate skills/awareness. Stage one is learning the rules. Stage two is learning the caveats to those rules. Stage three is discovering new rules/caveats.

    I like Stage Four the best... learning that "It Depends". 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply