Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Querying EAV data Expand / Collapse
Author
Message
Posted Thursday, August 30, 2012 2:36 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 6:38 PM
Points: 1,371, Visits: 1,560
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

Post #1352053
Posted Thursday, August 30, 2012 6:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:23 AM
Points: 12,915, Visits: 32,074
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1352141
Posted Thursday, August 30, 2012 2:05 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:23 PM
Points: 6,253, Visits: 7,428
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
Post #1352484
Posted Thursday, August 30, 2012 7:59 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:03 AM
Points: 37,065, Visits: 31,628
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1352556
Posted Thursday, August 30, 2012 11:04 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 6:38 PM
Points: 1,371, Visits: 1,560
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

Post #1352589
Posted Thursday, August 30, 2012 11:07 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 6:38 PM
Points: 1,371, Visits: 1,560
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

Post #1352591
Posted Friday, August 31, 2012 7:25 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:20 PM
Points: 1,945, Visits: 2,999
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.

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1352797
Posted Friday, August 31, 2012 8:46 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 6:38 PM
Points: 1,371, Visits: 1,560
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

Post #1352868
Posted Friday, August 31, 2012 9:09 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:25 PM
Points: 3,136, Visits: 11,490
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.

Post #1352890
Posted Friday, August 31, 2012 9:28 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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."
Post #1352910
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse