Use temp table.

src/Umbraco.Cms.Persistence.SqlServer/Operations/SqlServerPropertyDataReplacerOperation.cs

@@ -0,0 +1,238 @@
+using System.Data;
+using System.Data.Common;
+using Microsoft.Data.SqlClient;
+using Umbraco.Cms.Infrastructure.Persistence;
+using Umbraco.Cms.Infrastructure.Persistence.Dtos;
+using Umbraco.Extensions;
+
+namespace Umbraco.Cms.Persistence.SqlServer.Operations;
+
+/// <summary>
+/// SQL Server implementation of <see cref="IPropertyDataReplacerOperation"/> that uses SqlBulkCopy
+/// with a temp table and MERGE statement for optimized performance.
+/// </summary>
+/// <remarks>
+/// This approach combines:
+/// - SqlBulkCopy for fast data transfer to the server.
+/// - Temp table to stage the data.
+/// - MERGE statement for atomic UPDATE/INSERT/DELETE.
+/// </remarks>
+public class SqlServerPropertyDataReplacerOperation : IPropertyDataReplacerOperation
+{
+    private const string TempTableName = "#umbracoPropertyDataStaging";
+
+    private static readonly string[] _columnNames =
+    [
+        "versionId", "propertyTypeId", "languageId", "segment",
+        "intValue", "decimalValue", "dateValue", "varcharValue", "textValue"
+    ];
+
+    private const string CreateTempTableSql = $"""
+        CREATE TABLE [{TempTableName}] (
+            [versionId] INT NOT NULL,
+            [propertyTypeId] INT NOT NULL,
+            [languageId] INT NULL,
+            [segment] NVARCHAR(256) NULL,
+            [intValue] INT NULL,
+            [decimalValue] DECIMAL(38, 6) NULL,
+            [dateValue] DATETIME NULL,
+            [varcharValue] NVARCHAR(512) NULL,
+            [textValue] NVARCHAR(MAX) NULL
+        );
+        """;
+
+    private const string MergeAndCleanupSql = $"""
+        -- Get distinct versionIds from the staged data.
+        DECLARE @versionIds TABLE (versionId INT PRIMARY KEY);
+        INSERT INTO @versionIds (versionId)
+        SELECT DISTINCT versionId FROM [{TempTableName}];
+
+        -- Lock existing rows for the affected versionIds.
+        SELECT id FROM [umbracoPropertyData] WITH (UPDLOCK, HOLDLOCK)
+        WHERE versionId IN (SELECT versionId FROM @versionIds);
+
+        -- MERGE: UPDATE existing, INSERT new, DELETE removed.
+        MERGE [umbracoPropertyData] AS target
+        USING [{TempTableName}] AS source
+        ON (
+            target.versionId = source.versionId
+            AND target.propertyTypeId = source.propertyTypeId
+            AND (target.languageId = source.languageId OR (target.languageId IS NULL AND source.languageId IS NULL))
+            AND (target.segment = source.segment OR (target.segment IS NULL AND source.segment IS NULL))
+        )
+        WHEN MATCHED THEN
+            UPDATE SET
+                intValue = source.intValue,
+                decimalValue = source.decimalValue,
+                dateValue = source.dateValue,
+                varcharValue = source.varcharValue,
+                textValue = source.textValue
+        WHEN NOT MATCHED BY TARGET THEN
+            INSERT (versionId, propertyTypeId, languageId, segment, intValue, decimalValue, dateValue, varcharValue, textValue)
+            VALUES (source.versionId, source.propertyTypeId, source.languageId, source.segment,
+                    source.intValue, source.decimalValue, source.dateValue, source.varcharValue, source.textValue)
+        WHEN NOT MATCHED BY SOURCE AND target.versionId IN (SELECT versionId FROM @versionIds) THEN
+            DELETE;
+
+        -- Clean up the temp table.
+        DROP TABLE [{TempTableName}];
+        """;
+
+    /// <inheritdoc/>
+    public string? ProviderName => Constants.ProviderName;
+
+    /// <inheritdoc/>
+    public void ReplacePropertyData(IUmbracoDatabase database, int versionId, IEnumerable<PropertyDataDto> propertyDataDtos)
+    {
+        // Get the underlying SqlConnection and transaction.
+        SqlConnection connection = NPocoDatabaseExtensions.GetTypedConnection<SqlConnection>(database.Connection);
+        SqlTransaction? transaction = GetTransaction(database);
+
+        // Step 1: Create the temp table.
+        using (var createCmd = new SqlCommand(CreateTempTableSql, connection, transaction))
+        {
+            createCmd.ExecuteNonQuery();
+        }
+
+        // Step 2: Bulk copy the data into the temp table using SqlBulkCopy.
+        using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
+        {
+            bulkCopy.DestinationTableName = TempTableName;
+            bulkCopy.BulkCopyTimeout = 0; // Use connection timeout
+            bulkCopy.BatchSize = 4096; // Consistent with SqlServerBulkSqlInsertProvider
+
+            // Map columns explicitly by name.
+            foreach (var columnName in _columnNames)
+            {
+                bulkCopy.ColumnMappings.Add(columnName, columnName);
+            }
+
+            using var reader = new PropertyDataDtoDataReader(propertyDataDtos);
+            bulkCopy.WriteToServer(reader);
+        }
+
+        // Step 3: Execute the MERGE statement and clean up.
+        using (var mergeCmd = new SqlCommand(MergeAndCleanupSql, connection, transaction))
+        {
+            mergeCmd.ExecuteNonQuery();
+        }
+    }
+
+    private static SqlTransaction? GetTransaction(IUmbracoDatabase database)
+    {
+        using DbCommand command = database.CreateCommand(database.Connection, CommandType.Text, string.Empty);
+        return command.Transaction != null
+            ? NPocoDatabaseExtensions.GetTypedTransaction<SqlTransaction>(command.Transaction)
+            : null;
+    }
+
+    /// <summary>
+    /// A lightweight IDataReader implementation for streaming PropertyDataDto to SqlBulkCopy.
+    /// </summary>
+    private sealed class PropertyDataDtoDataReader : IDataReader
+    {
+        private readonly IEnumerator<PropertyDataDto> _enumerator;
+        private PropertyDataDto? _current;
+
+        public PropertyDataDtoDataReader(IEnumerable<PropertyDataDto> dtos)
+            => _enumerator = dtos.GetEnumerator();
+
+        public int FieldCount => _columnNames.Length;
+
+        public bool Read()
+        {
+            if (_enumerator.MoveNext())
+            {
+                _current = _enumerator.Current;
+                return true;
+            }
+
+            _current = null;
+            return false;
+        }
+
+        public object GetValue(int i)
+        {
+            if (_current == null)
+            {
+                throw new InvalidOperationException("No current row.");
+            }
+
+            return i switch
+            {
+                0 => _current.VersionId,
+                1 => _current.PropertyTypeId,
+                2 => _current.LanguageId.HasValue ? _current.LanguageId.Value : DBNull.Value,
+                3 => _current.Segment ?? (object)DBNull.Value,
+                4 => _current.IntegerValue.HasValue ? _current.IntegerValue.Value : DBNull.Value,
+                5 => _current.DecimalValue.HasValue ? _current.DecimalValue.Value : DBNull.Value,
+                6 => _current.DateValue.HasValue ? _current.DateValue.Value : DBNull.Value,
+                7 => _current.VarcharValue ?? (object)DBNull.Value,
+                8 => _current.TextValue ?? (object)DBNull.Value,
+                _ => throw new IndexOutOfRangeException($"Column index {i} is out of range."),
+            };
+        }
+
+        public string GetName(int i) => _columnNames[i];
+
+        public int GetOrdinal(string name) => Array.IndexOf(_columnNames, name);
+
+        public void Dispose() => _enumerator.Dispose();
+
+        // Required IDataReader members (minimal implementation for SqlBulkCopy)
+        public void Close() => Dispose();
+
+        public int Depth => 0;
+
+        public bool IsClosed => false;
+
+        public int RecordsAffected => -1;
+
+        public DataTable GetSchemaTable() => throw new NotImplementedException();
+
+        public bool NextResult() => false;
+
+        // IDataRecord members
+        public bool GetBoolean(int i) => throw new NotImplementedException();
+
+        public byte GetByte(int i) => throw new NotImplementedException();
+
+        public long GetBytes(int i, long fieldOffset, byte[]? buffer, int bufferoffset, int length) => throw new NotImplementedException();
+
+        public char GetChar(int i) => throw new NotImplementedException();
+
+        public long GetChars(int i, long fieldoffset, char[]? buffer, int bufferoffset, int length) => throw new NotImplementedException();
+
+        public IDataReader GetData(int i) => throw new NotImplementedException();
+
+        public string GetDataTypeName(int i) => throw new NotImplementedException();
+
+        public DateTime GetDateTime(int i) => throw new NotImplementedException();
+
+        public decimal GetDecimal(int i) => throw new NotImplementedException();
+
+        public double GetDouble(int i) => throw new NotImplementedException();
+
+        public Type GetFieldType(int i) => throw new NotImplementedException();
+
+        public float GetFloat(int i) => throw new NotImplementedException();
+
+        public Guid GetGuid(int i) => throw new NotImplementedException();
+
+        public short GetInt16(int i) => throw new NotImplementedException();
+
+        public int GetInt32(int i) => throw new NotImplementedException();
+
+        public long GetInt64(int i) => throw new NotImplementedException();
+
+        public string GetString(int i) => throw new NotImplementedException();
+
+        public int GetValues(object[] values) => throw new NotImplementedException();
+
+        public bool IsDBNull(int i) => GetValue(i) == DBNull.Value;
+
+        public object this[int i] => GetValue(i);
+
+        public object this[string name] => GetValue(GetOrdinal(name));
+    }
+}

src/Umbraco.Cms.Persistence.SqlServer/UmbracoBuilderExtensions.cs

@@ -8,6 +8,7 @@ using Umbraco.Cms.Core.DistributedLocking;
 using Umbraco.Cms.Infrastructure.Persistence;
 using Umbraco.Cms.Infrastructure.Persistence.SqlSyntax;
 using Umbraco.Cms.Persistence.SqlServer.Interceptors;
+using Umbraco.Cms.Persistence.SqlServer.Operations;
 using Umbraco.Cms.Persistence.SqlServer.Services;
 
 namespace Umbraco.Cms.Persistence.SqlServer;
@@ -44,6 +45,10 @@ public static class UmbracoBuilderExtensions
         builder.Services.TryAddEnumerable(ServiceDescriptor
             .Singleton<IProviderSpecificInterceptor, SqlServerAddRetryPolicyInterceptor>());
 
+        // Optimized database operations using SQL Server specific features.
+        builder.Services.TryAddEnumerable(ServiceDescriptor
+            .Singleton<IPropertyDataReplacerOperation, SqlServerPropertyDataReplacerOperation>());
+
         DbProviderFactories.UnregisterFactory(Constants.ProviderName);
         DbProviderFactories.RegisterFactory(Constants.ProviderName, SqlClientFactory.Instance);
 

src/Umbraco.Infrastructure/DependencyInjection/UmbracoBuilder.Repositories.cs

@@ -3,6 +3,7 @@ using Umbraco.Cms.Core.DependencyInjection;
 using Umbraco.Cms.Core.DynamicRoot.QuerySteps;
 using Umbraco.Cms.Core.Persistence.Repositories;
 using Umbraco.Cms.Core.Services;
+using Umbraco.Cms.Infrastructure.Persistence;
 using Umbraco.Cms.Infrastructure.Persistence.Repositories;
 using Umbraco.Cms.Infrastructure.Persistence.Repositories.Implement;
 using Umbraco.Cms.Infrastructure.Services.Implement;
@@ -20,7 +21,10 @@ public static partial class UmbracoBuilderExtensions
     /// </summary>
     internal static IUmbracoBuilder AddRepositories(this IUmbracoBuilder builder)
     {
-        // repositories
+        // Database provider operation factory - used by repositories for optimized provider-specific operations
+        builder.Services.AddSingleton<IDatabaseProviderOperationFactory, DatabaseProviderOperationFactory>();
+
+        // Repositories.
         builder.Services.AddUnique<IAuditRepository, AuditRepository>();
         builder.Services.AddUnique<IAuditEntryRepository, AuditEntryRepository>();
         builder.Services.AddUnique<ICacheInstructionRepository, CacheInstructionRepository>();

src/Umbraco.Infrastructure/Persistence/DatabaseProviderOperationFactory.cs

@@ -0,0 +1,35 @@
+namespace Umbraco.Cms.Infrastructure.Persistence;
+
+/// <summary>
+/// Implementation of <see cref="IDatabaseProviderOperationFactory"/> that indexes
+/// provider-specific operations by provider name and falls back to default implementations.
+/// </summary>
+internal class DatabaseProviderOperationFactory : IDatabaseProviderOperationFactory
+{
+    private readonly Dictionary<string, IPropertyDataReplacerOperation> _propertyDataReplacers;
+    private readonly IPropertyDataReplacerOperation _defaultPropertyDataReplacerOperation;
+
+    /// <summary>
+    /// Initializes a new instance of the <see cref="DatabaseProviderOperationFactory"/> class.
+    /// </summary>
+    /// <param name="propertyDataReplacers">The collection of provider-specific property data replacers.</param>
+    public DatabaseProviderOperationFactory(IEnumerable<IPropertyDataReplacerOperation> propertyDataReplacers)
+    {
+        _propertyDataReplacers = propertyDataReplacers
+            .Where(x => x.ProviderName is not null)
+            .ToDictionary(x => x.ProviderName!, StringComparer.InvariantCultureIgnoreCase);
+
+        _defaultPropertyDataReplacerOperation = new DefaultPropertyDataReplacerOperation();
+    }
+
+    /// <inheritdoc/>
+    public IPropertyDataReplacerOperation GetPropertyDataReplacerOperation(string providerName)
+    {
+        if (_propertyDataReplacers.TryGetValue(providerName, out IPropertyDataReplacerOperation? operation))
+        {
+            return operation;
+        }
+
+        return _defaultPropertyDataReplacerOperation;
+    }
+}

src/Umbraco.Infrastructure/Persistence/DefaultPropertyDataReplacerOperation.cs

@@ -0,0 +1,69 @@
+using NPoco;
+using Umbraco.Cms.Infrastructure.Persistence.Dtos;
+using Umbraco.Extensions;
+
+namespace Umbraco.Cms.Infrastructure.Persistence;
+
+/// <summary>
+/// Implements the <see cref="IPropertyDataReplacerOperation"/> as a default using database provider agnostic methods.
+/// </summary>
+internal class DefaultPropertyDataReplacerOperation : IPropertyDataReplacerOperation
+{
+    /// <inheritdoc/>
+    public string? ProviderName => null;
+
+    /// <inheritdoc/>
+    public void ReplacePropertyData(IUmbracoDatabase database, int versionId, IEnumerable<PropertyDataDto> propertyDataDtos)
+    {
+        // Replace the property data.
+        // Lookup the data to update with a UPDLOCK (using ForUpdate()) this is because we need to be atomic
+        // and handle DB concurrency. Doing a clear and then re-insert is prone to concurrency issues.
+        Sql<ISqlContext> propDataSql = database.SqlContext.Sql().Select("*").From<PropertyDataDto>().Where<PropertyDataDto>(x => x.VersionId == versionId).ForUpdate();
+        List<PropertyDataDto>? existingPropData = database.Fetch<PropertyDataDto>(propDataSql);
+        var propertyTypeToPropertyData = new Dictionary<(int propertyTypeId, int versionId, int? languageId, string? segment), PropertyDataDto>();
+        var existingPropDataIds = new List<int>();
+        foreach (PropertyDataDto? p in existingPropData)
+        {
+            existingPropDataIds.Add(p.Id);
+            propertyTypeToPropertyData[(p.PropertyTypeId, p.VersionId, p.LanguageId, p.Segment)] = p;
+        }
+
+        var toUpdate = new List<PropertyDataDto>();
+        var toInsert = new List<PropertyDataDto>();
+        foreach (PropertyDataDto propertyDataDto in propertyDataDtos)
+        {
+            // Check if this already exists and update, else insert a new one
+            if (propertyTypeToPropertyData.TryGetValue((propertyDataDto.PropertyTypeId, propertyDataDto.VersionId, propertyDataDto.LanguageId, propertyDataDto.Segment), out PropertyDataDto? propData))
+            {
+                propertyDataDto.Id = propData.Id;
+                toUpdate.Add(propertyDataDto);
+            }
+            else
+            {
+                toInsert.Add(propertyDataDto);
+            }
+
+            // track which ones have been processed
+            existingPropDataIds.Remove(propertyDataDto.Id);
+        }
+
+        if (toUpdate.Count > 0)
+        {
+            var updateBatch = toUpdate
+                .Select(x => UpdateBatch.For(x))
+                .ToList();
+            database.UpdateBatch(updateBatch, new BatchOptions { BatchSize = 100 });
+        }
+
+        if (toInsert.Count > 0)
+        {
+            database.InsertBulk(toInsert);
+        }
+
+        // For any remaining that haven't been processed they need to be deleted
+        if (existingPropDataIds.Count > 0)
+        {
+            database.Execute(database.SqlContext.Sql().Delete<PropertyDataDto>().WhereIn<PropertyDataDto>(x => x.Id, existingPropDataIds));
+        }
+    }
+}

src/Umbraco.Infrastructure/Persistence/Dtos/PropertyDataDto.cs

@@ -8,7 +8,7 @@ namespace Umbraco.Cms.Infrastructure.Persistence.Dtos;
 [TableName(TableName)]
 [PrimaryKey("id")]
 [ExplicitColumns]
-internal sealed class PropertyDataDto
+public sealed class PropertyDataDto
 {
     public const string TableName = Constants.DatabaseSchema.Tables.PropertyData;
     public const int VarcharLength = 512;

src/Umbraco.Infrastructure/Persistence/Dtos/PropertyTypeDto.cs

@@ -8,7 +8,7 @@ namespace Umbraco.Cms.Infrastructure.Persistence.Dtos;
 [TableName(TableName)]
 [PrimaryKey("id")]
 [ExplicitColumns]
-internal class PropertyTypeDto
+public class PropertyTypeDto
 {
     public const string TableName = Constants.DatabaseSchema.Tables.PropertyType;
 

src/Umbraco.Infrastructure/Persistence/IDatabaseProviderOperationFactory.cs

@@ -0,0 +1,14 @@
+namespace Umbraco.Cms.Infrastructure.Persistence;
+
+/// <summary>
+/// Defines a factory for the creation of handlers for specific database operations that have been optimized for a given provider.
+/// </summary>
+public interface IDatabaseProviderOperationFactory
+{
+    /// <summary>
+    /// Retrieves an instance of an <see cref="IPropertyDataReplacerOperation"/> for the specified provider name.
+    /// </summary>
+    /// <param name="providerName">The name of the provider for which to obtain the property data replacer.</param>
+    /// <returns>An <see cref="IPropertyDataReplacerOperation"/> instance associated with the specified provider name.</returns>
+    IPropertyDataReplacerOperation GetPropertyDataReplacerOperation(string providerName);
+}

src/Umbraco.Infrastructure/Persistence/IPropertyDataReplacerOperation.cs

@@ -0,0 +1,33 @@
+using Umbraco.Cms.Infrastructure.Persistence.Dtos;
+
+namespace Umbraco.Cms.Infrastructure.Persistence;
+
+/// <summary>
+/// Provides replacement of property data for content versions.
+/// </summary>
+/// <remarks>
+/// Database-specific implementations can use features like Table-Valued Parameters (SQL Server)
+/// to perform the operation in a single round trip.
+/// </remarks>
+public interface IPropertyDataReplacerOperation
+{
+    /// <summary>
+    /// Gets the database provider name this operation is specific to, or <c>null</c> for the default implementation.
+    /// </summary>
+    string? ProviderName { get; }
+
+    /// <summary>
+    /// Replaces all property data for the specified version IDs atomically.
+    /// </summary>
+    /// <param name="database">The database instance.</param>
+    /// <param name="versionId">The version Id.</param>
+    /// <param name="propertyDataDtos">The property data to save.</param>
+    /// <remarks>
+    /// This method will:
+    /// 1. Lock existing rows for the affected version IDs.
+    /// 2. Update existing property data where keys match (versionId, propertyTypeId, languageId, segment).
+    /// 3. Insert new property data.
+    /// 4. Delete property data that is no longer present.
+    /// </remarks>
+    void ReplacePropertyData(IUmbracoDatabase database, int versionId, IEnumerable<PropertyDataDto> propertyDataDtos);
+}

src/Umbraco.Infrastructure/Persistence/Repositories/Implement/ContentRepositoryBase.cs

@@ -51,7 +51,8 @@ namespace Umbraco.Cms.Infrastructure.Persistence.Repositories.Implement
             IDataTypeService dataTypeService,
             IEventAggregator eventAggregator,
             IRepositoryCacheVersionService repositoryCacheVersionService,
-            ICacheSyncService cacheSyncService)
+            ICacheSyncService cacheSyncService,
+            IDatabaseProviderOperationFactory databaseProviderOperationFactory)
             : base(
                 scopeAccessor,
                 cache,
@@ -66,6 +67,38 @@ namespace Umbraco.Cms.Infrastructure.Persistence.Repositories.Implement
             PropertyEditors = propertyEditors;
             _dataValueReferenceFactories = dataValueReferenceFactories;
             _eventAggregator = eventAggregator;
+            DatabaseProviderOperationFactory = databaseProviderOperationFactory;
+        }
+
+        [Obsolete("Please use the constructor with all parameters. Scheduled for removal in Umbraco 19.")]
+        protected ContentRepositoryBase(
+            IScopeAccessor scopeAccessor,
+            AppCaches cache,
+            ILogger<EntityRepositoryBase<TId, TEntity>> logger,
+            ILanguageRepository languageRepository,
+            IRelationRepository relationRepository,
+            IRelationTypeRepository relationTypeRepository,
+            PropertyEditorCollection propertyEditors,
+            DataValueReferenceFactoryCollection dataValueReferenceFactories,
+            IDataTypeService dataTypeService,
+            IEventAggregator eventAggregator,
+            IRepositoryCacheVersionService repositoryCacheVersionService,
+            ICacheSyncService cacheSyncService)
+            : this(
+                scopeAccessor,
+                cache,
+                logger,
+                languageRepository,
+                relationRepository,
+                relationTypeRepository,
+                propertyEditors,
+                dataValueReferenceFactories,
+                dataTypeService,
+                eventAggregator,
+                repositoryCacheVersionService,
+                cacheSyncService,
+                StaticServiceProvider.Instance.GetRequiredService<IDatabaseProviderOperationFactory>())
+        {
         }
 
         [Obsolete("Please use the constructor with all parameters. Scheduled for removal in Umbraco 18.")]
@@ -114,6 +147,8 @@ namespace Umbraco.Cms.Infrastructure.Persistence.Repositories.Implement
 
         protected PropertyEditorCollection PropertyEditors { get; }
 
+        protected IDatabaseProviderOperationFactory DatabaseProviderOperationFactory { get; }
+
         #region Versions
 
         // gets a specific version
@@ -1130,68 +1165,18 @@ namespace Umbraco.Cms.Infrastructure.Persistence.Repositories.Implement
         }
 
         /// <summary>
-        /// Used to atomically replace the property values for the entity version specified
+        /// Used to atomically replace the property values for the entity version specified.
         /// </summary>
-        /// <param name="entity"></param>
-        /// <param name="versionId"></param>
-        /// <param name="publishedVersionId"></param>
-        /// <param name="edited"></param>
-        /// <param name="editedCultures"></param>
-
         protected void ReplacePropertyValues(TEntity entity, int versionId, int publishedVersionId, out bool edited, out HashSet<string>? editedCultures)
         {
-            // Replace the property data.
-            // Lookup the data to update with a UPDLOCK (using ForUpdate()) this is because we need to be atomic
-            // and handle DB concurrency. Doing a clear and then re-insert is prone to concurrency issues.
-            Sql<ISqlContext> propDataSql = SqlContext.Sql().Select("*").From<PropertyDataDto>().Where<PropertyDataDto>(x => x.VersionId == versionId).ForUpdate();
-            List<PropertyDataDto>? existingPropData = Database.Fetch<PropertyDataDto>(propDataSql);
-            var propertyTypeToPropertyData = new Dictionary<(int propertyTypeId, int versionId, int? languageId, string? segment), PropertyDataDto>();
-            var existingPropDataIds = new List<int>();
-            foreach (PropertyDataDto? p in existingPropData)
-            {
-                existingPropDataIds.Add(p.Id);
-                propertyTypeToPropertyData[(p.PropertyTypeId, p.VersionId, p.LanguageId, p.Segment)] = p;
-            }
-
             IEnumerable<PropertyDataDto> propertyDataDtos = PropertyFactory.BuildDtos(entity.ContentType.Variations, entity.VersionId, publishedVersionId, entity.Properties, LanguageRepository, out edited, out editedCultures);
 
-            var toUpdate = new List<PropertyDataDto>();
-            var toInsert = new List<PropertyDataDto>();
-            foreach (PropertyDataDto propertyDataDto in propertyDataDtos)
-            {
-                // Check if this already exists and update, else insert a new one
-                if (propertyTypeToPropertyData.TryGetValue((propertyDataDto.PropertyTypeId, propertyDataDto.VersionId, propertyDataDto.LanguageId, propertyDataDto.Segment), out PropertyDataDto? propData))
-                {
-                    propertyDataDto.Id = propData.Id;
-                    toUpdate.Add(propertyDataDto);
-                }
-                else
-                {
-                    toInsert.Add(propertyDataDto);
-                }
-
-                // track which ones have been processed
-                existingPropDataIds.Remove(propertyDataDto.Id);
-            }
-
-            if (toUpdate.Count > 0)
-            {
-                var updateBatch = toUpdate
-                    .Select(x => UpdateBatch.For(x))
-                    .ToList();
-                Database.UpdateBatch(updateBatch, new BatchOptions { BatchSize = 100 });
-            }
-
-            if (toInsert.Count > 0)
-            {
-                Database.InsertBulk(toInsert);
-            }
-
-            // For any remaining that haven't been processed they need to be deleted
-            if (existingPropDataIds.Count > 0)
-            {
-                Database.Execute(SqlContext.Sql().Delete<PropertyDataDto>().WhereIn<PropertyDataDto>(x => x.Id, existingPropDataIds));
-            }
+            // Here we'll optimize the operation for SQL Server to help with database latency issues. On large content items with many properties,
+            // replacing property data can be slow due to the multiple round-trips to the database. For SQL Server we have an optimized operation
+            // that uses a TVP (table-valued parameter) to perform the operation in a single round-trip via a stored procedure.
+            var providerName = Database.DatabaseType.GetProviderName();
+            IPropertyDataReplacerOperation propertyDataReplacer = DatabaseProviderOperationFactory.GetPropertyDataReplacerOperation(providerName);
+            propertyDataReplacer.ReplacePropertyData(Database, versionId, propertyDataDtos);
         }
     }
 }

src/Umbraco.Infrastructure/Persistence/Repositories/Implement/DocumentBlueprintRepository.cs

@@ -37,7 +37,10 @@ internal sealed class DocumentBlueprintRepository : DocumentRepository, IDocumen
         IDataTypeService dataTypeService,
         DataValueReferenceFactoryCollection dataValueReferenceFactories,
         IJsonSerializer serializer,
-        IEventAggregator eventAggregator)
+        IEventAggregator eventAggregator,
+        IRepositoryCacheVersionService repositoryCacheVersionService,
+        ICacheSyncService cacheSyncService,
+        IDatabaseProviderOperationFactory databaseProviderOperationFactory)
         : base(
             scopeAccessor,
             appCaches,
@@ -53,7 +56,10 @@ internal sealed class DocumentBlueprintRepository : DocumentRepository, IDocumen
             dataValueReferenceFactories,
             dataTypeService,
             serializer,
-            eventAggregator)
+            eventAggregator,
+            repositoryCacheVersionService,
+            cacheSyncService,
+            databaseProviderOperationFactory)
     {
     }
 

src/Umbraco.Infrastructure/Persistence/Repositories/Implement/DocumentRepository.cs

@@ -59,7 +59,8 @@ public class DocumentRepository : ContentRepositoryBase<int, IContent, DocumentR
         IJsonSerializer serializer,
         IEventAggregator eventAggregator,
         IRepositoryCacheVersionService repositoryCacheVersionService,
-        ICacheSyncService cacheSyncService)
+        ICacheSyncService cacheSyncService,
+        IDatabaseProviderOperationFactory databaseProviderOperationFactory)
         : base(
             scopeAccessor,
             appCaches,
@@ -72,7 +73,8 @@ public class DocumentRepository : ContentRepositoryBase<int, IContent, DocumentR
             dataTypeService,
             eventAggregator,
             repositoryCacheVersionService,
-            cacheSyncService)
+            cacheSyncService,
+            databaseProviderOperationFactory)
     {
         _contentTypeRepository =
             contentTypeRepository ?? throw new ArgumentNullException(nameof(contentTypeRepository));
@@ -93,6 +95,47 @@ public class DocumentRepository : ContentRepositoryBase<int, IContent, DocumentR
             cacheSyncService);
     }
 
+    [Obsolete("Please use the constructor with all parameters. Scheduled for removal in Umbraco 19.")]
+    public DocumentRepository(
+        IScopeAccessor scopeAccessor,
+        AppCaches appCaches,
+        ILogger<DocumentRepository> logger,
+        ILoggerFactory loggerFactory,
+        IContentTypeRepository contentTypeRepository,
+        ITemplateRepository templateRepository,
+        ITagRepository tagRepository,
+        ILanguageRepository languageRepository,
+        IRelationRepository relationRepository,
+        IRelationTypeRepository relationTypeRepository,
+        PropertyEditorCollection propertyEditors,
+        DataValueReferenceFactoryCollection dataValueReferenceFactories,
+        IDataTypeService dataTypeService,
+        IJsonSerializer serializer,
+        IEventAggregator eventAggregator,
+        IRepositoryCacheVersionService repositoryCacheVersionService,
+        ICacheSyncService cacheSyncService)
+        : this(
+            scopeAccessor,
+            appCaches,
+            logger,
+            loggerFactory,
+            contentTypeRepository,
+            templateRepository,
+            tagRepository,
+            languageRepository,
+            relationRepository,
+            relationTypeRepository,
+            propertyEditors,
+            dataValueReferenceFactories,
+            dataTypeService,
+            serializer,
+            eventAggregator,
+            repositoryCacheVersionService,
+            cacheSyncService,
+            StaticServiceProvider.Instance.GetRequiredService<IDatabaseProviderOperationFactory>())
+    {
+    }
+
     [Obsolete("Please use the constructor with all parameters. Scheduled for removal in Umbraco 18.")]
     public DocumentRepository(
         IScopeAccessor scopeAccessor,

src/Umbraco.Infrastructure/Persistence/Repositories/Implement/MediaRepository.cs

@@ -52,7 +52,8 @@ public class MediaRepository : ContentRepositoryBase<int, IMedia, MediaRepositor
         IJsonSerializer serializer,
         IEventAggregator eventAggregator,
         IRepositoryCacheVersionService repositoryCacheVersionService,
-        ICacheSyncService cacheSyncService)
+        ICacheSyncService cacheSyncService,
+        IDatabaseProviderOperationFactory databaseProviderOperationFactory)
         : base(
             scopeAccessor,
             cache,
@@ -65,7 +66,8 @@ public class MediaRepository : ContentRepositoryBase<int, IMedia, MediaRepositor
             dataTypeService,
             eventAggregator,
             repositoryCacheVersionService,
-            cacheSyncService)
+            cacheSyncService,
+            databaseProviderOperationFactory)
     {
         _cache = cache;
         _mediaTypeRepository = mediaTypeRepository ?? throw new ArgumentNullException(nameof(mediaTypeRepository));
@@ -81,6 +83,47 @@ public class MediaRepository : ContentRepositoryBase<int, IMedia, MediaRepositor
             cacheSyncService);
     }
 
+    [Obsolete("Please use the constructor with all parameters. Scheduled for removal in Umbraco 19.")]
+    public MediaRepository(
+        IScopeAccessor scopeAccessor,
+        AppCaches cache,
+        ILogger<MediaRepository> logger,
+        ILoggerFactory loggerFactory,
+        IMediaTypeRepository mediaTypeRepository,
+        ITagRepository tagRepository,
+        ILanguageRepository languageRepository,
+        IRelationRepository relationRepository,
+        IRelationTypeRepository relationTypeRepository,
+        PropertyEditorCollection propertyEditorCollection,
+        MediaUrlGeneratorCollection mediaUrlGenerators,
+        DataValueReferenceFactoryCollection dataValueReferenceFactories,
+        IDataTypeService dataTypeService,
+        IJsonSerializer serializer,
+        IEventAggregator eventAggregator,
+        IRepositoryCacheVersionService repositoryCacheVersionService,
+        ICacheSyncService cacheSyncService)
+        : this(
+            scopeAccessor,
+            cache,
+            logger,
+            loggerFactory,
+            mediaTypeRepository,
+            tagRepository,
+            languageRepository,
+            relationRepository,
+            relationTypeRepository,
+            propertyEditorCollection,
+            mediaUrlGenerators,
+            dataValueReferenceFactories,
+            dataTypeService,
+            serializer,
+            eventAggregator,
+            repositoryCacheVersionService,
+            cacheSyncService,
+            StaticServiceProvider.Instance.GetRequiredService<IDatabaseProviderOperationFactory>())
+    {
+    }
+
     [Obsolete("Please use the constructor with all parameters. Scheduled for removal in Umbraco 18.")]
     public MediaRepository(
         IScopeAccessor scopeAccessor,
@@ -98,7 +141,8 @@ public class MediaRepository : ContentRepositoryBase<int, IMedia, MediaRepositor
         IDataTypeService dataTypeService,
         IJsonSerializer serializer,
         IEventAggregator eventAggregator)
-        : this(scopeAccessor,
+        : this(
+            scopeAccessor,
             cache,
             logger,
             loggerFactory,
@@ -114,8 +158,8 @@ public class MediaRepository : ContentRepositoryBase<int, IMedia, MediaRepositor
             serializer,
             eventAggregator,
             StaticServiceProvider.Instance.GetRequiredService<IRepositoryCacheVersionService>(),
-            StaticServiceProvider.Instance.GetRequiredService<ICacheSyncService>()
-            )
+            StaticServiceProvider.Instance.GetRequiredService<ICacheSyncService>(),
+            StaticServiceProvider.Instance.GetRequiredService<IDatabaseProviderOperationFactory>())
     {
     }
 

src/Umbraco.Infrastructure/Persistence/Repositories/Implement/MemberRepository.cs

@@ -60,7 +60,8 @@ public class MemberRepository : ContentRepositoryBase<int, IMember, MemberReposi
         IEventAggregator eventAggregator,
         IOptions<MemberPasswordConfigurationSettings> passwordConfiguration,
         IRepositoryCacheVersionService repositoryCacheVersionService,
-        ICacheSyncService cacheSyncService)
+        ICacheSyncService cacheSyncService,
+        IDatabaseProviderOperationFactory databaseProviderOperationFactory)
         : base(
             scopeAccessor,
             cache,
@@ -73,7 +74,8 @@ public class MemberRepository : ContentRepositoryBase<int, IMember, MemberReposi
             dataTypeService,
             eventAggregator,
             repositoryCacheVersionService,
-            cacheSyncService)
+            cacheSyncService,
+            databaseProviderOperationFactory)
     {
         _memberTypeRepository =
             memberTypeRepository ?? throw new ArgumentNullException(nameof(memberTypeRepository));
@@ -86,6 +88,49 @@ public class MemberRepository : ContentRepositoryBase<int, IMember, MemberReposi
             new MemberRepositoryUsernameCachePolicy(GlobalIsolatedCache, ScopeAccessor, DefaultOptions, repositoryCacheVersionService, cacheSyncService);
     }
 
+    [Obsolete("Please use the constructor with all parameters. Scheduled for removal in Umbraco 19.")]
+    public MemberRepository(
+        IScopeAccessor scopeAccessor,
+        AppCaches cache,
+        ILogger<MemberRepository> logger,
+        IMemberTypeRepository memberTypeRepository,
+        IMemberGroupRepository memberGroupRepository,
+        ITagRepository tagRepository,
+        ILanguageRepository languageRepository,
+        IRelationRepository relationRepository,
+        IRelationTypeRepository relationTypeRepository,
+        IPasswordHasher passwordHasher,
+        PropertyEditorCollection propertyEditors,
+        DataValueReferenceFactoryCollection dataValueReferenceFactories,
+        IDataTypeService dataTypeService,
+        IJsonSerializer serializer,
+        IEventAggregator eventAggregator,
+        IOptions<MemberPasswordConfigurationSettings> passwordConfiguration,
+        IRepositoryCacheVersionService repositoryCacheVersionService,
+        ICacheSyncService cacheSyncService)
+        : this(
+            scopeAccessor,
+            cache,
+            logger,
+            memberTypeRepository,
+            memberGroupRepository,
+            tagRepository,
+            languageRepository,
+            relationRepository,
+            relationTypeRepository,
+            passwordHasher,
+            propertyEditors,
+            dataValueReferenceFactories,
+            dataTypeService,
+            serializer,
+            eventAggregator,
+            passwordConfiguration,
+            repositoryCacheVersionService,
+            cacheSyncService,
+            StaticServiceProvider.Instance.GetRequiredService<IDatabaseProviderOperationFactory>())
+    {
+    }
+
     [Obsolete("Please use the constructor with all parameters. Scheduled for removal in Umbraco 18.")]
     public MemberRepository(
         IScopeAccessor scopeAccessor,
@@ -122,7 +167,8 @@ public class MemberRepository : ContentRepositoryBase<int, IMember, MemberReposi
             eventAggregator,
             passwordConfiguration,
             StaticServiceProvider.Instance.GetRequiredService<IRepositoryCacheVersionService>(),
-            StaticServiceProvider.Instance.GetRequiredService<ICacheSyncService>())
+            StaticServiceProvider.Instance.GetRequiredService<ICacheSyncService>(),
+            StaticServiceProvider.Instance.GetRequiredService<IDatabaseProviderOperationFactory>())
     {
     }
 

tests/Umbraco.Tests.Integration/Umbraco.Infrastructure/Persistence/Repositories/DocumentRepositoryTest.cs

@@ -1,8 +1,6 @@
 // Copyright (c) Umbraco.
 // See LICENSE for more details.
 
-using System.Collections.Generic;
-using System.Linq;
 using Microsoft.Extensions.DependencyInjection;
 using Microsoft.Extensions.Logging;
 using Microsoft.Extensions.Logging.Abstractions;
@@ -27,7 +25,6 @@ using Umbraco.Cms.Tests.Common.Attributes;
 using Umbraco.Cms.Tests.Common.Builders;
 using Umbraco.Cms.Tests.Common.Testing;
 using Umbraco.Cms.Tests.Integration.Testing;
-using Umbraco.Extensions;
 
 namespace Umbraco.Cms.Tests.Integration.Umbraco.Infrastructure.Persistence.Repositories;
 
@@ -60,6 +57,8 @@ internal sealed class DocumentRepositoryTest : UmbracoIntegrationTest
 
     private IDataTypeService DataTypeService => GetRequiredService<IDataTypeService>();
 
+    private IDatabaseProviderOperationFactory DatabaseProviderOperationFactory => GetRequiredService<IDatabaseProviderOperationFactory>();
+
     private FileSystems FileSystems => GetRequiredService<FileSystems>();
 
     private PropertyEditorCollection PropertyEditorCollection => GetRequiredService<PropertyEditorCollection>();
@@ -158,7 +157,8 @@ internal sealed class DocumentRepositoryTest : UmbracoIntegrationTest
             ConfigurationEditorJsonSerializer,
             Mock.Of<IEventAggregator>(),
             Mock.Of<IRepositoryCacheVersionService>(),
-            Mock.Of<ICacheSyncService>());
+            Mock.Of<ICacheSyncService>(),
+            DatabaseProviderOperationFactory);
         return repository;
     }
 

tests/Umbraco.Tests.Integration/Umbraco.Infrastructure/Persistence/Repositories/MediaRepositoryTest.cs

@@ -1,14 +1,12 @@
 // Copyright (c) Umbraco.
 // See LICENSE for more details.
 
-using System.Linq;
 using Microsoft.Extensions.Logging;
 using Microsoft.Extensions.Logging.Abstractions;
 using Moq;
 using NUnit.Framework;
 using Umbraco.Cms.Core;
 using Umbraco.Cms.Core.Cache;
-using Umbraco.Cms.Core.Configuration.Models;
 using Umbraco.Cms.Core.Events;
 using Umbraco.Cms.Core.Models;
 using Umbraco.Cms.Core.Persistence;
@@ -16,6 +14,7 @@ using Umbraco.Cms.Core.Persistence.Repositories;
 using Umbraco.Cms.Core.PropertyEditors;
 using Umbraco.Cms.Core.Serialization;
 using Umbraco.Cms.Core.Services;
+using Umbraco.Cms.Infrastructure.Persistence;
 using Umbraco.Cms.Infrastructure.Persistence.Dtos;
 using Umbraco.Cms.Infrastructure.Persistence.Repositories.Implement;
 using Umbraco.Cms.Infrastructure.Scoping;
@@ -42,6 +41,8 @@ internal sealed class MediaRepositoryTest : UmbracoIntegrationTest
 
     private IJsonSerializer JsonSerializer => GetRequiredService<IJsonSerializer>();
 
+    private IDatabaseProviderOperationFactory DatabaseProviderOperationFactory => GetRequiredService<IDatabaseProviderOperationFactory>();
+
     // Makes handing IDs easier, these are set by CreateTestData
     private Media _testFolder;
     private Media _testImage;
@@ -82,7 +83,8 @@ internal sealed class MediaRepositoryTest : UmbracoIntegrationTest
             JsonSerializer,
             Mock.Of<IEventAggregator>(),
             Mock.Of<IRepositoryCacheVersionService>(),
-            Mock.Of<ICacheSyncService>());
+            Mock.Of<ICacheSyncService>(),
+            DatabaseProviderOperationFactory);
         return repository;
     }
 

tests/Umbraco.Tests.Integration/Umbraco.Infrastructure/Services/ContentPublishingServiceTests.Publish.cs

@@ -1,10 +1,11 @@
+using System.Text;
 using NUnit.Framework;
 using Umbraco.Cms.Core;
 using Umbraco.Cms.Core.Models;
 using Umbraco.Cms.Core.Models.ContentPublishing;
-using Umbraco.Cms.Core.Models.Membership;
 using Umbraco.Cms.Core.Services;
 using Umbraco.Cms.Core.Services.OperationStatus;
+using Umbraco.Cms.Infrastructure.Persistence.Dtos;
 using Umbraco.Cms.Tests.Common.Builders;
 using Umbraco.Cms.Tests.Integration.Attributes;
 
@@ -24,6 +25,50 @@ public partial class ContentPublishingServiceTests
         VerifyIsPublished(Textpage.Key);
     }
 
+    [Test]
+    public async Task Can_Publish_Root_And_Persist_Expected_Property_Data()
+    {
+        // Act: Publish the root content item.
+        var publishResult = await ContentPublishingService.PublishAsync(Textpage.Key, [new CulturePublishScheduleModel()], Constants.Security.SuperUserKey);
+        Assert.IsTrue(publishResult.Success);
+
+        // Assert the actual values in the umbracoPropertyData table (this is primarily as a regression protection against failures coming from optimizations in the publishing code).
+        var propertyData = GetSerializedPropertyData(2, 6);
+        Assert.AreEqual("1,1,52,Welcome to our Home page|2,1,53,This is the welcome message on the first page|3,1,54,John Doe|16,6,52,Welcome to our Home page|17,6,53,This is the welcome message on the first page|18,6,54,John Doe|", propertyData);
+
+        // Act: Edit and publish the root content item a second time.
+        var textPage = ContentService.GetById(Textpage.Key)!;
+        textPage.SetValue("bodyText", "This is the updated welcome message on the first page");
+        textPage.SetValue("author", null);
+        ContentService.Save(textPage);
+        publishResult = await ContentPublishingService.PublishAsync(Textpage.Key, [new CulturePublishScheduleModel()], Constants.Security.SuperUserKey);
+        Assert.IsTrue(publishResult.Success);
+
+        // Assert the actual values in the umbracoPropertyData table.
+        propertyData = GetSerializedPropertyData(3, 7);
+        Assert.AreEqual("1,1,52,Welcome to our Home page|2,1,53,This is the welcome message on the first page|3,1,54,John Doe|16,6,52,Welcome to our Home page|17,6,53,This is the updated welcome message on the first page|19,7,52,Welcome to our Home page|20,7,53,This is the updated welcome message on the first page|", propertyData);
+    }
+
+    private string GetSerializedPropertyData(int expectedNumberOfContentVersionRecords, int expectedNumberOfPropertyDataRecords)
+    {
+        using var scope = ScopeProvider.CreateScope();
+        var contentVersionIds = scope.Database.Fetch<ContentVersionDto>().Where(x => x.NodeId == Textpage.Id).Select(x => x.Id).ToList();
+        Assert.AreEqual(expectedNumberOfContentVersionRecords, contentVersionIds.Count);
+
+        var propertyDataDtos = scope.Database.Fetch<PropertyDataDto>().Where(x => contentVersionIds.Contains(x.VersionId)).ToList();
+        Assert.AreEqual(expectedNumberOfPropertyDataRecords, propertyDataDtos.Count);
+
+        var sb = new StringBuilder();
+        foreach (var propertyDataDto in propertyDataDtos.OrderBy(x => x.VersionId).ThenBy(x => x.PropertyTypeId))
+        {
+            sb.AppendFormat("{0},{1},{2},{3}|", propertyDataDto.Id, propertyDataDto.VersionId, propertyDataDto.PropertyTypeId, propertyDataDto.TextValue ?? propertyDataDto.VarcharValue);
+        }
+
+        scope.Complete();
+
+        return sb.ToString();
+    }
+
     [Test]
     public async Task Publish_Single_Item_Does_Not_Publish_Children()
     {