查询拦截器并展开挂起IIS [英] Query Interceptors AND Expand Hang IIS
问题描述
我有一个ADO.NET数据服务，它应该提供对稍微复杂的数据库的只读访问。
逻辑上，我的数据模型中有每个类型的表（TPT）继承，但EDM没有实现继承。 （派生类型的数据服务和导航属性的限制。在.NET 4中没有修复STILL！）我可以使用我试图针对Web服务运行的查询的副本直接查询我的EDM（使用单独的项目），结果在10秒内返回。禁用查询拦截器我能够对Web服务进行相同的查询，结果也会快速返回。我可以启用查询拦截器的部分，并且结果会缓慢返回，大约一分钟左右。或者，我可以启用所有查询拦截器，在我查询的主对象上扩展较少的属性，并在相似的时间段内返回结果。 （我增加了一些超时时间）
I have an ADO.NET Data Service that's supposed to provide read-only access to a somewhat complex database.
Logically I have table-per-type (TPT) inheritance in my data model but the EDM doesn't implement inheritance. (Limitation of Data Services and navigation properties on derived types. STILL not fixed in .NET 4!) I can query my EDM directly (using a separate project) using a copy of the query I'm trying to run against the web service, results are returned within 10 seconds. Disabling the query interceptors I'm able to make the same query against the web service, results are returned similarly quickly. I can enable some of the query interceptors and the results are returned slowly, up to a minute or so later. Alternatively, I can enable all the query interceptors, expand less of the properties on the main object I'm querying, and results are returned in a similar period of time. (I've increased some of the timeout periods)
直到这一点，Sql Profiler指示减速是数据库。 （这是不同日期的帖子）但是当我启用所有查询拦截器并展开所有属性时，我希望IIS工作进程将CPU挂起20分钟，甚至不会对数据库进行查询，即查询永远不会通过Web服务器。这对我来说意味着是的，我的实现可能很糟糕，但无论数据服务"层"如何。它有一个问题不应该。 WCF追踪没有透露任何有趣的未经训练的眼睛。
Up til this point Sql Profiler indicates the slow-down is the database. (That's a post for a different day) But when I enable all my query interceptors and expand all the properties I'd like to have the IIS worker process pegs the CPU for 20 minutes and a query is never even made against the database, ie the query never makes it past the web server. This implies to me that yes, my implementation probably sucks but regardless the Data Services "tier" is having an issue it shouldn't. WCF tracing didn't reveal anything interesting to my untrained eye.
详细信息：
- 数据模型：代理 - > ;人 - >学生
- 学生有一系列推荐
- 学生和推荐是私人的，针对网络服务的查询应该只返回"你"学生和转介。这意味着人员和代理也需要过滤。其他实体（代理 - >组织 - >学校）可以由经过身份验证的任何人访问。
- 现有的安全模型不适合对此类型的数据访问，查询拦截器很复杂，导致EF生成一些有趣的 sql查询。
- Data model: Agent->Person->Student
- Student has a collection of referrals
- Students and referrals are private, queries against the web service should only return "your" students and referrals. This means Person and Agent need to be filtered too. Other entities (Agent->Organization->School) can be accessed by anyone who has authenticated.
- The existing security model is poorly suited to perform this type of filtering for this type of data access, the query interceptors are complicated and cause EF to generate some entertaining sql queries.
示例拦截器
[QueryInterceptor("Agents"
)]
public
Expression<Func<Agent, Boolean>> OnQueryAgents()
{
//Agent is a Person(1), Educator(2), Student(3), or Other Person(13); allow if scope permissions exist
return
ag =>
(ag.AgentType.AgentTypeId == 1 || ag.AgentType.AgentTypeId == 2 || ag.AgentType.AgentTypeId == 3 || ag.AgentType.AgentTypeId == 13) &&
ag.Person.OrganizationPersons.Count<OrganizationPerson>(op =>
op.Organization.ScopePermissions.Any<ScopePermission>
(p => p.ApplicationRoleAccount.Account.UserName == HttpContext.Current.User.Identity.Name && p.ApplicationRoleAccount.Application.ApplicationId == 124) ||
op.Organization.HierarchyDescendents.Any<OrganizationsHierarchy>(oh => oh.AncestorOrganization.ScopePermissions.Any<ScopePermission>
(p => p.ApplicationRoleAccount.Account.UserName == HttpContext.Current.User.Identity.Name && p.ApplicationRoleAccount.Application.ApplicationId == 124))) > 0;
}
推荐答案
使用这样一个复杂的查询拦截器，大概是你正在扩展的大多数实体集，然后使用你发布的查询将导致一个巨大的（我的意思是真的很大）LINQ查询针对EF提供商执行。如果不能调试这个，我会猜测减速的原因是SQL EF生成的复杂。并且"悬挂"的原因是很可能是查询变得如此复杂以至于LINQ - > EF - > SQL转换只是无法处理这样的查询（其中一些算法对于查询的大小是非线性的，所以它可能会变得非常糟糕）。
Using such a complex query interceptor and presumably on most of the entity sets you're expanding and then use the query you posted will result in a huge (and I mean really huge) LINQ query to be executed against the EF provider. Without being able to debug this I would venture a guess that the reason for the slowdown is the complex SQL EF generates. And the reason for the "hang" is porbably that the query got so complicated that the LINQ -> EF -> SQL translation just can't handle such query (some of these algorithms are non-linear to the size of the query, so it can get pretty bad).
一些技术细节：每个展开都表示为针对EF运行的查询中的投影（.Select），因此上面的查询实际上使用了大量的投影。每次在查询中遇到实体集时，每个查询拦截器都是一个过滤器（.Where）。因此，除了这些预测之外，您还应用了大量过滤器。由于这些过滤器相当复杂，因此生成的SQL必须非常重要。
A bit of technical detail: Each expand is expressed as a projection (.Select) in the query ran against EF, so your query above uses lot of projections in fact. Each query interceptor is a filter (.Where) every time the entity set is encountered in the query. So on top of those projections you have lot of filters applied. Since these filters are rather complicated the SQL generated must be quite a thing.
对于解决方案，我首先要减少扩展数量。您的客户是否真的需要预先了解所有这些扩展实体？如果没有，你可以使用LoadProperty方法延迟加载它们（这将发出一个更简单的查询来获得你要求的东西）。
As to a solution, I would start by decreasing the number of expansions. Does your client really need to know all those expanded entities up front? If not, you can lazy load them using the LoadProperty method (which will issue a much simpler query to get just the thing you asked for).
最后我可能还会尝试简化查询拦截器可能是造成复杂性的主要原因。
Eventually I would probably also try to simplify the query interceptors as those are probably the main reason for the complexities.
我还建议您在EF论坛上提出这个问题，因为他们可能会对EF究竟是什么伤害最好以及如何解决这个问题。
I would also suggest that you ask this question on the EF forum as they might have some ideas on what exactly hurts EF the most and how to workaround that.
你能尝试（如果可能的话）在.NET 4上运行它。在LINQ中有一些不错的修复 - > EF - > SQL转换器最终生成更简单，更好看的SQL。
Could you try (if possible) to run this on .NET 4. There have been some nice fixes in the LINQ -> EF -> SQL translators which end up generating much simpler and nicer looking SQL.
谢谢，
这篇关于查询拦截器并展开挂起IIS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
