Results 1 to 2 of 2

Thread: Sybase update takes lot of time

  1. #1
    Join Date
    Dec 2007

    Sybase update takes lot of time


    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)
    if upper(@vObjType) = 'E'
    update table1 set ENT_ID = @lTokens where WORK_UNIT = @vWorkUnit_ and KEY_GUID = @vObjGUID and ENT_NAME = @vObjName
    else if upper(@vObjType) = 'R'
    update table2 set ENT_ID = @lTokens where WORK_UNIT = @vWorkUnit_ and KEY_GUID = @vObjGUID and ENT_NAME = @vObjName
    fetch cList into @vObjGUID, @vObjName, @vObjType

    close cList
    deallocate cursor cList

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


  2. #2
    Join Date
    Dec 2007
    Don't use a cursor
    Your code set @lTokens = 1 for all

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts