Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2006
    Posts
    115

    Unanswered: performance: cursor vs looping

    any idea about the execution performance when writing a cursor to perform looping or using while looping to get next from a select sql?

  2. #2
    Join Date
    Dec 2006
    Posts
    7
    Pls don't use cursor in Sybase, unless really really required. Performance will be affected very badly. It is very different from Oracle ref-cursor.

    Reason is Sybase does not have row level locking. [atleast till 12.5 I know of]. Meaning it has only page-level-locking. Imagine you are working on a million row table and the data is stored in 1000 pages and you have to update 1000 rows. on worst case scenario, let us imagine each of these rows sits in different pages, then for the period of cursor run, you will be locking the entire table !!.

    Most sybase cursors could be replaced with case statements in sql. looping is not a good idea in Sybase [ unless really really required ]

  3. #3
    Join Date
    Jul 2006
    Posts
    115
    yes, select-case can solve many case if only proceed again within a select query... but many times, we need to extract values from select query one by one.. then cursor or looping cant be prevented.
    OR any alternatives?

  4. #4
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    IN a lot of cases a loop construct can be turned into an appropriate set operation. However, if this isn't possible then I would definitely use a read-only cursor, and not a T-SQL WHILE loop.

    Michael

  5. #5
    Join Date
    Dec 2006
    Posts
    7
    Another alternative I can think of is creating functions. but this java based, and has to be loading into the server by DBA. that is why it is not popular I think.

Posting Permissions

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