Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2007
    Posts
    13

    Unanswered: Sybase update takes lot of time

    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.

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

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I have a stored procedure in Sybase, in which I have used a cursor.
    Don't use a cursor - use a single update to process all the records. If you need assistance with this then you'll need to post the SQL. Oracle is quite efficient with cursors and so it doesn't matter if you use them but it's far better to simply avoid them.

  3. #3
    Join Date
    Nov 2007
    Posts
    13
    Thanks for your reply.
    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

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by august97
    Thanks for your reply.
    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.
    The problem is related to you running the update statement 40000 times rather than once. As smaller side issues you are using "not in" which is far slower than "not exists" and you are ordering the data that is coming back from the cursor select even though this is not required. You then join the data from your 2 initial selects but label one set with E and the other with R - then you treat each differently in the cursor! why not just do 2 update statements ie
    Code:
    update table1 
    set ENT_ID = @lTokens 
    where  WORK_UNIT = @vWorkUnit_ 
              and not exists(
                     select 1
                     from table2 t2
                     where t2.WORK_UNIT = @vWorkUnit_
                               and t2.ENT_NAME = table1.ENT_NAME )
        
    update table2 
    set ENT_ID = @lTokens 
    where WORK_UNIT = @vWorkUnit_
    PS please double check this code before trying it - I don't have any way to check that its correct and your original code is rather convoluted. I'd suggest either copy the tables and using those or at least using a transaction. If the value @lTokens (why not @vTokens?) is always 1 then why not just use the value 1.

    Mike

  5. #5
    Join Date
    Nov 2007
    Posts
    13
    Actually I forgot to add one statement, (to increment @lTokens)

    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

    set @lTokens = @lTokens + 1

    fetch cList into @vObjGUID, @vObjName, @vObjType

    end
    close cList
    deallocate cursor cList

    In every iteration I increment @lTokens by 1. This is why I used cursor. I can not use single update statement.
    I also think that lot of time is taken for 40000 updates.

    But same code runs very fast on SQL Server 2005 (1.5 minutes) & Oracle 10g (2 minutes) for same number of records (40000). For Sybase only it takes around 16 minutes.

    Do you think we have any way to reduce this time?

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by august97
    Actually I forgot to add one statement, (to increment @lTokens)
    Use the code previously given but either declare the original table an autoincrement field for the token field. If this is impossible then create a temporary table with an autoincrement field and use this as an intermediary stage. Of course you might also consider dropping the field all together and just using a natural key.

    Quote Originally Posted by august97
    But same code runs very fast on SQL Server 2005 (1.5 minutes) & Oracle 10g (2 minutes) for same number of records (40000). For Sybase only it takes around 16 minutes.

    Do you think we have any way to reduce this time?
    I'd be surprised if it took longer than a couple of seconds if you just stop using the cursor.
    Last edited by mike_bike_kite; 01-17-09 at 07:23.

  7. #7
    Join Date
    Nov 2007
    Posts
    13
    I can not use autoincrement since it will create same numbers for ENT_ID in both the tables. What I mean is, in both tables it will start from 1. I need values for ENT_ID should be unique across both tables.

    Do you think there is any way to make current code work faster for Sybase?

  8. #8
    Join Date
    Nov 2007
    Posts
    13
    If I comment UPDATE statement and just iterate through cursor, it completes in few seconds. If it was problem with cursor, just iterating also should have taken more time.

  9. #9
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    If I comment UPDATE statement and just iterate through cursor, it completes in few seconds. If it was problem with cursor, just iterating also should have taken more time.
    If you comment out all the code you'll find it will run even quicker

    I can not use autoincrement since it will create same numbers for ENT_ID in both the tables. What I mean is, in both tables it will start from 1. I need values for ENT_ID should be unique across both tables.

    Do you think there is any way to make current code work faster for Sybase?
    If it's important to your business for the query to complete in under a few seconds then I suggest you pay a Sybase expert to do this for you. If you pass them the advice I've already supplied then they (or you) should be able to achieve the performance you need.

  10. #10
    Join Date
    Apr 2009
    Posts
    1
    Remove the cursor from proc and create a temp table

    Create table #temp
    (ENT_ID identity
    KEY_GUID,
    ENT_NAME,
    TYPE
    )

    insert into #temp
    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

    for TYPE = 'E'
    update table1set table1.ENT_ID = #temp.ENT_ID from table1, #temp where table1..............


    for TYPE = 'R'
    update table1 set table1.ENT_ID = #temp.ENT_ID where table2...............

    just try this u can see disaster change

  11. #11
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    FWIW. I am a Sybase consultant, a performance expert, whom companies hire and pay to enhance their code. I see exactly this problem all the time. The problem is not in Sybase; not even in the table or the demanded procedure (which is bad anyway); it is in the approach: the developer knows cursors work well in Oracle, and they will simply not listen. Mike is right.

    Oracle is a row-processor, cursors are the "fastest" for this type of "engine".

    Sybase is a set-oriented engine, cursors are guaranteed poor performance. APproach the problem with a set oriented mind. Mike is right again. There are other things you can do, such as processing the two tables separately, and lose the Unions and the need for a next-sequential number, but that is a separate discussion.
    Last edited by Derek Asirvadem; 04-08-09 at 04:29.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  12. #12
    Join Date
    Mar 2007
    Posts
    86

    cursor running slowly

    The posters advising you to use aa set oriented approach are correct.
    If you insist on using curosor, you can minimize the locking behavior by using the "for read only" clause of the ceclare cursor statment .. that will help.. you'd have to benchmark it .. a cursor will never be as fast as a set oriented operation ..btw that is true on MS SQL Server too .. (the cursor behavior on SS05 is different by default than on Sybase).

    SyBooks Online


    Quote Originally Posted by august97
    Actually I forgot to add one statement, (to increment @lTokens)

    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

    set @lTokens = @lTokens + 1

    fetch cList into @vObjGUID, @vObjName, @vObjType

    end
    close cList
    deallocate cursor cList

    In every iteration I increment @lTokens by 1. This is why I used cursor. I can not use single update statement.
    I also think that lot of time is taken for 40000 updates.

    But same code runs very fast on SQL Server 2005 (1.5 minutes) & Oracle 10g (2 minutes) for same number of records (40000). For Sybase only it takes around 16 minutes.

    Do you think we have any way to reduce this time?

Posting Permissions

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