StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Regardless of MS SQL Server versus any other brand of database, the worst performance issue with EAV is that people try to do monster queries to reconstruct an entity on a single row. <em>This requires a separate join per attribute</em>.</p> <pre><code>SELECT e.id, a1.attr_value as "cost", a2.attr_value as "color", a3.attr_value as "size", . . . FROM entity e LEFT OUTER JOIN attrib a1 ON (e.entity_id = a1.entity_id AND a1.attr_name = 'cost') LEFT OUTER JOIN attrib a2 ON (e.entity_id = a2.entity_id AND a2.attr_name = 'color') LEFT OUTER JOIN attrib a2 ON (e.entity_id = a3.entity_id AND a3.attr_name = 'size') . . . additional joins for each attribute . . . </code></pre> <p>No matter what database brand you use, more joins in a query means geometrically increasing performance cost. Inevitably, you need enough attributes to exceed the architectural capacity of any SQL engine.</p> <p>The solution is to fetch the attributes in rows instead of columns, and write a class in application code to loop over these rows, assigning the values into object properties one by one. </p> <pre><code>SELECT e.id, a.attr_name, a.attr_value FROM entity e JOIN attrib a USING (entity_id) ORDER BY e.id; </code></pre> <p>This SQL query is so much simpler and more efficient, that it makes up for the extra application code.</p> <p>What I would look for in an EAV framework is some boilerplate code that retrieves a multi-row result set like this, and maps the attributes into object properties, and then returns the collection of populated objects.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload