DB2 Database Performance Comparison of delete in ID and batch Delete

Delete Volume: Delete 10,000 at a time.

The first way to delete data by deleting from table where ID in (a bunch of IDs) is to first query the data that needs to be deleted from the database, pass the ID to the MyBatis framework, and show the following:

      <DeleteID= "Deletebyid"ParameterType= "Map">DELETE from Tbl_acc_his_source WHERE ID in<foreachItem= "id"Index= "Index"Collection= "Idlist"Open="("Separator=","Close=")">#{id}</foreach>    </Delete>

Each time elapsed data is as follows:

[374, 206, 206, 205, 482, 231, 460, 297, 277, 218, 232, 203, 228, 272, 207, 280, 203, 195, 272, 374, 523, 314, 207, 222, 2 47, 275, 269, 203, 216, 190, 310, 225, 277, 238, 237, 213, 228, 235, 236, 204, 240, 217, 213, 191, 209, 260, 268, 317, 231 , 207, 200, 203, 204, 202, 209, 217, 212, 310, 216, 253, 201, 204, 212, 191, 211, 200, 237, 234, 279, 194, 203, 250, 199, 228, 224, 238, 259, 279, 257, 299, 196, 264, 299, 210, 228, 227, 219, 203, 207, 328, 213, 237, 348, 315, 210, 219, 325, 23 6, 196, 197, 211, 229, 187, 276]

Performed 104 times, averaging 243 milliseconds.

In the second way, using batch delete, the code is as follows:

 private  void  Batchdeletea (list< Long> IDs) {sqlsession batchsqlsession  = Getbat        Chsession ();  try   { for   (Long id:ids) {batchsqlsession.delete (" acchissourceent            Ity.delete ", id);        } batchsqlsession.commit ();  finally   {batchsqlsessionutils.closes        Qlsession (batchsqlsession); }    }

    Private sqlsession getbatchsession () {        return  batchsqlsessionutils.getsqlsession ( Sqlsessionfactory, Executortype.batch);    }

Among them Acchissourceentity.delete are as follows:

  <id= "delete"  parametertype= "Java.lang.Long"  >    Delete from      tbl_acc_his_source    where      ID = #{id,jdbctype=bigint}   </ Delete >

Execute 119 times, each time as follows:

[454, 253, 230, 224, 219, 214, 217, 215, 203, 204, 240, 250, 243, 230, 272, 208, 205, 223, 450, 235, 282, 336, 266, 209, 2 12, 222, 214, 431, 256, 238, 209, 456, 199, 616, 207, 336, 249, 196, 232, 230, 291, 240, 212, 266, 245, 229, 217, 211, 218 , 210, 211, 218, 222, 215, 200, 219, 208, 256, 208, 208, 248, 226, 220, 234, 219, 299, 478, 501, 251, 223, 510, 244, 265, 246, 278, 279, 244, 255, 251, 236, 260, 273, 246, 503, 280, 238, 256, 244, 255, 215, 217, 224, 220, 230, 236, 198, 252, 24 6, 270, 262, 263, 252, 233, 223, 262, 230, 478, 240, 269, 238, 232, 260, 232, 222, 228, 223, 231, 246, 247]

Average Time: 258

Conclusion: almost the same.

PS: How to delete a batch of data if a batch of data is detected. Use 50,001 batches, faster than 100,001 batches.

DB2 Database Performance Comparison of delete in ID and batch Delete