Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746

    Unanswered: Not true for "us", right?

    Google stumbeled upon this SQL Server Best Practices and a lot of those points are true for all databases.
    But point #2 caught my eye.
    As a former mainframe programmer I got very used to cursors and I always thought they were the "way to go".

    Is this evil-cursor thing is typical for MS-SQL (sybase)?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Well I think avoiding cursor is still a good recommendation, because I have seen a lot of programs doing something like this
    (pseudo-code)
    Code:
    OPEN CURSOR FOR SELECT ...
    FOR EACH ROW IN CURSOR 
       UPDATE some_table
          WHERE <information_from_row_in_cursor>
    END
    This is going to be slow on every DBMS, and can simply be replaced with a single UPDATE statement.
    The above approach is typical for programmers that do not have experience with relational databases or are being used to processing text files only.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I saw the neccesity of cursor only in external procedures or host language programs which process external(other than DB2 tables) resoruces(typically OS files) with DB2 table(s).
    In other words, all cursors I saw in SQL procedure could be rewritten without using cursor.
    Here are more discussions including advantage of using cursor(not my opinion).
    http://www.dbforums.com/db2/1657406-...procedure.html

  4. #4
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    Tools are tools. Each tool has advantages and disadvantages. Choose the right tool for each task. Cursors are not the perfect solution for all tasks, but imo, they remain a valuable tool. One of the main advantages is the ability to control the elapsed time between commits. Commit frequency should be controlled by elapsed time, not by number of records. The time a DBMS uses to process a fixed number of records can vary greatly even within a single run unit, so utilizing elapsed time should be the preferred approach. I'm not stating that we shouldn't utilize other approaches, I'm simply stating that there are no one size fits all solutions and for the money, cursors still deliver value. They may not be the fastest car on the lot, but sometimes we still buy the slower car because that's what we can afford and we know we can drive it in the snow.

  5. #5
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    What is the exact definition of a CURSOR? Coding SP's or cobol-with-inline-SQL is clear, but when your code java/jdbc or perl/dbi with structures like:
    - prepare
    - execute
    - fetch multiple times
    - close

    it looks like a cursor, it smells like a cursor, I think it is a cursor, but hey: who cares
    But IS it considered a cursor??

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Yes, that works with a cursor. However the rules change quite a bit when you take processing away from the back end and introduce transport concerns to the mix.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Jan 2011
    Posts
    14
    I avoid using cursors whenever I can, simply because they are slow, however sometimes there's just no way around using them

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    It may come as a surprise to some, but any select statement results in a cursor being created, so trying to avoid cursors is akin to avoiding reading data from the database. I think what is meant by these so called best practices is this: if something can be done as a set operation (e.g. UPDATE MYTABLE SET ... WHERE CRITERIA = SOMETHING), do not use a cursor loop to accomplish this.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by n_i View Post
    It may come as a surprise to some, but any select statement results in a cursor being created, so trying to avoid cursors is akin to avoiding reading data from the database.
    This is certainly true in DB2, but it isn't true in all database products or even all variations of SQL.

    It is dangerous to make sweeping generalizations about a specific database vendor's products. Lots of things may apply to UDB but not to z/OS or even the AS/400 (and vice versa) and that's just within DB2!

    Extending those generalizations to "all SQL implementations" or worse yet to "all databases" gets crazy fast! The platforms and the engine implementaitons bring so many variations to the table that it is almost impossible to make meaningful comparisons much less general statements.

    At least within SQL implementations, I've found set based operations to be faster than equivalent cursor based opeartions. You give up some control (such as determining when to COMMIT), but in my eyes that is rarely if ever a drawback.

    In other database organizations (OOP based NO-SQL implementations) actually perform better using object marshalling (effectively using a local iterator to perform the same type of process as a SQL cursor) because you acheive better parallelism and can also avoid or at least minimize the net and wrapper overhead.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Obviously, if you executing SQL remotely and using a cursor to update one row at a time, performance is going to be worse than a single SQL update. If the SQL is within a stored procedure and is running Not Fenced mode (standard for SQL language SP's) then there is not going to be much difference.

    One of the main advantage of cursors is that it enables higher concurrency since you can keep a small number of rows locked at one time (by doing intermediate commits after a certain number of updates. In this case, the cursor must be defined WITH HOLD so it will not be closed at commit time.

    High concurrency is an often-neglected issue in today's world of mediocre programming skills. In some applications it is critical. You can get around the locking issues of a single SQL statement to some degree by using Oracle or for DB2 by using cur_commit = ON in the DB CFG (9.7+ only). But these solutions make the application run slower than would otherwise be the case if they didn't have search the logs for the previously committed value to avoid locks. The bottom line is that "speed" is not as simple as it looks on first glance, since locking issues can be one the biggest performance problems encountered.
    Last edited by Marcus_A; 01-14-11 at 18:58.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Pat Phelan View Post
    This is certainly true in DB2, but it isn't true in all database products or even all variations of SQL.

    It is dangerous to make sweeping generalizations about a specific database vendor's products. Lots of things may apply to UDB but not to z/OS or even the AS/400 (and vice versa) and that's just within DB2!

    Extending those generalizations to "all SQL implementations" or worse yet to "all databases" gets crazy fast! The platforms and the engine implementaitons bring so many variations to the table that it is almost impossible to make meaningful comparisons much less general statements.

    At least within SQL implementations, I've found set based operations to be faster than equivalent cursor based opeartions. You give up some control (such as determining when to COMMIT), but in my eyes that is rarely if ever a drawback.

    In other database organizations (OOP based NO-SQL implementations) actually perform better using object marshalling (effectively using a local iterator to perform the same type of process as a SQL cursor) because you acheive better parallelism and can also avoid or at least minimize the net and wrapper overhead.

    -PatP
    Not sure I agree with this. At some level within the DBMS it has to process the rows one at a time. Granted it "may be" faster to do the row level processing within the DBMS instead of going back to the application each time, but as I pointed out above, there are usually bigger issues (concurrency, etc) that come into play. And if the application is local (SP), then the difference may be insignificant.

    No one is suggesting that one use cursor processing from a remote java program where performance is important.
    Last edited by Marcus_A; 01-14-11 at 19:15.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Marcus makes a great point...

    Knowing what you are doing (competancy) seems to be rare these days. Being competent tends to have all kinds of both benefits and hazards.

    The concept of "craftmanship" seeme to be dying. This isn't entirely the fault of the worker, becuase many employers are just as hard on the concept by encouraging behaviors that work against craftmanship.

    The world would be a very different place if the people that wrote code (or their families) depended on that code funcitoning. It is just AMAZING to see the attention to detail when someone realizes that their life or their family might depend on the things that they create working. Very few people work in that environment anymore, but if you do (or even have), it changes your outlook permanently.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  13. #13
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Pat Phelan View Post
    ...becuase many employers are just as hard on the concept by encouraging behaviors that work against craftmanship.
    Many (if not most) employers these days, determine value based on price per hour of the programmer, instead of total cost or quality. Poor management is even more prevalent than poor programming.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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