Post reply

Querying EAV data

  • August 30, 2012 at 2:36 am

    #266043

    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 questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • August 30, 2012 at 6:21 am

    #1531822

    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!

  • August 30, 2012 at 2:05 pm

    #1532120

    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[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • August 30, 2012 at 7:59 pm

    #1532179

    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)

  • August 30, 2012 at 11:04 pm

    #1532208

    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 questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • August 30, 2012 at 11:07 pm

    #1532210

    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 questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • August 31, 2012 at 8:46 am

    #1532437

    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 questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • August 31, 2012 at 9:09 am

    #1532457

    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.

  • August 31, 2012 at 9:28 am

    #1532475

    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]

  • August 31, 2012 at 9:58 am

    #1532502

    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?

  • August 31, 2012 at 10:22 am

    #1532519

    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]

  • August 31, 2012 at 11:18 am

    #1532559

    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 questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • October 21, 2012 at 5:24 pm

    #1551196

    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