Bulk copy is super slow and always timeout

[aloksharma1]

Hi, please check the following code:

var items = rolePermissions.SelectedRoleActions.Select(x => new Models.RolePermissions
                {
                    Id = Sql.NewGuid(),
                    PermissionName = x,
                    RoleId = rolePermissions.CurrentRole,
                    RoleStatus = RoleStatus.Permitted,
                    SiteId = SiteId,
                });
                await repository.GetDbContext().BulkCopyAsync(new BulkCopyOptions
                {
                    //BulkCopyTimeout = 5000
                }, items);

for testing i am only sending 2 rows of data here, but still bulkcopy function is not working, all i get is the following error:

Win32Exception: The wait operation timed out.
Unknown location

SqlException: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Operation cancelled by user.
The statement has been terminated.

other functions are like single insert/fetch etc are working ok.

i am using latest version of linq2db and efcore 5

[sdanyliv]

Just for check, could you please try AsEnumerable:

await repository.GetDbContext().BulkCopyAsync(new BulkCopyOptions
                {
                    //BulkCopyTimeout = 5000
                }, items.AsEnumerable());

[aloksharma1]

wait a few minutes...

[aloksharma1]

didnt worked, also tried this variation same result:

                using var connection = repository.GetDbContext().CreateLinqToDbConnection();
                using var t = connection.CreateTempTable<Models.RolePermissions>();
                await t.BulkCopyAsync(new BulkCopyOptions
                {
                    //BulkCopyTimeout = 5000
                }, items.AsEnumerable());

following is the stacktrace:

Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, bool breakConnection, Action<Action> wrapCloseInAction)
Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, bool breakConnection, Action<Action> wrapCloseInAction)
Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, bool callerHasConnectionLock, bool asyncClose)
Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, out bool dataReady)
Microsoft.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
Microsoft.Data.SqlClient.SqlBulkCopy.RunParser(BulkCopySimpleResultSet bulkCopyHandler)
Microsoft.Data.SqlClient.SqlBulkCopy.CopyBatchesAsyncContinuedOnSuccess(BulkCopySimpleResultSet internalResults, string updateBulkCommandText, CancellationToken cts, TaskCompletionSource<object> source)
LinqToDB.DataProvider.SqlServer.SqlServerBulkCopy+<>c__DisplayClass10_1<T>+<<ProviderSpecificCopyInternalAsync>b__3>d.MoveNext()
LinqToDB.DataProvider.BasicBulkCopy.TraceActionAsync(DataConnection dataConnection, Func<string> commandText, Func<Task<int>> action)
LinqToDB.DataProvider.SqlServer.SqlServerBulkCopy.ProviderSpecificCopyInternalAsync<T>(ProviderConnections providerConnections, ITable<T> table, BulkCopyOptions options, Func<List<ColumnDescriptor>, BulkCopyReader<T>> createDataReader, CancellationToken cancellationToken)
LinqToDB.EntityFrameworkCore.LinqToDBForEFTools.BulkCopyAsync<T>(DbContext context, BulkCopyOptions options, IEnumerable<T> source, CancellationToken cancellationToken)

[sdanyliv]

Are you trying to insert records from one table to another in the same database?

[aloksharma1]

no just trying to insert a list of model into a target table. merge function works fine but this one is failing with timeout.

[sdanyliv]

Another attempt before we start deeper investigation:

await repository.GetDbContext().BulkCopyAsync(new BulkCopyOptions
                {
                    BulkCopyType = BulkCopyType.MultipleRows
                }, items.AsEnumerable());

[aloksharma1]

this worked, but shouldn't this be default set or atleast give a meaning full warning of whats wrong with config?

[sdanyliv]

It's something strange and I saw that once before. Need to investigate why SQL Server's BULK COPY fails. Anyway post your table definition and model for this table.

[aloksharma1]

here is the model definition:

public class RolePermissions
    {        
        public Guid Id { get; set; } = Guid.NewGuid();
        public string RoleId { get; set; }
        public string PermissionName { get; set; }
        public RoleStatus RoleStatus { get; set; } = RoleStatus.Permitted;
        /// <summary>
        /// null means permanent restriction 
        /// </summary>
        public DateTimeOffset? RestrictionTillDate { get; set; }
    }

the rolePermissions.SelectedRoleActions is just a string[] array to grab selected unique PermissionNames.

[Shane32]

this worked, but shouldn't this be default set or atleast give a meaning full warning of whats wrong with config?

@aloksharma1 I believe that specifying BulkCopyType = BulkCopyType.MultipleRows basically bypasses SQL's native bulk copy, and just issues SQL INSERT statements (in batches for improved performance). So there's some issue with the native SQL bulk copy unique to your model or connection string or something that needs to be diagnosed. Confirm if this is incorrect @sdanyliv

[MaceWindu]

How many records you try to insert? Could be related to batch size, try to specify BulkCopyOptions.BatchSize

[sdanyliv]

@Shane32, that's correct. Unusual situation when BULK COPY won't work.

@aloksharma1, please post also SQL table definition and ConnectionString, with masked sensitive information. Also post result of the following select: SELECT @@VERSION AS 'SQL Server Version';

[aloksharma1]

@sdanyliv here is the sql query version result "Microsoft SQL Server 2017 (RTM-CU23) (KB5000685) - 14.0.3381.3 (X64) Feb 9 2021 12:08:50 Copyright (C) 2017 Microsoft Corporation Express Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)"

@MaceWindu i only tried to insert 2-4 results as i was testing with bulkcopy for first time.

what else do you need?

[MaceWindu]

Well, I'm aware of only one "issue" with bulk copy - it doesn't support table/column names if they doesn't match by case, but in that case it returns error.

It doesn't sound like an issue with linq2db or ef.core to me at all and in this situation I would look at sql server activity to identify why it is locked. Take a look at some useful queries here https://www.mssqltips.com/sqlservertip/2732/different-techniques-to-identify-blocking-in-sql-server/