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


Querying EAV data


Querying EAV data

Author
Message
Lokesh Vij
Lokesh Vij
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2046 Visits: 1599
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
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter


Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28046 Visits: 39926
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!

Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8561 Visits: 7660
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 Farrell

Never 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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85178 Visits: 41077
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.
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
Lokesh Vij
Lokesh Vij
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2046 Visits: 1599
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
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter


Lokesh Vij
Lokesh Vij
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2046 Visits: 1599
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
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter


Lokesh Vij
Lokesh Vij
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2046 Visits: 1599
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
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter


Michael Valentine Jones
Michael Valentine Jones
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5692 Visits: 11771
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.
RBarryYoung
RBarryYoung
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14640 Visits: 9518
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).

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
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