--------------------------------------------------------------------------------
Hi,

I have a stored procedure in Sybase, in which I have used a cursor. This cursor uses union all operator to fetch data from 2 tables. These 2 tables are almost same in structure.

Inside cursor loop, I update one field (ENT_ID) of both tables using some key fields in where clause.

Now total number of records cursor fetches is 40000. This means update statement is run 40000 times. This takes around 16 minutes to complete which is a lot of time.

Same stored procedure (with same number of records) in SQL Server 2005 takes only 1.5 minutes and in Oracle 10g, it takes around 2 minutes.

I don't understand why it takes so much time in Sybase to complete whereas SQL Server, Oracle do it quickly.

I need to use cursor as I need to update each record with different value. I need to fetch each line from tables.

I ran stored procedure by commenting out UPDATE statements and it completed fast enough. (within few seconds)
This is why I think this poroblem is not related to cursor but to UPDATE.

Following is SQL,

declare @lTokens int
declare cList cursor for
select KEY_GUID, ENT_NAME, 'E' from table1 where WORK_UNIT = @vWorkUnit_ and ENT_NAME not in (select distinct ENT_NAME from table2 where WORK_UNIT = @vWorkUnit_)
union all

select KEY_GUID, ENT_NAME, 'R' from table2 where WORK_UNIT = @vWorkUnit_ order by KEY_GUID
set @lTokens = 1

open cList
fetch cList into @vObjGUID, @vObjName, @vObjType
while (@@sqlstatus = 0)
begin
if upper(@vObjType) = 'E'
begin
update table1 set ENT_ID = @lTokens where WORK_UNIT = @vWorkUnit_ and KEY_GUID = @vObjGUID and ENT_NAME = @vObjName
end
else if upper(@vObjType) = 'R'
begin
update table2 set ENT_ID = @lTokens where WORK_UNIT = @vWorkUnit_ and KEY_GUID = @vObjGUID and ENT_NAME = @vObjName
end
fetch cList into @vObjGUID, @vObjName, @vObjType

end
close cList
deallocate cursor cList

Can somebody help with this? What can be done to make Sybase work faster?

Thanks,
Awadhoot