WCF RIA Services query slow

I have a WCF RIA Services domain service backed by Entity Framework. The entity model is quite complex. Generally, performance is OK for most operations.

However my Silverlight client includes a master/details view. The master view is a list of entities. Clicking between the entities to select them fires the IQueryable GetEntity() method on the server, and this is taking on average 5 seconds - an unacceptable amount of time.

I use Fiddler to look at the call over the wire, and I can see that all my time is being spent on the server:

ServerGotRequest:   15:59:44.545
ServerBeginResponse:15:59:48.836
ServerDoneResponse: 15:59:48.836
ClientBeginResponse:15:59:48.836
ClientDoneResponse: 15:59:48.836
Overall Elapsed:    00:00:04.2940000

The data returned, as it appears on the wire, is also quite small. In this example, around 6kb.

OK - so my problem is server side. The query into EF is fairly simple, with one caveat: we have ~25 .Include statements to bring in related entities. The .Includes select entities up to 4 levels deeps (e.g. .Include("1.2.3.4")).

So my next thought is that that the DB is slow. No so - I run SQL Profiler and see the (admittedly dreadful) SQL gets executed in, on average, 0.15 seconds. The data returned is not that bad - 3 rows of around 275 columns.

So:

• My bottleneck is on the server
• I select multiple, related entities through a straight EF query
• The SQL generated is ugly, but fast enough

So why am I slow? How can I de开发者_StackOverflow中文版bug this?

If I set a breakpoint at the end of my IQueryable GetEntity() method, it seems that after exiting that method it takes up to 3 seconds before the actual query appears in SQL Profiler. WTF?

Note that I have pre-generated my Entity Framework views, and I have tried using a compiled query to rule out EF 'warm up' times. No difference.

I appreciate any help with this issue. Thanks in advance.

I understand the responses telling me to batch my data, not load the whole entity, etc; and in general I agree with that approach. However, in this scenario, we are selecting a single entity - because it comes from a normalised database, the involves joining across multiple tables.

In this exact example, the horrible query generated by EF still executes in under 0.2 seconds. If I cared to hand write the query, it would probably take 1/10 of that again. As it is, it's definitely fast enough. The size of the whole entity on the wire is <6Kb; which, again, I consider small enough.

So for me to be batching this single entity across multiple requests, simply doesn't work in this example. Clearly if I was using straight ADO.NET and web services/WCF, I wouldn't be having this issue. Anyway, on to the answer:

I already stated the compiling the EF query didn't help. However, it seems that this may be because RIA Services was applying the 'Where EntityID = ID' condition on top of my original query, and blowing my compiled query away.

If I simply do this in my domain service:

(from e in ctx.Entities
.Include("SubEntity")
.Include("SubEntity.SubEntity")
// and so on, X20...
where e.EntityID == id
select e).FirstOrDefault();

This line of code takes nearly the whole time to execute, yet the SQL is very fast. It's just slow to get to the SQL Server. So this implies to me that EF is taking a long time to generate the query.

To fix the issue, I made a precompiled query that selects my entity directly by ID, and materialises it rather than return IQueryable:

 private static Func<DataContext, Guid, Entity> getEntityByEntityID =
        CompiledQuery.Compile<DataContext, Guid, Entity>(
        (ctx, id) => (from e in ctx.Entities
                        .Include("SubEntity")
                        .Include("SubEntity.SubEntity")
                        // and so on, X20...
                      where e.EntityID == id
                      select e).FirstOrDefault());

Then I expose a new operation on my domain service to use the compiled query:

public Entity GetEntityByEntityID(Guid entityID)
    {
        return getEntityByEntityID(this.ObjectContext, entityID);
    }

Result: it is now a little bit slower the first time this is called. For subsequent calls, the operation is now averaging around .5 seconds for the whole service call.

For stuff like this I think you are usually better off not using Includes. Load your main entity in Silverlight with a single call and then load up the other included items as you need them.

Even if you end up needing all of them in the view you will probably get better performance by pulling them down in smaller chunks and updating your view as you get them. It will definitely make a better user experience than waiting on a query.

The way you explain it - it is serialization/deserialization of a graph issue. Also, we had weird issues like that when SQL when executed in Analyzer runs fast and from EF it was slow. Try rebuilding statistcis on tables

In WCF RIA Services - you have to remember that their is a client side model that gets loaded first BEFORE the call back is issued. This client side model is NOT EF, but simply a rendering of what EF looks like on the server side. The WCF RIA Services call load this model first, before issuing the call back with a REFERENCE to the entities in the underlying model.

I would encourage you to look at the Rx framework to leverage multiple async calls before returning to the UI client with "work done" mentally. Using Rx, you can call several async methods and wait for all to be completed before you return to the UI. Each call would load the client side model, in smaller bits, allowing the work to be done in parallel before returning. Remember, this is a SOA architecture, and it's easy (speaking to myself) to fall back into the design of blocking calls.

(Task Parrallel Library (TPL) is also another way, but is making it's way into SL5. I'm not that familar with TPL and the nuances between when to use RX and/or TPL.)

I've used this strategy to pull data down the hierarchtical tree of what data that I'm returning- first job is the upper level, then the lower level, etc. Remember that navigation properties on the client are nothing more than linq queries under the covers, where the primary key is used to filter on the foriegn key. The client side model (at this point it's not an EF model, but something close to it) also has referential integrity constraints, but these only apply when inserting records, not LOADING records.