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?

Your thoughts/ideas will be greatly appreciated.

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

Rachel wrote:
I didnt fully understand your procedure, but is it really necesssary to
use a cursor? Couldnt you do the same thing with a single update stmt?

Next, I would investigate indexes and statistics and eventuallly rebind
the procedure (dont know what dbms you use so I dont know if this is
apropiate in your case)

HTH
/Lennart

[...]

Rachel wrote:
So am I. 8^)

Any reason why you did not mention what DBMS product? Version?
And how about describing the tables involved? at least the columns
involved in your code would help. That would help us know exactly what
you are dealing with, so that we can then help you.

Get rid of the cursor and write it in straight SQL.
Let the database do the work it was designed to do.

This is a full table scan AND a sort. Do you really need the sort
(ORDER BY)??

(Joe, I think we have another winner.) You source table appears to be
an EAV table. Are you sure you really need this structure. It is hard
to work with, including leading to the SQL update you are about to do
below.

So a dynamic UPDATE statement, due to your EAV design of the source
table.

You know, rather than posting all your application code, try to reduce
it to the simplest case that still exhibits the problem. This is
directed somewhat to you, but also to all other new posters..

Well, without knowing your DBMS, I can only see getting rid of the
sorts (ORDER BY) on the cursors. You don't seem to need them, so all
they do is make the database do more useless work.

This looks like conversion code (changing an EAV style table to a
normal Relational table). Without knowing more about your data model, I
cannot think of any other suggestions.

HTH,
Ed

It looks likeyour design is totally wrong. The give-away is the
improper data element names.

Site_Data_Category -- too vague and general; what EXACTLY is a
"data_category" ? I understand particular categories (loan_category,
shop_category, etc.).

Client_Site_Data_Category_Value -- That is just absurd! Value is
vague, but an attribute can be either a category or a value but never
both.

USys_View_Sorted_Client_Site_Data_Category_Hosting -- You never put
"view" or "table" in a data element name (that is how it is stored, NOT
what it is). A VIEW is a table, and tables are never sorted by
definition. Do you know what metadata is and why you never mix it with
data?

Why is everything in your world NVARCHAR(50)? Possible, but **very**
unlikely. Can you tell me about the careful research you did to get
that particular size? Bet there was none whatsoever.

You used a cursor! They are most NEVER needed and you should use
UPDATE/INSERT/DELETE statements instead .

Why did you write

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

instead of using constants? Do you like to allocate redudant local
variables? A more fundamental quesiton is why are you using Boolean
flags in SQL at all? This is not assembly language.

My guess is that you have a screwed up EAV design and are going to run
**at least 3 orders of magnitude slower** than a normalized schema. It
is time to start over and to get some help.