EF Big Data Bulk Add performance issues

A few days ago to do a bulk messaging function, because to bulk write data to the message table, the use of the EF framework of the insertion method; Do not know, the use of a scare jump, the 10,000 data will take several minutes, corresponding to the pursuit of the user experience of me this is very intolerable, and later instead splicing SQL, performance improved several times ; now to share the experience:

The original method is similar to this:

1          PublicActionResult Add (itemdetails entity)2         {3             varSW =NewStopwatch ();4 SW. Start ();5             using(db)6             {7                  for(vari =0; I <10000; i++)8                 {9 db. Itemdetails.add (entity);Ten db. SaveChanges (); One                 } A             } - SW. Stop (); -             varDate =SW. Elapsed; the             returnJson (string. Format ("Total time: {0}", date)); -}

Take a look at the time it takes to add 10,000 data:

The 10,000 data takes so long, if millions of data, it can not be imagined, and then to look at the optimized:

Methods for generating SQL:

1  Public classItemdetailbatch2     {3          Public Static stringBatchadd (itemdetails entity)4         {5SqlParameter [] paras=6             {7                 NewSqlParameter ("@Item_Name", SqlDbType.VarChar, -), 8                 NewSqlParameter ("@Item_Price", SqlDbType.Int),9                 NewSqlParameter ("@Image_Name", SqlDbType.VarChar, -), Ten                 NewSqlParameter ("@Description", SqlDbType.VarChar, -), One                 NewSqlParameter ("@AddedBy", SqlDbType.VarChar, -) A             }; -paras[0] . Value=entity. Item_name; -paras[1]. Value =entity. Item_price; theparas[2]. Value =entity. Image_name; -paras[3]. Value =entity. Description; -paras[4]. Value =entity. Addedby; -             varsb=NewStringBuilder (); +Sb. Append ("INSERT INTO ItemDetails (Item_name,item_price,image_name,description,addedby)"); -Sb. AppendFormat ("values (' {0} ', {1}, ' {2} ', ' {3} ', ' {4} ')", paras[0]. Value, paras[1]. Value, paras[2]. value,paras[3]. Value, paras[4]. Value); +             returnsb. ToString (); A         } at}

Controller Layer Invocation:

1          PublicActionResult Add (itemdetails entity)2         {3             varSW =NewStopwatch ();4 SW. Start ();5             using(vardb =Newshoppingdbconn ())6             {7                 varsql =NewStringBuilder ();8                  for(inti =0; I <10000; i++)9                 {Ten                     //Generate SQL One SQL. Append (Itemdetailbatch.batchadd (entity)); A                 } -                 //Execute SQL once - db. Database.executesqlcommand (SQL. ToString ()); the             } - SW. Stop (); -             varDate =SW. Elapsed; -             returnJson (string. Format ("Total time: {0}", date)); +}

Interface data:

The same 10,000 total time-consuming:

EF is not added once to the database to submit once, and directly splicing SQL method is to reduce the number of interactions with the database, one-time commit to execute all the data;

EF Big Data Bulk Add performance issues