Difference between SQL Server and non-SQL Server

Yesterday, when testing a batch insert record database operation, it was found that enabling the transaction has a significant impact on the performance. According to the current environment, there was a 6-fold gap in the test.

Insert 500/5000/50000, respectively,

The transaction is started in 480 ms/4546 ms/47407 ms respectively.

When no transaction is started, the values are 3100 ms/30859 ms/308802 ms respectively.

Why? After careful analysis, this should be related to sqlserver's background memory management and data access mechanism.

When we submit data to the server, sqlserver will operate in the following order:

1) copy data to memory; 2) write operation logs; 3) save data to disk data files.

In these three steps, the efficiency of step 1 and Step 2 is relatively high (memory writing is not required, log writing is sequential writing, and complex verification is not required, so it is faster ),

However, writing data files to the disk in step 3 is time-consuming. (Verification and Index Update are required)

OK. Let's explain the differences between the two situations:

1. When we execute a (only one insert) insert statement without starting a transaction, sqlserver will start the transaction for this statement by default.

After the statement is completed, an automatic commit operation is performed to refresh the data to the disk data file.

If we loop multiple times, it will take up more system time.

2. When a transaction is started, we only have the last commit operation. Once the operation log is successfully written, the system reports that the transaction has been submitted successfully. (In fact, the background is still refreshing data in the disk data file ).

Summary: Since the disk data file is refreshed in a serialized manner, it is better to reduce the number of commit operations and enable transactions. Otherwise, the default transaction will not stop committing, occupying our valuable time resources.
Attached Test Code :
Static void testbatchinsert ()
{
Using (iconnectionhelper CH = new connectionhelper ())
{
Long T = datetime. Now. ticks;
Try
{
Ch. begintransaction ();
For (INT I = 0; I <5000; I ++)
{
Xml_companyentity Ce = new xml_companyentity ();
Ce. setnextmaxvalue (CH );
Ce. xcm_name = "Eric. Zhang" + I. tostring ();
Ce. xcm_address = "Hangzhou" + I. tostring ();
Ce. insertentity (CH );
}
Ch. committransaction ();
}
Catch
{
Ch. rollbacktransaction ();
Throw;
}
Timespan Ts = new timespan (datetime. Now. ticks-t );
Console. writeline (TS. totalmilliseconds );
}
}