1

I have a reasonably large edmx generated from a database and I have been working on performance recently to improve my application I have read a number of articles in a variety of places some here some not

this one on disabling auto detect of changes http://msdn.microsoft.com/en-us/data/jj556205.aspx

this one on improving performance on delete DbContext is very slow when adding and deleting

this one (which I think is pretty good) http://www.codeproject.com/Articles/38922/Performance-and-the-Entity-Framework

I am already using myentities.tablename.MergeOption = MergeOption.NoTracking, i am using compiledqueries, I am pregenerated my View using EdmGen, I have reduced the data I am fetching etc.. and, of course, I have gained performance in leaps and bounds so that a page that was loading in 54 seconds is now taking 16.1 seconds - however I have to get it to 3 seconds So I am still looking for the next improvement

so the research is all well and great and as a result I have upgraded to the latest EntityFramework, I have regenerated my .edmx from db etc... and tried a variety of things but I simply cannot find a myEntities.Configuration.AutoDetectChangesEnabled in order to set it to false. Now I must be missing a simple easy trick - how do I get my edmx to have this option.

I am in this environment.Net 4.0.3, visual studio 2010, latest version of EntityFramework, MVC 4.0... All I need is somebody to say "aha" you need to go and do this....

Currently if I delete 1000 records from one of my larger tables (134million rows) it takes nearly 10 minutes to savechanges. So from what I have read AutoDetectChangesEnabled is what I need to alter but it doesnt exist in my classes? where is it what must I do to get it?

Any help appreciated I am trying to solve this one quickly

Regards Julian

Community
  • 1
  • 1
julian guppy
  • 411
  • 1
  • 4
  • 21

2 Answers2

1

Right, I eventually found this item on stackoverflow Get DbContext for Entities that describes what is needed in order to change your database first edmx into a version that has the .Configuration.AutoDetectChangesEnabled this was great and I was able to progress. However, this did not get me the solution I was looking for as deletes being saved still took an inordinate amount of time.

So the moral is, yes apply all of the performance tricks pre generate your views, use AutoDetectChangesenabled=true, use compiled queries, smart connection strings create fake objects instead of fetching the data first, etc... you can probably in most cases get the performance that is acceptable but if you really need to do things quickly you will need to go to TSQL and do it by hand

Regards Julian

Community
  • 1
  • 1
julian guppy
  • 411
  • 1
  • 4
  • 21
  • as an additional comment, i improved the deletes by letting sql use the cascading delete on the foreign key, in older sqls when I did this it behaved poorly, however on sql 2008 R2 a cascading delete was the next part of the puzzle to do the deletes i needed – julian guppy Nov 01 '12 at 14:17
  • Following up on this, part of the solution was to move up to EF 5.0, and latest versions of everything... this meant a large amount of refactoring the code. However, the answer seems to be with EF if its slow you need to update to the latest... painful but the best solution – julian guppy Nov 15 '13 at 08:53
0

AutoDetectChanges sits in DbContext.Configuration.AutoDetectChangesEnabled. For deletion what you can also try is to get list of IDs you want to delete, create fake objects that have only these IDs set, attach these objects and than delete them.

However we have also recently had a similar problem and we are currently deleting with ADO.NET. (or there is a method on DbContext where you can push SQL). In general EF works great for our app, but in 2-3 places we need performance, as number of records is huge. Unfortunatelly we had to use ADO.NET in these places, it's just many time faster when you work with mass data.

Maxim Zabolotskikh
  • 3,091
  • 20
  • 21
  • I do not have a .Configuration in my class it doesnt exist, how do I create my edmx so that it does. And yes I am already just fetching the ids, creating a fake object, attaching to the entities, doing the delete..... its the save which is a killer. My question is how do i get the option .Configuration.AutoDetectChanges - because it doenst exist for me – julian guppy Oct 27 '12 at 20:35
  • Just some guesses: "Latest version of EF" - you mean 5.0, right? Did you do a clean install - i.e. if you had 4.2 CTP installed, you should have deinstall that prior to installing the new version. What code generation template are you using? – Maxim Zabolotskikh Oct 28 '12 at 09:14
  • No I was missing a basic bit of info in that I needed to alter my DB generated EDMX using the detail in this article http://stackoverflow.com/questions/11180250/get-dbcontext-for-entities thanks anyway – julian guppy Oct 28 '12 at 10:44