Relationally Divided over EAV

Question

Relationally Divided over EAV

Tony Davis

SSCarpal Tunnel

Points: 4405
More actions
December 9, 2013 at 12:00 am

#281002

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

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

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

donald.parish SSC-Addicted Points: 423 More actions · Answer 1

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 More actions · Answer 2

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: 1004343 More actions · Answer 3

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.

Change is inevitable... Change for the better is not.

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

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: 39757 More actions · Answer 5

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 More actions · Answer 6

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 More actions · Answer 7

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: 1004343 More actions · Answer 8

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.

Change is inevitable... Change for the better is not.

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

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 9

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.

Change is inevitable... Change for the better is not.

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

Stephen Hirsch SSCommitted Points: 1822 More actions · Answer 10

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: 124210 More actions · Answer 11

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: 76000 More actions · Answer 12

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.

LinkedIn Profile

Gary Varga SSC Guru Points: 82166 More actions · Answer 13

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: 1004343 More actions · Answer 14

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.

Change is inevitable... Change for the better is not.

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