Performance difference with BulkOperation Insert and Context.BulkInsert

[keerthankumarCSnet]

Performance difference with BulkOperation Insert and Context.BulkInsert

[JonathanMagnan]

Hello @keerthankumarCSnet ,

Could you provide us your table definition ([email protected]), we will try your scenario on our side.

Perhaps we will also find something that could explain why it's so long.

Best Regards,

Jonathan

[keerthankumarCSnet]

Hi Jonathan, Please find the table definition below, which get created based on the POCO: [Key] //[DatabaseGenerated(DatabaseGeneratedOption.Identity)] //public int Id { get; set; } public Guid GUID { get; set; } public Guid ActivityGUID { get; set; } public Guid? ClientGUID { get; set; } public Guid UserGUID { get; set; } public string AuditEvent { get; set; } public string Notes { get; set; } public DateTime Created { get; set; } public DateTime Modified { get; set; } If we set “Id” column as the Key and use BulkOperation.BulkInsert() method the performance is far better than using context.BulkInsert<T> and “GUID” as the Primary Key column. Look forward to hearing from you. Thanks, Keerthan

[JonathanMagnan]

Hello @keerthankumarCSnet ,

I'm very sorry, we forget to answer your question.

One way to improve the performance is to make sure SqlBulkCopy is directly used under the hood by our library. If you have some Identity column, you will need to use AutoMapOutputDirection = false to improve the performance.

You can use SQL Profiler to make sure SqlBulkCopy is used. Our library uses it whenever it can but in some scenario when the identity value must be outputted, a new strategy is used instead.

For example:

context.BulkInsert(result, options =>
{
	options.AutoMapOutputDirection = false;
	options.SqlBulkCopyOptions = SqlBulkCopyOptions.TableLock;
});

Best Regards,

Jonathan

[JonathanMagnan]

Hello @keerthankumarCSnet ,

This issue will be closed since it has been answered (a little bit too late).

Feel free to reopen it if you feel otherwise.

Best Regards,

Jonathan