And NPM completed succesfully as well :)

@atripp Success!

I'm now migrating the NPM feed which I expect will work just as well.

Ended up getting rid of the MERGE statement and replacing it with a simple UPDATE & INSERT:

USE [ProGet]
GO
/****** Object:  StoredProcedure [dbo].[FeedPackages_UpdatePackageData]    Script Date: 25-07-2023 11:43:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[FeedPackages_UpdatePackageData]
(
	@Feed_Id INT,
	@Packages_Table dbo.[FeedPackageEntry] READONLY,
	@DeleteMissing_Indicator YNINDICATOR = 'N'
)
AS
BEGIN

DECLARE @queryStart AS VARCHAR(max) = CONVERT(varchar, SYSDATETIME(), 121);
PRINT @queryStart;
BEGIN TRY
	BEGIN TRANSACTION;

	SELECT [PackageName_Id] INTO #existingPkgIds FROM [FeedPackages] WHERE [Feed_Id] = @Feed_Id;

	PRINT 'update';
	UPDATE [FeedPackages]
	SET [FeedPackages].[Latest_Package_Version] = old.[Latest_Package_Version],
		[FeedPackages].[LatestStable_Package_Version] = old.[LatestStable_Package_Version] FROM @Packages_Table old WHERE [FeedPackages].PackageName_Id = old.PackageName_Id;

	PRINT 'insert'
	INSERT INTO [FeedPackages] SELECT @Feed_Id,
			[PackageName_Id],
			[Total_Download_Count],
			[Latest_Package_Version],
			[LatestStable_Package_Version] FROM @Packages_Table old
			WHERE [PackageName_Id] NOT IN (SELECT [PackageName_Id] FROM #existingPkgIds);
			-- insert all new packages not already in table

	COMMIT
END TRY BEGIN CATCH
	IF XACT_STATE()<>0 ROLLBACK
	EXEC [HandleError]
END CATCH
DECLARE @queryEnd AS VARCHAR(max) = CONVERT(varchar, SYSDATETIME(), 121);
PRINT @queryEnd;
END

My SQL skills are a bit rusty but I make do.
This runs on my desktop in about 3 seconds.

Note that this does not account for updating download statistics (I don't really care about those) or deleting orphaned packages, but I figure that's easy enough to add back later. I removed those from the MERGE earlier today and it didn't have any impact so I don't think the issue lies there.

I compared the resulting table from this SP with the resulting table of the original SP and they are identical, I'm going to replace the SP on the server with this and see how it goes!

@atripp Yup, we get 1658 insert statements into @p2 that are then passed onto the SP.

@atripp

The recovery model was set to FULL.. I just changed it to SIMPLE and started a new run, but it doesn't really seem to have any impact, oh well.

As for my profiling:

FeedPackages_UpdatePackageData ended up timing out right at the 10 minute mark, as expected. I did however pull the entire @p2 temporary table from the profiler and used it to run some local tests.

Running the SP on my much beefier Windows 11 Pro desktop on SQL Server 2022, using a database backup from last night, stills ends up with an execution time of a solid 2 minutes, so I'm starting to suspect it's something in the database itself. The columns seem properly indexed though, so that's kinda odd..

I'll try some adjustments to the SP and see if I can figure something out.

Hi @atripp

I'm profiling the migration right now to see if I can figure out what it's timing out on. Meanwhile, I noticed that the SP Executions_AddLogEntry is rather slow, between 10ms and 130ms. Is this expected or should I just empty part of my ScopedExecutionLogEntries table, since I have logs going back to 2018?

I'll post again once the migration has failed again with my findings.

We recently upgraded to ProGet Version 2023.13 (Build 14) but are having issues migrating out NPM and Nuget feed. It will iterate through all ~16000 versions but always ends up with a timeout:

DEBUG: Read 16196 rows into 1658 packages.
ERROR: Error importing data: 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.
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__209_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---
   at Inedo.Data.DatabaseContext.DbResult.CreateAsync(DbCommand command, DatabaseContext context, DateTimeOffset startTime, Stopwatch stopwatch)
   at Inedo.Data.DatabaseContext.ExecuteInternalAsync(String storedProcName, GenericDbParameter[] parameters)
   at Inedo.Data.SqlServerDatabaseContext.ExecuteInternalAsync(String storedProcName, GenericDbParameter[] parameters)
   at Inedo.Data.DatabaseContext.ExecuteNonQueryAsync(String storedProcName, GenericDbParameter[] parameters)
   at Inedo.ProGet.Feeds.StandardPackageFeed`3.ImportLegacyPackageDataAsync[TOldRow](ImportLegacyPackageDataContext context, IEnumerable`1 oldRows, Func`2 importAsync, CancellationToken cancellationToken)
   at Inedo.ProGet.Feeds.Npm.NpmFeed.Inedo.ProGet.Feeds.ILegacyImportableFeed.ImportLegacyPackageDataAsync(ImportLegacyPackageDataContext context, CancellationToken cancellationToken)
   at Inedo.ProGet.Executions.MigrateFeedsForProGet23Execution.ExecuteAsync(IManualExecutionContext context)
ClientConnectionId:739689f3-4403-494f-ac1d-d343740bf4ee
Error Number:-2,State:0,Class:11

Our server is running Windows Server 2016 with SQL Server 2016 (13.0.4604.0).

I've already tried the following:

• restarting the service
• restarting the server
• restarting SQL server
• clearing the feed's cache (which by the way didn't really do anything)
• set the SQL server's timeout to 1200 seconds (from the default 600)

I'm not sure what the query itself is trying to do, I feel like reading/inserting 16k rows should not be such a heavy operation.

Any help?