SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Relationally Divided over EAV


Relationally Divided over EAV

Author
Message
Tony Davis
Tony Davis
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: Administrators
Points: 2115 Visits: 1171
Comments posted to this topic are about the item Relationally Divided over EAV
donald.parish
donald.parish
SSC Veteran
SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)

Group: General Forum Members
Points: 232 Visits: 44
Datomic (http://www.datomic.com) 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
Stephen Hirsch
Right there with Babe
Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)Right there with Babe (768 reputation)

Group: General Forum Members
Points: 768 Visits: 161
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
Jeff Moden
SSC Guru
SSC Guru (435K reputation)SSC Guru (435K reputation)SSC Guru (435K reputation)SSC Guru (435K reputation)SSC Guru (435K reputation)SSC Guru (435K reputation)SSC Guru (435K reputation)SSC Guru (435K reputation)

Group: General Forum Members
Points: 435822 Visits: 43509
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." :-D

--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

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
SwePeso
SwePeso
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18959 Visits: 3433
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
SwePeso
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18959 Visits: 3433
Here is a link to download the schema of my hybrid solution.

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


N 56°04'39.16"
E 12°55'05.25"
Robert.Sterbal
Robert.Sterbal
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1095 Visits: 2000
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
Gary Varga
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41810 Visits: 6562
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
Jeff Moden
SSC Guru
SSC Guru (435K reputation)SSC Guru (435K reputation)SSC Guru (435K reputation)SSC Guru (435K reputation)SSC Guru (435K reputation)SSC Guru (435K reputation)SSC Guru (435K reputation)SSC Guru (435K reputation)

Group: General Forum Members
Points: 435822 Visits: 43509
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 www.sqltopia.com


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

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (435K reputation)SSC Guru (435K reputation)SSC Guru (435K reputation)SSC Guru (435K reputation)SSC Guru (435K reputation)SSC Guru (435K reputation)SSC Guru (435K reputation)SSC Guru (435K reputation)

Group: General Forum Members
Points: 435822 Visits: 43509
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

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search