none
Query Interceptors AND Expand Hang IIS

    Question

  • 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)

    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.

    Details:

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

    Sample Interceptor

    [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;
    }

    The query interceptors for Person, Student, Referral are all very similar, ie they traverse multiple same/similar tables to look for ScopePermissions as above.

    Sample Query

    This sample query is just that, a sample, intended to illustrate to third parties how to access the data using the provided web service. I realize a production query wouldn't have that many expands. (But also remember that to get the entire object in the OOP sense I need an Agent, Person, and Student row.)

    var
     referrals =
    (from r in service.Referrals
    .Expand("Organization/ParentOrganization" )
    .Expand("Educator/Person/Agent" )
    .Expand("Student/Person/Agent" )
    .Expand("Student" )
    .Expand("Grade" )
    .Expand("ProblemBehavior" )
    .Expand("Location" )
    .Expand("Motivation" )
    .Expand("AdminDecision" )
    .Expand("OthersInvolved" )
    where
    r.DateCreated >= coupledays &&
    r.DateDeleted == null
    select r);

    Any suggestions or tips would be greatly associated, for fixing my current implementation or in developing a new one, with the caveat that existing database logic can't be changed (though I can add to it) and that ultimately I need to expose a large portion of the database via a web service that limits data access to the data authorized for, for the purpose of data integration with multiple outside parties. These outside parties will be performing regular batch jobs to import our data into their database/data-warehouse.

    THANK YOU!!!

    • Edited by PreMagination Monday, March 29, 2010 9:18 PM Clarification of issue described based on responses thus far.
    Thursday, March 25, 2010 8:54 PM

All replies

  • Hi,

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

    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.

    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.

    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.

    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.

    Thanks,


    Vitek Karas [MSFT]
    Friday, March 26, 2010 10:00 AM
    Moderator
  • Also one way to debug the issue would be to start from the database - use Profiler to check if the database receives the query at all and if it does how long does it take to get to the database. Then check how long it takes for the database to process the query. If the database needs a lot of time to process the query use debugging tools to see why. Check the results returned by the database - if this is something huge - serialization to Xml may be another bottleneck.

    Pawel

    Saturday, March 27, 2010 1:03 AM