Do We Have to Use a SQL Cursor?

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]		

Get the Names of Stored Procedures that Reference a Given Table in SQL Server

One of my colleagues sent me a very handy bit of code this morning that I thought would be helpful to post.

Below are two options which, when run against the database, should return the names of all stored procedures that reference the named table.

----Option 1
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%tablename%'

----Option 2
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tablename%'

I am told that the functionality of the first option has been verified.

%d bloggers like this: