SEO

Hi,

My code is running a bit slow.

I'm using a cursor to perform updates based on an ID. Any thoughts on
ways of optimising?

DECLARE Data_Category_Cursor CURSOR FOR
SELECT Client_Site_ID, Site_Data_Category,
Client_Site_Data_Category_Value
FROM USys_View_Sorted_Client_Site_Data_Category_Hosting
ORDER BY Client_Site_ID


DECLARE @Site_ID NVARCHAR(50)

DECLARE @Data_Category_Value VARCHAR(50)
DECLARE @Data_Category VARCHAR(50)

OPEN Data_Category_Cursor

FETCH NEXT FROM Data_Category_Cursor
INTO @Site_ID, @Site_Data_Category, @Data_Category_Value

WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = N'UPDATE USys_tbl_Temp_Site_Output_SQL SET ' +
@Site_Data_Category + ' = ''' + @Data_Category_Value + '''' + ' WHERE
Client_Site_ID = ''' + @Site_ID + '''' +';'

EXEC @ErrorCode = sp_executesql @SQL

FETCH NEXT FROM Data_Category_Cursor
INTO @Site_ID, @Site_Data_Category, @Data_Category_Value

END

Close Data_Category_Cursor
DEALLOCATE Data_Category_Cursor

DECLARE BU_Table_cursor CURSOR FOR
SELECT Client_Business_Unit FROM
USys_View_Sorted_Client_Business_Unit_Not_NA_Displ ay
ORDER BY Display_Order

DECLARE @BU_Field NVARCHAR(75),
@BU_Value_Yes NVARCHAR(3),
@BU_Value_No NVARCHAR(3)

SET @BU_Value_Yes = 'Yes'
SET @BU_Value_No = 'No'

OPEN BU_Table_cursor

FETCH NEXT FROM BU_Table_cursor
INTO @BU_Field

WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQL = N'UPDATE USys_tbl_Temp_Site_Output_SQL SET ' + @BU_Field +
' = ''' + @BU_Value_No + '''' + ';'

EXEC @ErrorCode = sp_executesql @SQL

FETCH NEXT FROM BU_Table_cursor
INTO @BU_Field
END

CLOSE BU_Table_cursor
DEALLOCATE BU_Table_cursor

DECLARE @BU_Value NVARCHAR(3)

DECLARE BU_Data_Cursor CURSOR FOR
SELECT Client_Site_ID, Client_Business_Unit
FROM USys_View_Sorted_Client_Site_Business_Unit
ORDER BY Client_Site_ID

OPEN BU_Data_Cursor

FETCH NEXT FROM BU_Data_Cursor
INTO @Site_ID, @BU_Field

WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = N'UPDATE USys_tbl_Temp_Site_Output_SQL SET ' + @BU_Field +
' = ''' + @BU_Value_Yes + '''' + ' WHERE Client_Site_ID = ''' +
@Site_ID + '''' +';'

EXEC @ErrorCode = sp_executesql @SQL

FETCH NEXT FROM BU_Data_Cursor
INTO @Site_ID, @BU_Field

END

Close BU_Data_Cursor
DEALLOCATE BU_Data_Cursor

Many Thanks

Flick