Good SQL statements can also improve efficiency

In the previous articleArticleThe slow data exchange in the database is mentioned. I still see this method when reading the description of sqlbulkcopy. I directly construct an SQL statement to insert data and execute it using transactions, divide 100 records into 100 segments for data insertion. I found that the execution was fast. if there are more than one million records, it will take 1-2 seconds to complete the execution, which is relatively high. However, if you get one million select statements at a time and run them together, the execution will not succeed. When I test the statements, an exception will be thrown, indicating that the connection times out, even if I set the timeout time to 2000 s, it also throws a timeout exception. I think the Union string is too long, causing the execution to fail.

Let's talk about the SQL format: here is an example. After this SQL statement is executed, 10 records are inserted at a time, and 10 SQL statements are combined into one SQL statement for execution, if 100 inserts are combined into one SQL statement for execution, the effect is quite obvious.

 
InsertIntoTesttable ([column0], [column1], [column2], [column3], [column4])
 

  select   '77' ,  '74 ',  '30' ,  '61 ',  '30'   Union  

  select   '85' ,  '6' ,  '69' ,  '26' ,  '73 '  Union  

  select   '87 ',  '82 ',  '10' ,  '79 ',  '83'   Union  

  select   '61' ,  '55' ,  '79 ',  '7' ,  '12'   Union  

  select   '77' ,  '36' ,  '47' ,  '35' ,  '13'   Union  

  select   '83 ',  '72 ',  '36' ,  '93 ',  '47'   Union  

  select   '97 ',  '49' ,  '66' ,  '12' ,  '84 '  Union  

  select   '95' ,  '47' ,  '10' ,  '89 ',  '27'   Union  

  select   '17' ,  '47' ,  '46' ,  '80' ,  '50'   Union  

Select '8','54','48','64','65'
 

 

The problem now becomes the combination of SQL statements. From the above example, we can see that the required parameter has a table structure, that is, to obtain the field name of the table, it is best to have a field type, you can set the values in the following SELECT statement based on the field type, such as those of the datetime type. You still need to perform some conversions.

Preliminary ideas:

• 1. Define a struct to store table structure information. The most basic attributes are field name, field type, field length, field value, and other attributes, which need to be added.
• 2. You need to write a method to splice the SQL statement in the graph. It is best to set the number of select statements for a single value into a group. Also, when a SELECT statement is built, there must be multiple records. How can we obtain the values following by concatenating the datareader values? Or do I just get a datatable and traverse it again to splice it? It is found that the speed of directly retrieving datareader is still very fast.
• 3. What should I do if a binary field exists in the table?
• 4. The above is the method used to insert data. What should the SQL statement look like when a large amount of data is updated? Is it difficult to delete the file before inserting it?

Note: make sure that the process of splicing SQL statements does not take too much time. It only takes 2 seconds to execute the SQL statement. It takes 10 seconds to execute the concatenated SQL statement before the result is executed, that's not worth it.

If the above problems can be solved, there will be no speed problems when a large amount of data is inserted in the future.

A codesmith template is provided to generate test SQL statements. If anyone wants to test it, take it.

/Files/bluesky4133/create inserttest sentence. Zip

My tags:Massive Data Operations and SQL statement Optimization