A colleague wrote a tool to compare the two methods of inserting records into a SQL SERVER database in C# and found that the performance of the two methods is extremely different:
\
1, SqlBulkCopy, batch insert method, 50,000 records, only 4 seconds
2, pieced together a SQL statement, single insert, 50,000 records, 7 minutes
\
// The first method is batch insert
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy((SqlConnection)connection))
{
sqlBulkCopy.DestinationTableName = dataTable.TableName;
sqlBulkCopy.BatchSize = 100000; // The amount of data inserted in a batch
sqlBulkCopy.BulkCopyTimeout = 360;
// A custom DataTable corresponds to a database field
foreach (DataColumn column in dataTable.Columns)
{
sqlBulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
}
sqlBulkCopy.WriteToServer(dataTable);
}
Copy the code// Single insert
sqlString = "INSERT INTO Table2 (Int,Long,Float,Real,Date,NText,String1,String2,String3,String4) VALUES (@Int,@Long,@Float,@Real,@Date,@NText,@String1,@String2,@String3,@String4)";
IDataParameter[] parameters = new IDataParameter[10];
int count = Convert.ToInt32(txtCount.Text);
for (int i = 0; i < count; i++)
{
parameters[0] = DBHelper.CreateParameter("Int", i);
parameters[1] = DBHelper.CreateParameter("Long", i * 10);
parameters[2] = DBHelper.CreateParameter("Float", i * 100 + 0.33);
parameters[3] = DBHelper.CreateParameter("Real", i * 1000 + 0.999);
parameters[4] = DBHelper.CreateParameter("Date", DateTime.Now);
parameters[5] = DBHelper.CreateParameter("NText", DateTime.Now.ToLongTimeString());
parameters[6] = DBHelper.CreateParameter("String1"."String1_" + i.ToString());
parameters[7] = DBHelper.CreateParameter("String2"."String2_" + (i * 100 + 0.33).ToString());
parameters[8] = DBHelper.CreateParameter("String3"."String3_" + (i * 1000 + 0.999).ToString());
parameters[9] = DBHelper.CreateParameter("String4"."String4_" + DateTime.Now.ToShortDateString() + "_" + DateTime.Now.ToLongDateString());
DBHelper.ExecuteNonQuery(connection, CommandType.Text, sqlString, parameters);
}
connection.Close();
Copy the codeWhy are they so different?
Similar to method 2, which I often use, the database connection string is opened before the loop starts, only opened once, and then closed at the end of the loop, which looks perfect.
But I’ve always suspected that using code to manipulate a database is not as fast as working directly in the database. So today specifically in the database to run the following statement
\
3, method three, directly in the database run cycle insert
use [test]
go
TRUNCATE TABLE table2;
declare @i int
set @i = 0;
while @i < 50000
BEGIN
INSERT INTO [dbo].[Table2]
([Int]
,[Long]
,[Float]
,[Real]
,[Date]
,[NText]
,[String1]
,[String2]
,[String3]
,[String4])
VALUES
(0
,0
,0.33
,0.999
,'the 2012-10-10 15:23:01. 433'
,'15:23:01'
,'String1_0 '
,'String2_0. 33'
,'String3_0. 999'
,'string4_2012-10-10 _ October 10, 2012'
);
SET @i = @i + 1;
end;
Copy the codeThe resulting time is also more than 7 minutes, similar to the operation in C#!
As my colleague told me,
Check online today for an article on why Microsoft offers such a big difference in bulk write and single insert performance
As a result, bulk is written directly by copying files, so servers are pretty much the same
A single entry directly reflects the read and write performance of the database. Each entry traverses the entire table before being inserted
Therefore, the speed reflects the read/write performance data speed of the database.
\
Inserting a record requires traversing the entire table? This was the first time I had heard it and I was skeptical.
Commit a transaction every time a record is inserted. However, when SQL SERVER writes data, it does not write data directly to the hard disk. Instead, it modifies data in the memory and then periodically checks in to write the changes to the disk or writes the dirty data back to the hard disk. In that case, does normal COMMIT data consume resources this way?
Don’t know. But you can try.
This should remove the default setting of committing a transaction for every insert in the loop:
use [test]
go
TRUNCATE TABLE table2;
declare @i int
set @i = 0;
BEGIN TRAN;
while @i < 50000
BEGIN
INSERT INTO [dbo].[Table2]
([Int]
,[Long]
,[Float]
,[Real]
,[Date]
,[NText]
,[String1]
,[String2]
,[String3]
,[String4])
VALUES
(0
,0
,0.33
,0.999
,'the 2012-10-10 15:23:01. 433'
,'15:23:01'
,'String1_0 '
,'String2_0. 33'
,'String3_0. 999'
,'string4_2012-10-10 _ October 10, 2012'
);
SET @i = @i + 1;
end;
COMMIT TRAN;
Copy the codeThe result is a performance leap, 50,000 records only 4 seconds!
\
In fact, this can also be verified through mode 4
4. Method 4
Create table table1 (table1, table2, table2); SELECT…
TRUNCATE TABLE table1;
INSERT INTO [test].[dbo].[Table1]
([Int]
,[Long]
,[Float]
,[Real]
,[Date]
,[NText]
,[String1]
,[String2]
,[String3]
,[String4])
SELECT [Int]
,[Long]
,[Float]
,[Real]
,[Date]
,[NText]
,[String1]
,[String2]
,[String3]
,[String4]
FROM [test].[dbo].[Table2]
Copy the codeFour seconds, even.
\
SqlBulkCopy imports a DataTable object into the database. Before importing it, edit the DataTable directly, which is more convenient.
In addition, this method is similar to BULK INSERT in SQL SERVER. BULK INSERT does not generate logs when the database is in simple mode, so SqlBulkCopy should be the fastest.
SqlBulkCopy is similar to SqlDataAdapter. In my experience, this operation is also faster than single insert, but I don’t know why.
In fact, it is not clear what the reason is!
\
\
\
\