There is a good deal of information on the web explaining why the EV model does not scale and does not perform well under load.

No amount of indexing will fix the basic flaw in the EV design.

Some people are fooled by the relative decent performance for single row and related row set retrieval but it is very easy to find fairly simple queries that do not run well with this design. Once you get 250 million rows of EV values like an application we purchased has you can pretty much schedule vacation while you wait for results.

I can see the flexibility demands that a rapidly changing data model requires. But I will argue strongly against abstracting the data model away from the database layer.

All these conceptual thought games around changing data models, software changes and end user configurablity just pass the decisions in a changing application to a person that is abstracted from a relational view of the performance impact of the data model change they are contemplating.

Unmanaged incremental performance risk with potentially cheaper development blind to the risks of the modelling decisions they are making. If only I could force the decision makers to do the ongoing support for these applications.

I love how everytime I read about EAVs there's always some quote about performance blah, blah. EAVs have their place and they are good at what they do. Hmm, let's take an example, start using Extended Properties or querying the SysProperties or using the ::fn_listExtendedProperty functions. Oh my god! Microsoft used an EAV! Did they use it throughout the whole design of Sql Server, No! EAVs serve their purpose as long as you are not relying on this data for business logic, I think it's fine.

When you need to display different descriptions for Products based on culture, you're doing an EAV even though you've defined the columns very specifically to Products, and in most minds it's a 1-to-Many design, it's underlying concept is still EAV.

I tend to use EAV data where it's applicable. For example, logging Event data. I don't use that data for business logic, maybe some reports, but it's rarely used. It does, however, need to be flexible or else you end up creating 100's of tables to log different types of events. Just looks at SQL Notification Services (hmm, I wonder why they're no longer going to support it?). It's too difficult and a maintenance nightmare.

Please keep reading! There will come a point after storing certain attributes that they will either become their own tables or additional columns of a root table once you realize the value or having that as an additional column or you find a way to name the column so that it's meaning is universal to anyone using it (for example, changing SSN to NationalID so that it works in any country or State to Region and County to District). EAVs make great sandboxes for data that can eventually become part of the standard model.

I don't know, my EAV tables have always had 2 indexes and have always been fine. Not sure how others are implementing this. Obviously partitioning will help.

tymberwyld (3/24/2008)I love how everytime I read about EAVs there's always some quote about performance blah, blah. EAVs have their place and they are good at what they do. Hmm, let's take an example, start using Extended Properties or querying the SysProperties or using the ::fn_listExtendedProperty functions. Oh my god! Microsoft used an EAV! Did they use it throughout the whole design of Sql Server, No! EAVs serve their purpose as long as you are not relying on this data for business logic, I think it's fine.

When you need to display different descriptions for Products based on culture, you're doing an EAV even though you've defined the columns very specifically to Products, and in most minds it's a 1-to-Many design, it's underlying concept is still EAV.

I tend to use EAV data where it's applicable. For example, logging Event data. I don't use that data for business logic, maybe some reports, but it's rarely used. It does, however, need to be flexible or else you end up creating 100's of tables to log different types of events. Just looks at SQL Notification Services (hmm, I wonder why they're no longer going to support it?). It's too difficult and a maintenance nightmare.

Please keep reading! There will come a point after storing certain attributes that they will either become their own tables or additional columns of a root table once you realize the value or having that as an additional column or you find a way to name the column so that it's meaning is universal to anyone using it (for example, changing SSN to NationalID so that it works in any country or State to Region and County to District). EAVs make great sandboxes for data that can eventually become part of the standard model.

I don't know, my EAV tables have always had 2 indexes and have always been fine. Not sure how others are implementing this. Obviously partitioning will help.

I agree... event logging and audit logs are a great example of EAV's. I also use them with great success in cross-tab reporting... first step is frequently to put the data into some form of EAV as a derived table. If I really want things to fly, the EAV will be a temp table with the correct index. I've made several monthly reports that report 9 different aggregated attributes by hour of day by day of month for all days of the month. Millions of rows are the source of the data (IVR application, in this case) and they are quickly aggregated into an EAV and then cross-tabbed with ease. And, unlike the Pivot function, can return more than one measure (if you want to call it that.).

We've also very successfully used them in "Customer Added 'Fields'"... not something you should go nuts with, but effective when the customer uses them properly.

As has been said, they have their place and that's not "everywhere".

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

See section 11 Disccussion of drawackshttp://en.wikipedia.org/wiki/Entity-Attribute-Value_model

See Downsideshttp://www.answers.com/topic/entity-attribute-value-model?cat=technology

Article in favor of generic design with many mostly negative comments postedhttp://www.sswug.org/see/26210

Posted 4 table model of everything with a few mostly negative and not overly specific disagreementshttp://discuss.joelonsoftware.com/default.asp?design.4.331499.15

The basic EV design works great when you have only one set of attributes to values, it works pretty good when you have a few sets of attributes to value pairs by a limited number of groups. By add another layer or two of grouping and increase the volume then the model starts to break down as soon as your query complexity increases.

If you are just considering using the model for a simple parameter table then you will probably not have to deal with most of the issues but if you needs are more complex you can quickly get into trouble.