In migrating data from a legacy budget application to its replacement, my requirement was to take the first and last years in a budget from the old application and update the corresponding table in the “new” app table with those dates as the beginning and ending of the project. One possible solution is to use a SQL cursor to loop through each record. When there are relatively few records, and the databases/tables are in the same location, this is an easy choice as the performance hit will be minimal.
However, in my case, there were 10000 records and the servers were on separate LANs. The cursor below did produce the desired result in terms of data, but it was horribly slow. It would have taken hours to completely copy all of the data, given the locations of the servers and possibly not the best indexing on the databases. (I didn’t design the indices! 😀 )
DECLARE WidgetBudgetCursor CURSOR
FOR SELECT [PSProjectID],[WidgetID] FROM [LinkedServername].[DbName].[Owner].[TableName]
OPEN WidgetBudgetCursor
FETCH NEXT FROM WidgetBudgetCursor INTO @ProjectID, @WidgetID
WHILE @@FETCH_STATUS = 0
BEGIN
-- ...
DECLARE @BudgetYearMin INT
DECLARE @BudgetYearMax INT
SELECT @BudgetYearMin=MIN([BudgetYear]), @BudgetYearMax=MAX([BudgetYear]) FROM [tWidgetbudgetbreakdown]
WHERE [RequestId] = @WidgetID
UPDATE [LinkedServername].[DbName].[Owner].[TableName]
SET [ProjectStartDate] = CONVERT(DATETIME, '01-01-' + CAST(@BudgetYearMin AS CHAR(4)) + ' 00:00:00')
WHERE [ProjectID] = @ProjectID
UPDATE [LinkedServername].[DbName].[Owner].[TableName]
SET [InServiceDate] = CONVERT(DATETIME, '12-31-' + CAST(@BudgetYearMax AS CHAR(4)) + ' 23:59:59')
WHERE [ProjectID] = @ProjectID
-- ...
FETCH NEXT FROM WidgetBudgetCursor INTO @ProjectID, @WidgetID
END
CLOSE WidgetBudgetCursor
DEALLOCATE WidgetBudgetCursor
After some initial attempts to copy small portions of the data took much longer than I expected it to, I redesigned the queries to be updates on joined tables (see below). This was much faster than using the cursor. While I was not able to eliminate the cursor for some other required functions, moving these update queries out of the cursor made the data move use less resources and take much less time.
UPDATE pm2
SET [ProjectStartDate] =
a.MinBudgetYear FROM
(SELECT CONVERT(DATETIME, '01-01-' + CAST(MIN(Widget.[BudgetYear]) AS CHAR(4)) + ' 00:00:00') AS MinBudgetYear, pm.[WidgetID] AS WidgetID
FROM [LinkedServername].[DbName].[Owner].[TableName] pm INNER JOIN
[tWidgetbudgetbreakdown] Widget ON pm.[WidgetID] = Widget.[RequestID] GROUP BY pm.[WidgetID] HAVING LEN(MIN(Widget.[BudgetYear]))=4) a INNER JOIN
[LinkedServername].[DbName].[Owner].[TableName] pm2 ON a.[WidgetID] = pm2.[WidgetID]
UPDATE pm2
SET [InServiceDate] =
a.MaxBudgetYear FROM
(SELECT CONVERT(DATETIME, '12-31-' + CAST(MAX(Widget.[BudgetYear]) AS CHAR(4)) + ' 23:59:59') AS MaxBudgetYear, pm.[WidgetID] AS WidgetID
FROM [LinkedServername].[DbName].[Owner].[TableName] pm INNER JOIN
[tWidgetbudgetbreakdown] Widget ON pm.[WidgetID] = Widget.[RequestID] GROUP BY pm.[WidgetID] HAVING LEN(MAX(Widget.[BudgetYear]))=4) a INNER JOIN
[LinkedServername].[DbName].[Owner].[TableName] pm2 ON a.[WidgetID] = pm2.[WidgetID]