Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    184

    Unanswered: Cursors in Stored Procedures

    What is the impact of using cursors in stored procedures? Is it good to use cursors (or) good to avoid using cursors in Stored Procedures?

    Please eloborate.

    TIA

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Cursors are the only construct that allow you to manipulate multiple rows of data within a Stored Procedure. You can do without them if:

    1) you do not return any results sets
    2) you only select one row of values.

    You have to use cursors to return result sets. You need them if any select returns anything more than a single row.

    HTH

    Andy

  3. #3
    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    184
    I came across the following recently, which has raised my doubts about cursors in stored procedures...


    Cursors use memory, lock tables and are slow. Using cursors defeat performance optimization. Suppose the cursor has 10,000 records, it will execute about 10,000 SELECTs! We can do this in a couple of SELECT, UPDATE or DELETE, it will be much faster.
    Do give your input...

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Where did you get this? What is its context?


    Cursors use memory - so. what else does not?
    Cursors Lock tables - only if they are designated to (e.g. for update)
    Cursors are slow - define slow, I use cursors in SP all the time, they seem pretty fast to me. Very acceptable response times.
    Using Cursors defeat performance optimization - What is meant here?
    The last statement seems a bit far fetched.

    How can a SP return a result set without use of a cursor?
    If you have business logic that needs to be performed on multiple rows of a table, how do you do that without cursors.

    Andy

    Originally posted by ggnanaraj
    I came across the following recently, which has raised my doubts about cursors in stored procedures...



    Do give your input...

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Originally posted by ggnanaraj
    I came across the following recently, which has raised my doubts about cursors in stored procedures...


    Cursors use memory, lock tables and are slow. Using cursors defeat performance optimization. Suppose the cursor has 10,000 records, it will execute about 10,000 SELECTs! We can do this in a couple of SELECT, UPDATE or DELETE, it will be much faster.

    Do give your input...
    I agree with almost everything what ARWinner says, exept one thing. That statement about "10000" SELECTs is outright incorrect. SELECT is executed once when you open a cursor; after that a FETCH (from the result set) is used to obtain data from the current row.

    Unless, of course, the piece you quote does not refer to DB2 cursors but rather to some other cursors, e.g. some kind of VB construct...
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    When using cursors, there is the issue of “cursor materialization.” This is the situation where all the rows in the answer set must be read by DB2 and placed in a temporary table (which you cannot see) before DB2 can return the first row as a result of the first fetch. However, cursor materialization is not an issue specific to stored procedures.

    Cursor materialization can be caused by several things including the use of an ORDER BY in the cursor that causes a sort of the rows. But many times an index can be used by DB2 to order the rows without a sort, even when an ORDER BY is request.

    When declaring a cursor, one should consider the use of the following options as appropriate:

    WITH HOLD
    WITH RETURN
    FOR READ ONLY or FOR UPDATE
    OPTIMIZE FOR n ROWS

  7. #7
    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    184
    Originally posted by n_i
    I agree with almost everything what ARWinner says, exept one thing. That statement about "10000" SELECTs is outright incorrect. SELECT is executed once when you open a cursor; after that a FETCH (from the result set) is used to obtain data from the current row.

    Unless, of course, the piece you quote does not refer to DB2 cursors but rather to some other cursors, e.g. some kind of VB construct... [/SIZE]
    My quote is with reference to DB2 cursors only. So, does that mean that '10000' SELECTs are issued, instead of 1 SELECT and then FETCH?

    TIA.
    Last edited by ggnanaraj; 10-03-03 at 02:12.

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Originally posted by ggnanaraj
    My quote is with reference to DB2 cursors only. So, does that mean that '10000' SELECTs are issued, instead of 1 SELECT and then FETCH?

    TIA.
    I'll quote myself, if you don't mind

    "SELECT is executed once when you open a cursor; after that a FETCH (from the result set) is used to obtain data from the current row".
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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