Slow Insert on Tables with Trigger when using BulkInsertAsync

[dcutic]

I use Kafka as datasource and insert the messages 1:1 into an MSSQL DB. The target tables each have a trigger, which updates only 2 columns during the insert (sysUser, sysTime).
What I have noticed is that the insert into the target table with the trigger switched on is much slower than if switched off. As a test, I wrote an insert loop in SSMS with 100,000 inserts into the target table. Once with enabled and once with disabled trigger. The processing time was almost the same in both cases (with trigger 3min 15sec, without trigger 3min 10sec).

I use BulkInsertAsync or SingleInsertAsync-Method for inserting the Data into DB.

Further technical details

• EF version: .net 8
• EF Extensions version: 8.103.1
• Database Server version: [SQL Server 2017]

[JonathanMagnan]

Hello @dcutic ,

Indeed, triggers themselves can slow down the operation but another reason within our library could also explain it

What happens when we return data from a table with a trigger

In this section, I will assume that your table has an identity column that is currently automatically outputted by default within our library.

When a trigger is used, we cannot simply use the OUTPUT Clause since that's not compatible with a trigger, we need instead to OUTPUT into a temporary table, and we perform a SELECT after on.

So there are some additional steps:

• Creating the temporary table
• Insert existing identity into a temporary table (for the UPDATE part of the BulkMerge)
• Outputting identity into the temporary table
• Selecting data from the temporary table
• Dropping the temporary table

Which could indeed explain the few additionals seconds it takes.

One way to easily solve this if you don't need to return data such as the identity value is disabling the AutoMapOutputDirection:

context.BulkMerge(list, options =>
{
	options.AutoMapOutputDirection = false;
});

Let me know if my explanation make sense and if you need some additional help.

Best Regards,

Jon

[dcutic]

Hello @JonathanMagnan

Thank you for your answer. Your explanation make sense for me but will all this happen the same, when I use the BulkInserAsync or only when using BulkMerge?

Best Regards
Dani

[JonathanMagnan]

Hello Dani,

If you output data such as the identity column, then the answer is yes; besides the step I specified, it was for the UPDATE part.

But again, the options.AutoMapOutputDirection = false; option fixes the problem.

Best Regards,

Jon