SQL Server上大型EAV /开放式架构系统的性能 [英] Performance of large EAV/open schema systems on SQL Server

问题描述

有没有人在SQL Server中实现了一个非常大的EAV或打开的模式样式数据库？我想知道这是否有性能问题，以及如何克服这些障碍。

解决方案

无论MS SQL服务器与任何其他品牌的数据库，EAV最糟糕的性能问题是人们尝试做怪物查询以重建单行上的实体。 。

<$ c> ，a2.attr_value为color，
a3.attr_value为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'）
。 。 。每个属性的附加连接。 。 。

无论您使用什么数据库品牌，查询中的更多联接都意味着几何级别的性能提升。不可避免地，您需要足够的属性来超过任何SQL引擎的架构容量。

解决方案是获取行而不是列的属性，并在应用程序代码循环遍历这些行，将值逐个分配到对象属性中。

  SELECT e.id，a.attr_name，a.attr_value 
 FROM entity e JOIN attrib a USING（entity_id） 
 ORDER BY e.id; 
  

这个SQL查询非常简单，更高效，它弥补了额外的应用程序代码。

我在EAV框架中寻找的是一些样板代码，用于检索像这样的多行结果集，并将属性映射到对象属性中，然后返回填充对象的集合。

Has anyone implemented a very large EAV or open schema style database in SQL Server? I'm wondering if there are performance issues with this and how you were able to overcome those obstacles.

解决方案

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. This requires a separate join per attribute.

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

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.

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.

SELECT e.id, a.attr_name, a.attr_value
FROM entity e JOIN attrib a USING (entity_id)
ORDER BY e.id;

This SQL query is so much simpler and more efficient, that it makes up for the extra application code.

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.

这篇关于SQL Server上大型EAV /开放式架构系统的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！