Don't alter any settings related to timeouts.Įxecute lengthy query. Net Framework 4.0 app that uses Connector/Net 6.3.4 and Entity Framework to access the data. The only way I can find to change this behavior is to change context.CommandTimeout in my application code.Ĭreate MySql DB that has a query that takes well over 30 seconds.Ĭreate. No matter what you specify, the timeout stays at 30 seconds. In fact, Default Command Timeout in the connection string has no effect. And this is backed up in the documentation: ".the default command timeout can be changed using the connection string option Default Command Timeout." ( ) Per available documentation, if your Entity Framework 4.0's ObjectContext object's CommandTimeout is null, it's supposed to use the timeout of the "underlying provider". ("Many hours" is an estimation, I never finished this test, I stopped at 50.000 entities after 20 minutes.) This non-linear behaviour is not so significant in all other tests.Default Command Timeout is supposed to be a value you can specify in a MySql connection string. The behaviour in the first test above is that the performance is very non-linear and decreases extremely over time. commitCount = 1000, recreateContext = true: 191 sec.commitCount = 100, recreateContext = true: 164 sec.commitCount = 10, recreateContext = true: 241 sec.commitCount = 1, recreateContext = true: more than 10 minutes.commitCount = 1000000, recreateContext = false: out of memory exception.commitCount = 100000, recreateContext = false: 199 sec.commitCount = 10000, recreateContext = false: 202 sec.commitCount = 1000, recreateContext = false: 242 sec.commitCount = 100, recreateContext = false: more than 20 minutes.commitCount = 1, recreateContext = false: many hours (That's your current procedure).Here are a few measurements for my 560000 entities: So, it is helpful to clear it after some time. It is the growing size of attached entities in the context what slows down the insertion step by step. This clears the context from all entites, SaveChanges doesn't do that, the entities are still attached to the context in state Unchanged. It also improves the performance to dispose the context after SaveChanges and create a new one. With this code it works in less than 3 minutes.įor the performance it is important to call SaveChanges() after "many" records ("many" around 100 or 1000). ![]() I have a test program which inserts 560.000 entities (9 scalar properties, no navigation properties) into the DB. Private MyDbContext AddToContext(MyDbContext context,Įntity entity, int count, int commitCount, bool recreateContext) Call SaveChanges() after for example 100 records and dispose the context and create a new one.įor bulk inserts I am working and experimenting with a pattern like this: using (TransactionScope scope = new TransactionScope())Ĭ = false įoreach (var entityToInsert in someCollectionOfEntitiesToInsert)Ĭontext = AddToContext(context, entityToInsert, count, 100, true). ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |