1

I am looking for the fastest way to do a bulk insert using Dapper Plus. I've been looking around and I haven't found any answer, neither on this site, nor on the Dapper Plus site.

Right now I am using something like :

DapperPlusManager.Entity<T_PARENT>().Table("T_PARENT")
 .Identity(c => c.PK)
 .AfterAction((kind, parent) =>
  {
    if(kind == DapperPlusActionKind.Insert)
     {
       parent.T_CHILDREN.ToList().ForEach(child => child.FK = parent.PK); 
     }
  }

Then 

Connection
 .BulkInsert<T_PARENT>(Parents)
 .ThenBulkInsert(c => c.T_CHILDREN);

Or 

DbTransaction Trans = Connection.BeginTransaction();
Trans
 .BulkInsert<T_PARENT>(Parents)
 .ThenBulkInsert(c => c.T_CHILDREN);
Trans.Commit();

While it works, it is quite slow when inserting a huge number of complex objects.

In my case we are inserting a total of 100 000 object to the database, and with this method it is taking around 60 secondes to complete.

Is there a way to perform this operation faster ?

Edit :

I should have mentioned that I am working on an Oracle 11g database. I also should have mentioned that I have no other choice than to use Entity Framework and Dapper Plus for this.

Edit : In my test case, I basically have two situations :

Case 1 : for each T_PARENT we insert, we also insert several thousands T_CHILD.

Case 2 : for each T_CHILD we insert, we only insert a few T_CHILD (less than 10).

In case 1, the operation is quite fast (around 10s), but case 2 is comparatively slower (around 30s).

The reason seems obvious, as in case 1, the number of operations on T_PARENT is much lower than in case 2.

NobDoe
  • 11
  • 3
  • Possible duplicate of [Insert 2 million rows into SQL Server quickly](https://stackoverflow.com/questions/13722014/insert-2-million-rows-into-sql-server-quickly) – Liam Nov 06 '18 at 13:28
  • FYI ["Dapper plus"](https://github.com/zzzprojects/Dapper-Plus) ("Like Dapper") appears to be nothing to do with [Dapper](https://github.com/StackExchange/Dapper). – Liam Nov 06 '18 at 13:31
  • Dapper Plus is an extension to Dapper for doing bulk operations. – Icemanind Nov 06 '18 at 13:33
  • 1
    @Liam in [Insert 2 million rows into SQL Server quickly](https://stackoverflow.com/questions/13722014/insert-2-million-rows-into-sql-server-quickly) the proposed solutions are using SqlBulkCopy, but in my case I need to use Dapper Plus. – NobDoe Nov 06 '18 at 13:34
  • @Icemanind it doesn't seem to reference or use Dapper. It seems to to just ripped off the name – Liam Nov 06 '18 at 13:34
  • 1
    What about [`Oracle.DataAccess.Client.OracleBulkCopy`](https://stackoverflow.com/a/3602749/1260204) – Igor Nov 06 '18 at 13:49

0 Answers0