how perform batch operation with dapper orm?

batch-file bulk dapper orm

Question

I Have dapper orm in project and i have save alto of data (1200000row) in database but in transaction with dapper is very slow i want fast.with nhibernate (session statetless)is slow. I think dapper is fast because that fetch data(700000) with nhibernate in 33 second that with dapper in 9 second.

how solved problem ?

my code is :

IDbTransaction trans = connection.BeginTransaction();
connection.Execute(@"
    insert DailyResult(Id, PersonId,DateTaradod,DailyTaradods)
    values(@Id, @PersonId,@DateTaradod,@DailyTaradods)", entity, trans);                                    
trans.Commit();

Expert Answer

There is no mechanism to make inserting 1200000 rows in a transaction instant, via any regular ADO.NET API. That simply isn't what the intent of that API is.

For what you want, it sounds like you should be using SqlBulkCopy. This supports transactions, and you can use FastMember to help here; for example:

IEnumerable<YourEntity> source = ...
using(var bcp = new SqlBulkCopy(
    connection, SqlBulkCopyOptions.UseInternalTransaction))
using(var reader = ObjectReader.Create(source,
         "Id", "PersonId", "DateTaradod", "DailyTaradods"))
{
    bcp.DestinationTableName = "DailyResult";
    bcp.WriteToServer(reader);
}

It also supports external transactions, but if you are going to "create tran, push, commit tran" you might as well use the internal transaction.

If you don't want to use SqlBulkCopy, you can also look at table-valued-parameter approaches, but SqlBulkCopy would be my recommended API when dealing with this volume.

Note: if the table has more columns than Id, PersonId, DateTaradod and DailyTaradods, you can specify explicit bcp.ColumnMappings to tweak how the insert behaves.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why