Querying EAV data
-
Friends,
I am using a third partry tool whose database is modeled using Entity-Attribute-Value model.
What according to you would be the best way to query data. Every time I increase one attribute in my select clause, I need to add a join conditions.
Looking forward for your response.
Thanks
Lokesh
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
can you post some more details? the DDL for the table, a couple of sample rows, and an example of what you have to change wehn you add a new value would help enourmously.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lokesh Vij (8/30/2012)
What according to you would be the best way to query data. Every time I increase one attribute in my select clause, I need to add a join conditions.Create a view that will pivot the data for you and hook to that. Otherwise, you're basically stuck working with EAV as you describe to handle additional values as join conditions or to pivot it.
If you know your attribute names you can simply build it once (with further adjustments as necessary) as a pivoted view. Indexing the results may be in your best interest, but you will have to decide about the weight of the pivot updating for every change vs. speed of access to the rest.
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
Evil Kraig F (8/30/2012)
Lokesh Vij (8/30/2012)
What according to you would be the best way to query data. Every time I increase one attribute in my select clause, I need to add a join conditions.Create a view that will pivot the data for you and hook to that. Otherwise, you're basically stuck working with EAV as you describe to handle additional values as join conditions or to pivot it.
If you know your attribute names you can simply build it once (with further adjustments as necessary) as a pivoted view. Indexing the results may be in your best interest, but you will have to decide about the weight of the pivot updating for every change vs. speed of access to the rest.
d
Materializing the view as an indexed view may be the best bet for such a thing. Using a pivot or a Cross Tab to create the view without persisting the information would mean that the whole view would probably need to materialize before you could use any of the pivoted columns in something like a join.
--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) -
Evil Kraig F (8/30/2012)
Lokesh Vij (8/30/2012)
What according to you would be the best way to query data. Every time I increase one attribute in my select clause, I need to add a join conditions.Create a view that will pivot the data for you and hook to that. Otherwise, you're basically stuck working with EAV as you describe to handle additional values as join conditions or to pivot it.
If you know your attribute names you can simply build it once (with further adjustments as necessary) as a pivoted view. Indexing the results may be in your best interest, but you will have to decide about the weight of the pivot updating for every change vs. speed of access to the rest.
Thanks Kraig. My first thought was the same --> create a view to hold pivot data. But was bit reluctant, because any changes in the database may trigger changes in the view 🙂
Any way thanks for supporting my thoughts. Much appreciated!
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Jeff Moden (8/30/2012)
Materializing the view as an indexed view may be the best bet for such a thing. Using a pivot or a Cross Tab to create the view without persisting the information would mean that the whole view would probably need to materialize before you could use any of the pivoted columns in something like a join.
Very rightly pointed out. I just had a word with my product vendor reagrding the frequency of change in design. Outcome was that design is frozen.
I will go ahead and materialize my pivot view. Thanks for your kind response.
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
CELKO (8/31/2012)
You are screwed; EAV is 10 orders of magnitude worse than even a bad non-normalized schema. Yes, you have to assembly each column with joins and all yiou can do is hide it.🙂
My company should have involved in POC before zeroing-in-on this crappy product.
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
I would be very careful with the materialized view.
There is a lot of potential for blocking or deadlocks, so you may find it just makes things worse.
-
This is basically the price you pay for the flexibility that EAV brings.
And developers love EAV because its seems like a dream during the Design and initial Development phases, especially compared to the apparent unreasonable strictures and inflexibility of SQL and Relational Data Design. Plus EAV intuitively maps better to the OAV concepts implicit in Object-Orientation.
Of course they don't understand the maintenance and administration issues that come along with it. Nor even the massive effort overhead of actually doing the development correctly, that doesn't become apparent until Version 2 (or late in the V1 development, if they've got good testing and acceptance procedures).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
The thing I think is stupid about the EAV model is that it actually poorly duplicates what is already built into SQL Server: sys.tables, sys.columns, sys.types, and the tables themselves.
The database engine already handles the metadata correctly, and you can add a column to a table with just a simple statement. The engine already deals with checking that data is valid for the type, referential integrity, indexes, and many other things that you have to program into an EAV model.
Given all this, why would you ever want to put yourself to the work of dealing with an EAV structure?
-
Michael Valentine Jones (8/31/2012)
...The engine already deals with checking that data is valid for the type, referential integrity, indexes, and many other things that you have to program into an EAV model.
Given all this, why would you ever want to put yourself to the work of dealing with an EAV structure?
(emphasis mine)
The italicized part is the key. They either don't realize that they have to do this beforehand, or they just decide not to do it completely (or perhaps at all). That's a big time-saver. For them, not us. At first, anyway.
Which gives them the flexibility they want: to be able to add and change Attributes and Relationships without regard to those far-off consequences because the annoying SQL database keeps trying to force them to be consistent, stable, and valid now. Instead of in the future where problems you don't want to think about belong.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
RBarryYoung (8/31/2012)
This is basically the price you pay for the flexibility that EAV brings.And developers love EAV because its seems like a dream during the Design and initial Development phases, especially compared to the apparent unreasonable strictures and inflexibility of SQL and Relational Data Design. Plus EAV intuitively maps better to the OAV concepts implicit in Object-Orientation.
Of course they don't understand the maintenance and administration issues that come along with it. Nor even the massive effort overhead of actually doing the development correctly, that doesn't become apparent until Version 2 (or late in the V1 development, if they've got good testing and acceptance procedures).
Very true Barry.
It is the pain one gets being pricked by the Rose stem and at the same time enjoying the fragrance of Rose 🙂
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
CELKO (8/31/2012)
You are screwed; EAV is 10 orders of magnitude worse than even a bad non-normalized schema. Yes, you have to assembly each column with joins and all yiou can do is hide it.I realize this post is a little old but... you don't need to "assembly" each column with joins on an EAV. A properly written CROSS TAB or even a PIVOT will work just fine.
--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)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply