Mass data Insertion database efficiency comparison test---ADO. NET under SqlBulkCopy () vs LINQ under Insertallonsubmit ()

Summary: Use. NET-related technology to insert large amounts of data into a database is a common operation. This article compares the two Technologies of ADO and LINQ, using the SqlBulkCopy () andinsertallonsubmit () methods respectively. It is concluded that the insertion efficiency of the same insertion effort (1w data) is 200 times times greater!

Test scenario:

Prepare two databases TestDB and TESTDB2, with table: T_users. The table structure looks like this:

The SqlBulkCopy () insertion method is as follows:

private static Stopwatch insertusingsqlbulkcopy (list<userinmemory> List) {Stopwatch Stopwatch = n            EW Stopwatch ();            Stopwatch.start ();            DataTable dt = new DataTable (); Dt.            Columns.Add ("ID"); Dt.            Columns.Add ("UserName"); for (int i = 0; i < list. Count; i++) {DataRow row = dt.                NewRow ();                row["ID"] = list[i].id; row["UserName"] = List[i].                UserName; Dt.            Rows.Add (row); } using (SqlConnection con = new SqlConnection (connStr2)) {con.                Open ();                        using (SqlBulkCopy bulkcopy = new SqlBulkCopy (con)) {try { Bulkcopy.destinationtablename = "dbo."                        T_username ";                        BULKCOPY.COLUMNMAPPINGS.ADD ("id", "id");                        BULKCOPY.COLUMNMAPPINGS.ADD ("UserName", "UserName"); BulkCopy.writetoserver (DT); } catch (Exception ex) {Console.WriteLine (ex.                    ToString ()); } finally {con. Close ();            }}} stopwatch.stop ();        return stopWatch; }

The LINQ insertion method is as follows:

private static Stopwatch Insertusinglinq (list<t_user> _list)        {            Stopwatch Stopwatch = new Stopwatch ();            Stopwatch.start ();            Dataclasses1datacontext dataContext = new Dataclasses1datacontext ();            DataContext.T_Users.InsertAllOnSubmit (_list);            Datacontext.submitchanges ();            Stopwatch.stop ();            return stopWatch;        }

Use the above code to import 10,000 user data into the database respectively.

The results are as follows:

The same insertion effort (1w data), the conclusion:

1 ADO. NET under the SqlBulkCopy () method is 266 times times greater than the Insertallonsubmit () method insertion Efficiency under LINQ to SQL!

2 LINQ is easy to use, short in code, low in learning, and elegant in language, but it is preferable to use the ADO method if it is highly efficient for large data-related operations.

Related Source: http://download.csdn.net/detail/fanrong1985/8130953

Mass data Insertion database efficiency comparison test---ADO. NET under SqlBulkCopy () vs LINQ under Insertallonsubmit ()