[FeatureRequest] More convenient way to remove default MergeTableHints

[sven5]

Hi Pawel,

at first, thanks for your effort in this nice tool. It really helps a lot when it comes to bulk insert data.

However, I came across an issue while implementing bulk inserts. I'm using Parallel.ForEachAsync for async parallel processing of importing jobs. This really speeds things up.

Sometimes, I had exceptions with deadlocks, perhaps of parallel processing. Removing the default MergeTableHints seems to resolve these issues. You're using a default table hint WITH HOLDLOCK in SqlServerBulkInsertOrUpdateOptions.

I'm using the following options settings:

            var options = new SqlServerBulkInsertOrUpdateOptions
            {
                TempTableOptions =
                  {
                     BatchSize = 5_000,
                     EnableStreaming = true,
                     BulkCopyTimeout = TimeSpan.FromSeconds(120),
                     SqlBulkCopyOptions = SqlBulkCopyOptions.Default
                  },
            };

From my experience, it's not always a good idea to use hints in SQL databases. Ideally, the database optimizer should take care of the best plan to execute the statement.

To remove the default table hints I'll have to call options.MergeTableHints.Clear(). For convenience, it would be better to have another option for disabling default hints.

Regards Sven

[PawelGerr]

I provided a new ctor overload for the options.

var options = new SqlServerBulkInsertOrUpdateOptions(holdLockDuringMerge: false);

I can release a new version if it works for you.

[sven5]

yes, would be great, thanks.

[PawelGerr]

7.1.0 is released