[Advanced internal information]. NET data batch Write Performance Analysis article 2

[Advanced internal information]. NET data batch Write Performance Analysis article 2

In the previous articleArticleWe have already talked about some of the padding knowledge, so from the beginning of this article, we will start to formally study the performance of batch insert.

 

First, let's take a look at the things we mainly test. Because our articles in this series study the performance of sqlbulkcopy and SSIs, We will compare them in detail. For sqlbulkcopy, we are mainly interested in the following attributes, because these attributes have a great impact on performance:

 

Table locking: During batch data insertion, an exclusive lock is often created on the table to which data is inserted. On the one hand, this lock makes data insertion faster. On the other hand, other operations such as reading the table are waiting. We will use many different scenarios to test this attribute, so that you can have a clear understanding.

Batchsize: This parameter mainly defines the number of data entries inserted in batches each time. We found that there is no document describing the impact of this value on performance, so we will also study this attribute value.

 

Use internal transaction: In fact, many people often have incorrect understanding of sqlbulkcopy transactions. Through a lot of tests and practices, we found that if we do not need transactions when inserting data in sqlbulkcopy, the speed will be very fast. Of course, this speed is at the cost of other features.

 

In addition to some of the above attributes, we will also test the problems related to batch data import, as shown below:

1. We can import data to tables and heap tables with clustered indexes to see which table is the fastest to import and how to import data better.
2. Check the usage of database log files during data import. When using batch data import, you can not only write data files, but also constantly write logs. We can see the log size and other effects caused by different settings and methods.
3. Using the flag 610 flag of SQL Server trace can minimize the log records during batch data import. Is this flag worth using.

 

OK. Now let's prepare the test environment.

 

First, create a simple table with six fields, as shown below:

 

Obviously, the data size of each row in the table is about 320 bytes. During the test, we will fill the table with different data volumes, which will be from 60 to 6 million, it also checks whether there is a linear relationship between the relevant performance data.

 

To test the clustered index table and heap table, we also create an index as needed, as shown below:

 

 

 

Every time we test a scenario, we will destroy the table from the database and recreate it. The data in the inserted Table is randomly generated. In addition, two tests based on random values are also common:

 

1. The values of each row produced by each producer (that is, the data producer) are the same, and the values of each field must be random. It may be as follows:

 

 

2. The values of each row produced by each producer are completely different, similar to the following:

 

 

The above two tests are mainly used to check whether the data rows with the same data are compressed before the network input (because the same data values are repeated multiple times, this is a good opportunity for compression, which can greatly save network bandwidth and make data insertion faster ).

 

In addition, we adopt the simplest log mode for the database where our tables are located, which is also the method adopted by most databases. We will not test the Attribute combination of sqlbulkcopy and all the conditions above, because the results of many combinations can be inferred from experience, not all combinations affect performance.

 

To make the test more accurate, before sqlbulkcopy writes data in batches, all data producers will first prepare all the data, and then pass the data to sqlbulkcopy. We are concerned about sqlbulkcopy write performance and its impact on the database. Please remember this.

 

Next, we will enter the first scenario: A consumer (Data consumer, here, this data consumer is sqlbulkcopy) receives 6 million pieces of data and then imports the data into a heap table.

 

Multiple tests showed that the consumer execution time was 56 seconds. At the same time, we set the values of tablock and batchsize to 0. At the same time, we found that the log file was increased by 6 MB,

 

 

In this process, the network conditions are as follows:

 

 

We can see that the network usage is about 25% (the test uses 1 GB of bandwidth), that is, the bandwidth is not fully utilized.

 

In addition, the CPU shows that only one thread is running and does not fully use all cores. That is to say, if the data import client has multiple cores, we can increase the number of sqlbulkcopy and use parallel technology to import data to improve performance.

 

Next, let's test the above ideas as follows:

 

 

As shown in the figure above, we use four sqlbulkcopy.

 

The network is used as follows:

 

 

At this time, the bandwidth is fully utilized and the speed is increased by 300%.

today, we are here to give you a feeling! Next, let's compare more things.