Bulk Delete and Partitioned table issue.

[AgarwalSuchita]

Hi,

I am not sure if this is an issue but something stopped working and I wanted to check if there is already a know fix.

I am using BulkDelete and BulkInsert operations at various places in the project and it was working fine. Recently, we partitioned one of the SQL table based on it's id column. And after that the BulkDelete operation is always timing out. I tried adding the BatchSize but a smaller batch size works, but even increasing the batch size to 5000 starts timing out again. The project uses EF6 and .net6. and Z.EntityFrmaework.Extension.EFCore 7.102.2.1

//Code that is timing out _pscdRepository.BulkDelete(_pscdRepository.FindBy(x => x.pscdId == run.PSCDId && elementIds.Contains(x.ElementId) && x.IsSystemGenerated, 180));

And the bulk delete code is like this public void BulkDelete(IEnumerable<T> entities) { _entities.Database.SetCommandTimeout(180); _entities.BulkDelete(entities, options => options.BatchSize = 1000); }

Getting this error: Please increase the timeout globally: ctx.Database.SetCommandTimeout(timeoutValue); or BulkOperationManager.BulkOperationBuilder = operation => operation.BatchTimeout = timeoutValue; or by operation: db.BulkSaveChanges(operation => operation.BatchTimeout = timeoutValue); ---> Microsoft.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
---> System.ComponentModel.Win32Exception (258): The wait operation timed out.

Could you please help?

[JonathanMagnan]

Hello @AgarwalSuchita ,

I’ll assume you’re referring to EF Core 6 with Z.EntityFramework.Extensions.EFCore 6.102.2.1 and not 7.102.2.1.

This is not a known issue. I could be wrong, but I believe the issue is more likely related to how the partitioning was done.

Would it be possible for you to create a runnable project that reproduces the issue? It doesn’t have to be your actual project—just a new solution with the minimal code required to reproduce the problem.

If you can’t share your partitioned table, could you at least provide more details on how the partition was set up—such as the table creation with the partition script, number of partitions, number of rows, etc.—so we can try to see if it significantly affects the delete time?

Also, do your table have triggers?

Best Regards,

Jon

[JonathanMagnan]

Hello @AgarwalSuchita,

Since our last conversation, we haven't heard from you.

As previously mentioned, would it be possible to share a runnable project?

Best regards,

Jon

[AgarwalSuchita]

Hi Jon,

Thank you for the response.

We are doing some debugging on our own to see what could be wrong. I will try to share a runnable project in some time, if the debugging doesn't help.

Regards Suchita

[AgarwalSuchita]

Hi Jon,

So, adding few more details to the original post.

• We are using EF Core 7.0.2 and Z.EntityFramework.Extensio.EFCore(7.102.2.1)
• The table looks like below (at the end), with few other columns. We recently added partitioning based on the column PSCId
• We have multiple instances of the same job running, but they are working on different PSCId at a given time.
• After more debugging found that, the actual issue is not timeout. But deadlock happening on this table while BulkDelete.
• I suspect two reasons for it:

1. After partitioning the queries are performing faster, and the deletes are colliding.
2. The query being generated by BulkDelete is not using the partitioned column in where clause, so it is putting a lock on whole table. Query below:

DELETE DestinationTable
FROM [PSCD] AS DestinationTable 
INNER JOIN (SELECT TOP 100 PERCENT * FROM #ZZZProjects_7fec2b0b_504c_41ab_9e6e_4eecca9ce678
WHERE ZZZ_Index >= @IndexStart AND ZZZ_Index <= @IndexEnd ORDER BY ZZZ_Index) AS StagingTable ON DestinationTable.[Id] = StagingTable.[Id]

What are your thoughts on this?

Table script:

CREATE TABLE [dbo].[PSCD](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[PSCId] [int] NOT NULL,
	[ElementId] [int] NOT NULL,
	[StartDateTime] [datetime] NOT NULL,
	[EndDateTime] [datetime] NOT NULL,
	[Value] [decimal](19, 6) NOT NULL,
	[IsSystemGenerated] [bit] NOT NULL,
	[ModuleId] [int] NULL,
 CONSTRAINT [C_PSCId] PRIMARY KEY CLUSTERED 
(
	[PSCId] ASC,
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PS_PSCId]([PSCId])
) ON [PS_PSCId]([PSCId])
GO

[JonathanMagnan]

My first thought when looking at the query and your table is that an index on the Id column might be missing (though perhaps you already have one).

The DELETE statement likely can’t use the clustered index efficiently and ends up scanning all partitions instead. Even if all Id values are within the same partition, SQL Server still needs to scan all of them, as it doesn't know this through the query.

Adding an index should definitely help improve performance.

Best regards,

Jon

[JonathanMagnan]

Hello @AgarwalSuchita,

Since our last conversation, we haven't heard from you.

Let me know if the index solution solved your issue.

Best regards,

Jon

[JonathanMagnan]

Hello @AgarwalSuchita,

A simple reminder that we are here to assist you.

Dont hesitate to contact us if you need anything.

Best regards,

Jon