. Net batch big data insertion performance analysis and comparison (4. Batch insertion using dataadapter)

# Region use dataadapter <br/> Public static bool executedataadapterinsert (datatable DT, int batchsize) <br/>{< br/> int COUNT = DT. rows. count; <br/> bool flag = false; <br/> try <br/> {<br/> sqlconnection Cn = new sqlconnection (connectionstring ); <br/> sqlcommand cmd = new sqlcommand ("insert into testtable (ID, name) values (@ ID, @ name)", CN); <br/> cmd. parameters. add ("@ ID", sqldbtype. int, 4, "ID"); <br/> cmd. parameters. add ("@ name", sqldbtype. nvarchar, 20, "name"); <br/> cmd. updatedrowsource = updaterowsource. none; <br/> sqldataadapter da = new sqldataadapter (); <br/> da. insertcommand = cmd; <br/> da. updatebatchsize = batchsize; <br/> CN. open (); <br/> da. update (DT); <br/> CN. close (); <br/> flag = true; <br/>}< br/> catch (exception ex) <br/>{< br/> loghelper. error (ex. message); <br/> return false; <br/>}< br/> return flag; <br/>}< br/> # endregion

 

The result is as follows:

Use sqlserver dataadapter insert; recordcount: 40000; batchsize: 10; Time: 21569;

Use sqlserver dataadapter insert; recordcount: 40000; batchsize: 20; Time: 19607;

Use sqlserver dataadapter insert; recordcount: 40000; batchsize: 50; Time: 19647;

Use sqlserver dataadapter insert; recordcount: 40000; batchsize: 100; Time: 18649;

Use sqlserver dataadapter insert; recordcount: 40000; batchsize: 200; Time: 21823;

 

I was disappointed with the results of the test, and sqldataadapter sent a T-SQL command for each modification line, so the efficiency was not high.

 

In addition, asynchronous processing can be performed in other methods. The update () method of sqldataadapter does not support asynchronous results. Therefore, this method is not recommended.

 

Full text link:

. Net batch big data insertion performance analysis and comparison (1. Preparation)

. Net batch big data insertion performance analysis and comparison (2. Normal insertion and splicing SQL batch insertion)

. Net batch big data insertion performance analysis and comparison (3. Use transactions)

. Net batch big data insertion performance analysis and comparison (4. Batch insertion using dataadapter)

. Net batch big data insertion performance analysis and comparison (5. Use sqlbulkcopy)

. Net batch big data insertion performance analysis and comparison (6. Using Table value parameters)