DD + EF Performance terrible?

Question

User-2013179010 posted

We've built a site from the ground up with dynamic data using Entity Framework. Performance is unacceptably slow loading almost any page - confusingly, insert pages and edit pages seem to be slower than list pages.

 We've deliberately ignored optimization issues until this point figuring that things wouldn't be THAT bad and we could go and figure out speed issues later, but I'm reluctant to even demo the code with these kind of page load speeds (6,7secs +). I'm about to go and run some tracing but just debugging and doing sql server profiling, it subjectively seems like there isn't necessarily a single choke point for the slow down.

I've read of a few ways to improve EF performance  but I'm not sure how I'd go about implementing them in dynamic data.

Setting the objectcontext's ObjectTrackingEnabled property false apparently works in linq to sql to cut tracking overhead on objects you don't want to change. Is there an entity equiv and how would I implement it on entitydatasources? Something like that seems like a great idea for all databound filters and field templates. However they are bound using a sealed PopulateListControl method so I can't get at it (and would rather not implement the whole thing myself for that minor change). I would also like to get at the context.Log property to see what's going on in the app, but again Dynamic Data doesn't really expose anything like that. So:

1. Does anyone know of any collection of optimization tips I can read up on for dynamic data, or entity framework?
2. Is this DD / EF's issue or can people make quite large apps using this technology with no performance issue (in which case we must be doing something weird)
3. It'd be great if the EntityDataSource exposed some of these properties so that you could optimize its queries from the template pages. Just throwing that out there.

Edit: Traces reveal that the slow down is in all the binding of foreign key template fields and whatnot. Is there any turnkey caching solutions I can implement or other properties that can be enabled to make simple readonly population of dropdownlists and the like faster? (Edit #2 sql trace indicates most queries themselves run in negligable time, but the connection for each query stays open for 0.3 - 2 secs, as evident in the Audit Logout Duration field. I have no idea why but I guess it might be that that's the time EF takes to translate the resultset into entities, afterwich it d/c from the db? That seems big for such simple cases as binding a dropdownlist so I assume that EF is doing extra unnecessary work somewhere)

 

Also, perplexingly, it appears that the app is selecting the entire table every time I load the Insert page for an entity (first query that gets executed on the db). I have no idea why this would be necessary but it's a slow operation so I need a way of stopping or optimizing whatever it's trying to do there.

 

Answer 1

User-1005219520 posted

To get you started look at Tweaking the Filter Repeater - this is a know problem. That should solve 90% of your problems.

>>each query stays open for 0.3 - 2 secs,
I wouldn't worry about that until you fix the FR problem. Follow my blog and then do a perf re-evaluation.

 

Answer 2

User-2013179010 posted

Thanks Rick that's a useful tip there I'll probably wind up implementing at some stage. I do not however have long filter lists on my pages - in cases where I have lists of unmanageable size I've been utilizing autocomplete filters. I'm also kind of ok with waiting for a little while to get a list page up. What's bugging me is how long my Insert (especially) and Edit pages take to load given that they are simple forms with around 1-3 drop downlists bound to relatively small entitysets.

I'm starting to assume that the slow down is coming almost entirely in the step after EF makes a query to the db, before PreRender completes, and I think that must be the projection step. So I desperately need to figure out how to streamline that step or I'll just have to hack in some databinding shortcut for my drop down field template and filter controls to bypass the slowdown.

The big select * from table query at the start of an Insert page load is still bizarre too.

Answer 3

User-1005219520 posted

Any chance you can repro this behavior with a moderate sized DB like AdventureWorks (AW)?  I know that AW has perf problems, but they are associated with the FilterRepeater when there are thousands of FK (thus my blog).  I'll also try to repro.

One thing I like to do is figure out what in EF is causing the problem. It's usually best to reproduce the problem in a console application, then post the question to the EF forum. Dynamic Data is a framework that consume data models (such as DLINK and EF).  Another good source before posting is the EF FAQ.

>>The big select * from table query at the start of an Insert page load is still bizarre too.
That does sound unreasonable.

 

Answer 4

User660823006 posted

Did you modify the insert page? It should only be loading the single record that you are editing and any foreign key tables (which it would get the entire table for).

Answer 5

User-2013179010 posted

I've made modifications but not (i think) substantial ones. I decided the quickest way to test this was to attach the northwind db from the Dynamic Data Futures sample and profile that. Here's the first query run when you load up the Products/Insert page

SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued], [t2].[test], [t2].[CategoryID] AS [CategoryID2], [t2].[CategoryName], [t2].[Description], [t2].[Picture], [t4].[test] AS [test2], [t4].[SupplierID] AS [SupplierID2], [t4].[CompanyName], [t4].[ContactName], [t4].[ContactTitle], [t4].[Address], [t4].[City], [t4].[Region], [t4].[PostalCode], [t4].[Country], [t4].[Phone], [t4].[Fax], [t4].[HomePage]
FROM [dbo].[Products] AS [t0]
LEFT OUTER JOIN (
    SELECT 1 AS [test], [t1].[CategoryID], [t1].[CategoryName], [t1].[Description], [t1].[Picture]
    FROM [dbo].[Categories] AS [t1]
    ) AS [t2] ON [t2].[CategoryID] = [t0].[CategoryID]
LEFT OUTER JOIN (
    SELECT 1 AS [test], [t3].[SupplierID], [t3].[CompanyName], [t3].[ContactName], [t3].[ContactTitle], [t3].[Address], [t3].[City], [t3].[Region], [t3].[PostalCode], [t3].[Country], [t3].[Phone], [t3].[Fax], [t3].[HomePage]
    FROM [dbo].[Suppliers] AS [t3]
    ) AS [t4] ON [t4].[SupplierID] = [t0].[SupplierID]

That's followed by the expected queries to populate dropdowns. Note it's the same query as that run when you load the edit page, only the edit page query has a WHERE id = ... Does an insert page really need to be selecting the whole table? DDF uses LinqDataSources so it's not just an EF thing I guess. I can imagine that getting slow for big tables.

 As for the slow list table selects I mentioned above, that turned out to be a bit of a red herring (latency. *headsmack*). The real times I get loading foreignkey tables is something in the order of 60-80ms (with the odd outlier of like 200.. inconsistently.. weird), which still isn't as good as the ~15 I see from the DD futures sample using l2s which is supposed to be slower. Does having a lot of entities in your datamodel slow things down? Is there a way of disabling tracking for selective entities? Should I just be avoiding DD's PopulateListControl entirely and binding all my databindable fieldtemplates myself with ESQL?

Answer 6

User-1005219520 posted

 I'm guessing the query is not from DD but from LINQ - can you write a console app that inserts into NW - profile and compare? If you can repro with a console app you can post the question on the EF forum. If the console app version is not using similar SQL we can investigate.

The EF guys made the following suggestion:

For general application development, use LINQ to Entities and avoid Entity SQL.
 
Very few developers are going to learn Entity SQL, making code using it will be difficult to build and maintain.  And embedding queries as literal strings in application code is never better than a necessary evil.

Answer 7

User-1005219520 posted

One of the EF guys pointed out

If you have a SQL view that has 90 columns from various tables and write a query against it that returns 10 rows, you will get a query plan that doesn’t materialize all 90 columns and then throw 80 of them away.

Answer 8

User-2013179010 posted

Thanks for the replies Rick, I'll try that console idea and see what happens. In the meantime I've brought performance back up to an acceptable speed through entirely unrelated measures, and am finding every time I've really drilled down on something that seems EF / DD related so far, it's turned out to be something else so it seems this whole topic might be pointless.

The query there from insert does strike me as potentially problematic but as far as far as our app is concerned it's not actually causing any appreciable slowdown.

Answer 9

User-1005219520 posted

Great - if you figure out any good perf tips please post them here.