实体框架性能问题
我在实体框架的性能方面遇到问题。
这是场景。
我有一个名为“段”的实体。其中每一个都存储在数据库中自己的表中。
“段”有一个名为“IsHPMSSegment”的自定义属性，它是一个计算字段。它是通过调用数据库中的存储过程来计算的，该存储过程获取“段”的“ID”并将其中的一些值与另一个表中的值进行比较。
我们需要运行的查询之一如下所示：获取所有属于 HPMS 段的段。
由于“Segment”的“ISHPMSSegment”值是自定义属性，因此当首次选择段时，我无法直接从数据库检索它的值。相反，当在结果集中创建每个“段”时，实体框架再次查询数据库以获取“IsHPMSSegment”的值。因此，每次填充“段”时，它都必须再次查询数据库以获取返回的每个段。
示例：如果我获取 ID 大于 5 的所有“Segment”，并且结果集为 1000 个 Segments，则 DB 总共被命中 1001 次。一次用于获取 1000 条记录的初始选择查询，然后再进行 1000 次以填充每个“段”的“IsHPMSSegment”值。
我能想到的唯一解决方法是在包含此额外计算属性的数据库（“vSegments”）中创建一个视图，然后将我的 EF 对象链接到该视图，而不是“Segment”表。这样，该属性将在第一个查询中填充。
然后，我对其余功能（插入、更新、删除）有两种选择： 1）将实体的插入、更新和删除函数连接到存储过程 2) 使视图可更新
所有这些似乎只是为了解决这个性能问题而需要做很多额外的工作，我想知道使用 EF 有什么好处？
对于我上面所说的“视图+存储过程”的想法（仍然使用EF）是否有更好的解决方案？
如果没有，EF 为我提供什么好处？如果我从头开始创建自己的 DAL，我仍然需要创建存储过程和/或视图。通过使用 EF 并必须围绕其限制进行编程，我真正节省了多少精力？
最重要的是，EF 似乎并没有以令人满意的方式处理一次更新多个记录。它会为您正在更新的每条记录发送一个更新语句调用，即使您更新的记录完全相同。这似乎也是一个缺点（除非有一些我不知道的解决方法）。
I'm having a problem with performance with the entity framework.
Here's the scenario.
I have an entity called "Segment". Each of these are stored in their own table in the DB.
"Segments" have a custom property called "IsHPMSSegment" which is a calculated field. It is calculated by calling a stored procedure in the DB that takes the "ID" of the "Segment" and compares some of it's value against values in another table.
One of the queries we need to run is stated as follows: Get me all Segments that are HPMS Segments.
Since the "ISHPMSSegment" value of "Segment" is a custom property, I cannot retrieve it's value directly from the DB when the segments are first selected. Instead, as each "Segment" is being created in the result set, entity framework queries the db again to get the value for "IsHPMSSegment". So everytime a "Segment" is being filled, it has to query the DB once again for each Segment returned.
Example: If I get all "Segments" with an ID greater than 5, and the resultset is 1000 segments, then the DB is hit for a total of 1001 times. Once for the initial select query that gets the 1000 records, and then another 1000 times to fill the "IsHPMSSegment" value of each of the "Segments".
The only workaround I can think of it to create a view in the DB ("vSegments") that contains this extra calculated property, and then link my EF object to this view, instead of to the "Segment" table. That way this property would be filled in the first query.
I then have two choices for the remaining functionality (insert, update, delete):
1) wire up my insert, update, and delete functions for the entity to stored procedures
2) make the view updatable
All of this seems like a lot of extra work just to address this performance issue, and I'm left wondering what benefit there is to using EF at all?
Is there a better solution to the "view + stored procedures" idea I stated above (still using EF)?
If not, what benefit does EF provide me? If I was creating my own DAL from scratch, I would still have to create stored procedures and/or views. How much effort am I really saving by using EF and having to program around it's limitations?
On top of all this, EF doesn't seem to handle updating multiple records at once in a satisfactory way. It sends a single update statement call for each record you are updating, even if you are updating them all exactly the same. This also seems to be a detractor (unless there is some workaround for this that I am unaware of).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
这完全是主观的。在我的选择中，各层之间的职责分离会变得混乱并给你带来问题。
我的建议是删除存储过程并将逻辑移至业务层。 “细分”的创建应该从业务层开始，并针对它完成所有适当的逻辑。然后可以将最终状态推送到数据访问层以实现持久性。
This is entirely subjective. In my option the separation of duties between your layers is getting mixed up and causing you problems.
My suggestion would be to remove your stored procedure and move the logic into you business layer. Creation of your 'segments' should start in your business layer and have all the appropriate logic done against it. The final state can then be pushed into your data access layer for persistence.