Entity Framework data insertion Performance Tracking

At the beginning: Most of the reviewers in this article think that this test is not correct, but no one gives a clear response; I have deleted some purely abusive comments (I seem to have said that EF performance is low = insulted his family. My goal is to insert pieces of data into the database. The conclusion is that it is time-consuming to Add data to the context. If there are methods to bypass this process or suggestions for improvement, please advise, otherwise I do not think EF's low performance conclusion in this scenario is wrong!

In order not to "irritate" more people, the title was changed several times. At that time, I wrote this article not to prove anything. It was simply because I "found" a bottleneck of EF during the running of a project, and found that the solution failed, so I recorded it. Alas, I am so simple ...... The solution is at the end of the article.

In addition, the statement that the generated SQL statement will be submitted to the database at one time during SaveChanges () can also be eliminated. Some comments below hold this opinion, first, I have repeatedly stressed that the bottleneck is not in the database interaction stage. To avoid repeated meaningless comments, you may need to use Profiler to track the problem.

I have long heard that EF has a poor performance. I didn't expect it to be really bad, and it's not so bad, bloggers who do not know the truth are calm ). Those who have applied EF in the project should be careful when there is a picture and a truth!

1. NET4.0, EF4.4

1 public void ExecRealTimeRun (List <RealTimeStocks> realTimeData) 2 {3 using (var context = new StockDataEntities () 4 {5 context. database. executeSqlCommand ("delete from RealTimeStocks"); 6 7 var now1 = DateTime. now. timeOfDay; 8 Console. writeLine (string. format ("{0} starts to Add data to the context. Data volume: {1}", now1, realTimeData. count); 9 foreach (var data in realTimeData) 10 {11 context. realTimeStocks. add (data); 12} 13 14 var now2 = DateTime. now. timeOfDay; 15 Console. writeLine (string. format ("{0} data Added is complete, start the Insert operation, time consumed {1}", now2, now2-now1); 16 try17 {18 context. saveChanges (); 19} 20 catch (DbEntityValidationException dbEx) 21 {} 22 catch23 {} 24 25 var now3 = DateTime. now. timeOfDay; 26 Console. writeLine (string. format ("{0} Insert completed, time consumed {1}", now3, now3-now2); 27} 28}

Simple. There is redundant logic in the wood. Result:

Insert more than 7 k data, more than 1 minute. If the SaveChanges operation is wrapped in TransactionScope (purely for testing, to see if it is more time-consuming), The result remains unchanged.

2.. NET4.5, EF4.4

I heard that as long as you upgrade to. NET4.5, EF will improve the performance, because EF uses some class libraries of the. NET Framework. With the upgrade of these class libraries, EF also improves the performance. So, cognac. Result:

(This figure does not need to be pasted, because it is no different from the other)

3.. NET4.5, EF5.0

Microsoft told me that 5.0 has improved the performance by 67% (some people say it only improves the query performance, but here it focuses on ridicule). I think such a large company will talk about it in disorder? So I uninstalled 4.4 and installed 5.0 (it seems that NuGet will be based on your.. ). Restart the test. Result:

(This figure does not need to be pasted, because it is no different from the other)

4. Actually,The key is not in the database insertion stage, but in the Add stage of the Code layer. so, I don't think it is consistent with ADO. NET. NET has no context, and the inefficiency is the process of adding to context.(The blogger explicitly stated here that the following code is to compare the SaveChanges method andADO. NETExecution efficiency, not the efficiency of the entire insert phase).A friend commented that the method of adding is wrong and I don't know how to write it. Ask.

In any case, paste the Code:

1 public void ExecRealTimeRunByADO (List <RealTimeStocks> realTimeData) 2 {3 string insertText = @ "insert [dbo]. [RealTimeStocks] ([Hqgpdm], [Hqzrsp], [Hqjrkp], [Hqzjcj], [Hqcjsl], [Signature], 4 [Hqcjje], [Hqdqcjje], [Hqcjbs], [Hqzgcj], [Hqzdcj], [Hqsyl1], [Hqsyl2], [sequence], [sequence], [Hqhycc], [Hqsjw5], [Hqssl5], 5 [Hqsjw4], [Hqssl4], [Hqsjw3], [Hqssl3], [Hqsjw2], [Hqssl2], [Hqsjw1], [Hqssl1], [Hq Bjw1], [Hqbsl1], [Hqbjw2], [Hqbsl2], 6 [Hqbjw3], [Scheme], [Hqbjw4], [Hqbsl4], [Hqbjw5], [Scheme], [HQTime], [UpdateTime], [ExponentRiseDown], [RiseDownPercent], 7 [ExponentSwing], [TimeID], [RiseNum], [DownNum], [interval num], [DataSource], [IsDeleted], [AddTime], [SMGUID]) 8 values ('{0}', {1}, {2}, {3 }, {4}, null, {5}, null, null, {6}, {7}, {8}, null, {9}, {10}, null, {11 },{ 12 },{ 13 },{ 14 },{ 15 }, 9 {16}, {17}, {18}, null, {19}, null, {20}, {21}, {22}, {23 }, {24}, {25}, {26}, {27}, {28}, '{29}', '{30}', {31}, {32 }, {33}, {34}, 10 {35}, {36}, {37}, null, {38}, '{39}', '{40 }') "; 11 using (var context = new StockDataEntities () 12 {13 context. database. executeSqlCommand ("delete from RealTimeStocks"); 14 15 var now2 = DateTime. now. timeOfDay; 16 Console. writeLine (string. format ("{0} start the Insert operation", now2 ); 17 using (TransactionScope scope = new TransactionScope (TransactionScopeOption. requiresNew, new TimeSpan (0, 2, 0) 18 {19 try20 {21 foreach (var data in realTimeData) 22 {23 string cmd = string. format (insertText, data. hqgpdm, data. hqzrsp, data. hqjrkp, data. hqzjcj, data. hqcjsl, 24 data. hqcjje, data. hqzgcj, data. hqzdcj, data. hqsyl1, data. hqjgsd1, data. hqjgsd2, data. hqsjw5, data. hqssl5, 25 data. hqsjw 4, data. hqssl4, data. hqsjw3, data. hqssl3, data. hqsjw2, data. hqssl2, data. hqssl1, data. hqbsl1, data. hqbjw2, data. hqbsl2, 26 data. hqbjw3, data. hqbsl3, data. hqbjw4, data. hqbsl4, data. hqbjw5, data. hqbsl5, data. HQTime, data. updateTime, data. exponentRiseDown, data. riseDownPercent, 27 data. exponentSwing, data. timeID, data. riseNum, data. downNum, data. interval num, data. isDeleted? 1: 0, data. addTime, data. SMGUID); 28 context. database. executeSqlCommand (cmd); 29} 30 scope. complete (); 31} 32 catch33 {34 Console. writeLine ("error"); 35 return; 36} 37 38 var now3 = DateTime. now. timeOfDay; 39 Console. writeLine (string. format ("{0} Insert completed, time consumed {1}", now3, now3-now2); 40} 41} 42}

Insert statement I directly copied the statement dynamically generated by EF and modified it slightly. Although it is not an authentic ADO. NET, it is similar. Don't tell me that EF ExecuteSqlCommand uses another set of things.

Result:

Self-explanatory (it seems that some people do not understand, the blogger wants to say: SaveChanges does not have much performance loss ).

Conclusion: Wait for version 10.0! Or context. Configuration. AutoDetectChangesEnabled = false! (Thx to eflay & flytothemoon)

Reprinted please indicate the source of this article: http://www.cnblogs.com/newton/archive/2013/06/06/3120497.html