SQL BULK INSERT record in Delphi

http://www.cnblogs.com/azhqiang/p/4050331.html

 

When performing database operations, we often encounter the situation of writing records to the database in batches. Here I provide 3 operation methods:

1. Single statement loop insertion, this is the dumbest method:
 for i: = 0 to 100 do ADOCommand1.Execute (‘IINSERT INTO .....’) // Pseudo code, just to explain the meaning
2. Multiple statements are inserted in batches. Multiple Insert statements are combined into one statement and executed at one time:
 for i: = 0 to 100 do sqlstr: = sqlstr + ‘INSERT INTO ...’; ADOCommand1.Execute (sqlstr);
3. Transaction Insert:
ADOConnection1.BeginTrans; for i: = 0 to 100 do ADOCommand1.Execute (‘IINSERT INTO .....’); ADOConnection1.CommitTrans;

  How to use these operations, I wo n’t elaborate on them, let ’s talk about their efficiency, I think this is what we care about. I did this experiment: build a data table with 10 fields, and then use this to send data The table inserts 10, 100, 500, 1000, 5000, 10,000 records, and calculates the time they take. More records have not been tested because of time, but this is enough to explain the problem. Take a look at these two graphics: we It can be seen that when the number of inserts in a batch is greater than 1000, there is a big difference between the three insertion methods.The time taken to splice the SQL statement is greatly increased, and the time spent for transaction processing is about 1/2 of the time taken for a single insert. Let's look at what it looks like when the number of records is less than 1000: We can see that when the number of records is less than 100, the efficiency of splicing SQL statements is very high and can be used preferentially.

 to sum up:     
Transaction processing is the only choice! In fact, most of the time spent splicing SQL statements is wasted on splicing statements, (don't understand? Go and see the principle of string!)

 Original: http://anony3721.blog.163.com/blog/static/511974201022242736689/
 

Batch insert records in SQL in Delphi