我使用下面的扩展函数来更新数据与EF Core与EFCore.BulkExtensions
,但问题是这个函数的执行,当我试图插入200万记录是大约17分钟,最近它抛出了这个异常
无法为数据库"tempdb"中的对象"dbo. SORT临时运行存储:140737501921280"分配空间,因为"PRIMARY"文件组已满.通过删除不需要的文件、删除文件组中的对象、向文件组中添加其他文件或为文件组中的现有文件设置自动增长来创建磁盘空间.\ r\n由于"ACTIVE_TRANSACTION",数据库"temPDB"的事务日志(log)已满,并且holdup lsn为(41:136:347)
我可以看到"C"分区的存储量正在减少,当我执行这个函数:
当我重新启动SQL Server时,可用空间变为大约30 GB,我try 使用多线程(并行)与插入没有注意到的时间变化,所以你有什么建议或有任何问题在这里显示的代码.
Note: for循环不会花费太多时间,即使它是200万条记录.
public static async Task<OperationResultDto> AddOrUpdateBulkByTransactionAsync<TEntity>(this DbContext _myDatabaseContext, List<TEntity> data) where TEntity : class
{
using (var transaction = await _myDatabaseContext.Database.BeginTransactionAsync())
{
try
{
_myDatabaseContext.Database.SetCommandTimeout(0);
var currentTime = DateTime.Now;
// Disable change tracking
_myDatabaseContext.ChangeTracker.AutoDetectChangesEnabled = false;
// Set CreatedDate and UpdatedDate for each entity
foreach (var entity in data)
{
var createdDateProperty = entity.GetType().GetProperty("CreatedDate");
if (createdDateProperty != null && (createdDateProperty.GetValue(entity) == null || createdDateProperty.GetValue(entity).Equals(DateTime.MinValue)))
{
// Set CreatedDate only if it's not already set
createdDateProperty.SetValue(entity, currentTime);
}
var updatedDateProperty = entity.GetType().GetProperty("UpdatedDate");
if (updatedDateProperty != null)
{
updatedDateProperty.SetValue(entity, currentTime);
}
}
// Bulk insert or update
var updateByProperties = GetUpdateByProperties<TEntity>();
var bulkConfig = new BulkConfig()
{
UpdateByProperties = updateByProperties,
CalculateStats = true,
SetOutputIdentity = false
};
// Batch size for processing
int batchSize = 50000;
for (int i = 0; i < data.Count; i += batchSize)
{
var batch = data.Skip(i).Take(batchSize).ToList();
await _myDatabaseContext.BulkInsertOrUpdateAsync(batch, bulkConfig);
}
// Commit the transaction if everything succeeds
await transaction.CommitAsync();
return new OperationResultDto
{
OperationResult = bulkConfig.StatsInfo
};
}
catch (Exception ex)
{
// Handle exceptions and roll back the transaction if something goes wrong
transaction.Rollback();
return new OperationResultDto
{
Error = new ErrorDto
{
Details = ex.Message + ex.InnerException?.Message
}
};
}
finally
{
// Re-enable change tracking
_myDatabaseContext.ChangeTracker.AutoDetectChangesEnabled = true;
}
}
}