关联属性，噩梦性能（实体框架）
我有一个相当大的 EF4 模型，使用 POCO 代码生成。我有很多实例，我通过 ID 从任意表中选择单个实体。
然而，在某些表上，这需要 2 分钟或更长的时间，而在大多数表上，这需要不到一秒的时间。我现在不知道该去哪里寻找，因为我看不出任何理由。导致问题的总是相同的表，但我可以直接针对数据库查询它们而不会出现问题，因此问题一定来自实体框架领域的某个地方。
该行是相当无害的：
Dim newProd As New Product
Product.ShippingSize = Entities.ShippingSizes.Single(Function(ss) ss.Id = id)
- id 只是从 UI 传入的整数，我的实体上的 Id 是主键，它在数据库上索引
- Entities 是我的实体框架 datacontext 的新创建的实例
- 这不是第一个查询针对上下文执行，这是针对该 EntitySet 的第一个查询，尽管
- 我已经重新索引了所有表，这些表已经看到帖子表明损坏的索引可能导致访问缓慢，但这没有任何区别
- 与其他代码完全相同的行表格几乎立即运行，只是某些表格
- 这个特定的表格很小 - 它里面只有 4 个东西
对于从哪里开始有什么建议吗？
--edit - 我将问题中的代码过度简化到问题消失的地步！
I have a fairly large EF4 model, using POCO code gen. I've got lots of instances where I select a single entity from whichever table by its ID.
However on some tables, this takes 2 minutes or more, where on most tables it takes less than a second. I'm out of ideas as to where to look now, because I can't see any reason. It's always the same tables that cause problems, but I can query them directly against the database without problems, so it must be somewhere in Entity Framework territory that the problem is coming from.
The line is the quite innoccuous:
Dim newProd As New Product
Product.ShippingSize = Entities.ShippingSizes.Single(Function(ss) ss.Id = id)
- id is simply an integer passed in from the UI, Id on my entity is the primary key, which is indexed on the database
- Entities is a freshly created instance of my entity framework datacontext
- This is not the first query being executed against the Context, it is the first query against this EntitySet though
- I have re-indexed all tables having seen posts suggesting that a corrupt index could cause slow access, that hasn't made any difference
- The exact same line of code against other tables runs almost instantly, it's only certain tables
- This particular table is tiny - it only has 4 things in it
Any suggestions as to where to even start?
--edit - I'd oversimplified the code in the question to the point where the problem disappeared!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
从哪里开始？
这应该可以告诉您是数据库问题还是 EF 问题。
Where to start?
That should tell you whether you have a database problem or an EF problem.
看起来这是 POCO 模板的 Fixup 行为与延迟加载相结合的函数。
由于实体已通过 Single 加载，因此后续操作似乎发生在内存中，而不是针对数据库。 Fixup 方法默认进行 Contains() 调用，此时一切都会停止，同时检索数十万个项目，将其初始化为代理并在内存中进行评估。
我尝试将此 Contains() 更改为Where(Function(x) x.Id = id).Count > 0（逻辑上会做同样的事情，但尝试强制快速数据库操作而不是缓慢的内存操作）。查询仍然在内存中执行，而且速度同样慢。
我从 POCO 切换到标准 EntityGenerator，这个问题就消失了，没有进行其他更改。说出你对模式/实践的看法，但这是一个令人讨厌的问题 - 直到我从假货和小型测试数据库切换到全尺寸数据库时我才发现这一点。实体生成器暂时挽救了局面。
Seems like this is a function of the POCO template's Fixup behaviour in combination with lazy loading.
Because the entity has already been loaded via Single, subsequent operations seem to be happening in memory rather than against the database. The Fixup method by default makes Contains() calls, which is where everything grinds to a halt while 10s of thousands of items get retrieved, initialised as proxies, and evaluated in memory.
I tried changing this Contains() to a Where(Function(x) x.Id = id).Count > 0 (will do logically the same thing, but trying to force a quick DB operation instead of the slow in-memory one). The query was still performed in-memory, and just as slow.
I switched from POCO to the standard EntityGenerator, and this problem just disappeared with no other changes. Say what you will about patterns/practices, but this is a nasty problem to have - I didn't spot this until I switched from fakes and small test databases to a full size database. Entity Generator saves the day for now.