In the case of database operations, we often encounter batches of records written to the database. Here I provide 3 ways to operate: 1. A single statement loops through the INSERT, which is the stupidest way:
For I: = 0 To
Adocommand1.execute (' Iinsert into ... ')//pseudo code, just stating meaning 2. Multiple statements are bulk inserted, and multiple INSERT statements are spelled into a single statement, one execution at a time:
For I: = 0 To
SQLSTR: = sqlstr + ' INSERT into ... ';
Adocommand1.execute (SQLSTR); 3. Transaction processing Insert:
Adoconnection1.begintrans;
For I: = 0 To
Adocommand1.execute (' Iinsert into ... ');
Adoconnection1.committrans; How to use these operations, I do not elaborate, I say their efficiency issues, I think this is what we care about. I did an experiment like this:
Build a 10-field data table and then insert 10,100, 500, 1000, 5000, 10,000 records into the data table to calculate the time they need. More records were not tested because of the time problem, but that was enough to explain the problem. Take a look at these two graphs: we can see that when the number of insert bars is greater than 1000, the 3 kinds of insertions make a big difference, the time spent on stitching SQL statements is greatly increased, and transaction processing takes about 1/2 of the time spent in 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. Summarize:
Transaction processing is not the second choice Ah! In fact, the concatenation of the SQL statement method spends most of the time wasted on splicing statements, (do not understand?) to see the principle of string!) Original: http://anony3721.blog.163.com/blog/static/511974201022242736689/
SQL BULK INSERT record in Delphi
