SQL Server data insertion performance in small memory

For SQL performance, it's been a long time since you've been watching. Because of the recent project neither tens of millions of of the massive data nor too much performance requirements, so in the performance is not too much effort. However, not long ago and friends chat between the topic a turn on the program, he said he used Delphi to do a data import function, when inserting data feel very slow. With a personal understanding of SQL, it is recommended that he use the bulk Insert method, and very seriously tell him that this should be much faster. In real work, similar to the ability to import data in bulk is very common, perhaps a careless we hang the server. That is what to do to leave the server to stay alive, so that users do not have to finish uploading to see the film and then see the results of the upload? To do a little experiment, and simply say their own views.

Environment:

Cpu:i7;

Memory: 6G;

Database: SqlServer2008;

Data Volume: 10W

　　

Experimental content:

Create Loopinsert and Batchinsert two functions, use stitching in loopinsert to generate INSERT into XXX values (...) insert into XXX values (...). form of SQL execution and returns the execution time of SQL, Batchinsert also uses stitching SQL to generate INSERT into XXX values (...), (...). Resembles SQL and returns the execution time of the SQL. Use the console program to execute two functions multiple times and output execution results.

Table structure:

CREATE TABLE tqrcode    (      ID INT PRIMARY KEY  IDENTITY (1, 1),      Name NVARCHAR,      Remark NVARCHAR (300)    )

　　

C # implementation code:

1      Public classdatainerttest2     {3         /// <summary>4         ///Loop Insert5         /// </summary>6         /// <returns>execution Time (seconds)</returns>7          Public DoubleLoopinsert (intcount)8         { 9StringBuilder sql =NewStringBuilder ();Ten              for(inti =0; I < count; i++) One             { ASql. Append ("Insert into Tqrcode (Name,remark) VALUES (' This is the first"). Append (i). Append ("data ', ' This is the first"). Append (i). Append ("bar Data _remark ')"); -             } -             //Time Statistics the             varStopwatch =NewStopwatch (); - stopwatch. Start (); -             NewHelper (). Excute (SQL. ToString ()); -             returnstopwatch. Elapsed.totalmilliseconds; +         } -  +         /// <summary> A         ///BULK Insert at         /// </summary> -         /// <returns>execution Time (seconds)</returns> -          Public DoubleBatchinsert (intcount) -         {  -StringBuilder sql =NewStringBuilder (); -Sql. Append ("Insert into Tqrcode (Name,remark) Values"); in              for(inti =0; I < count; i++) -             { to  +Sql. Append ("(' This is the first"). Append (i). Append ("data ', ' This is the first"). Append (i). Append ("bar Data _remark ')"); -                 ifI -==0) the                 {  *Sql. Append ("Insert into Tqrcode (Name,remark) Values"); $                 }Panax Notoginseng                 Else if(I < count-1) -                 { theSql. Append (","); +                 } A             } the  +             //Time Statistics -             varStopwatch =NewStopwatch (); $ stopwatch. Start ();  $             NewHelper (). Excute (SQL. ToString ()); -             returnstopwatch. Elapsed.totalmilliseconds; -         } the}

C # implementation code

Note: A maximum of 1000 data is inserted in SQL Server in a single batch, otherwise it will prompt us: The number of row value expressions in the INSERT statement exceeds the maximum allowed Number of the row values.

Test code:

1     class Program2     {3         Static voidMain (string[] args)4         {5              for(inti =0; I <3; i++)6             {7                 varobj =Newdatainerttest ();8                 varT1 = obj. Loopinsert (100000);9                 vart2 = obj. Batchinsert (100000);Ten  OneConsole.WriteLine ("Loopinsert: {0}", T1); AConsole.WriteLine ("Batchinsert: {0}", T2); -Console.WriteLine ("--------------------------------------------------"); -                the             }  - console.readline (); -         }  -}

Test Code

　　

Test results:

The 3-time results were obvious and the efficiency gap was more than 10 times times. The BULK insert is faster than the loop insert, mainly because each insert into SQL Server is a separate transaction, the loop inserts 500 data is 500 transactions, and one time to insert 500 data, there is only one transaction. The transaction reduces the consumption of nature is also small. and frequent transaction commits quite affect the performance of the database, but also play a role in the overall performance of the system (hey, accidentally maybe the server hangs).

It is important to note that because of the small amount of data in the test, both methods are used in a single storage mode, which reduces the number of database connections. But there's a big drawback: memory consumption can be huge. 10w Data SQL splicing OK, if it is 100w line that is not necessarily. So, if a single piece of data is large, it is recommended to submit every hundreds of or thousands of lines, this number is exactly how much to tailor, balance memory consumption.

SQL Server data insertion performance in small memory