Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2004
    Posts
    6

    Unanswered: Cursor or Temp table

    I just want to know which one is more efficient cursor or temp table for looping through a record set?

    I am basically looking for better performance and server utilization.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I generally prefer set based solutions over cursors, so I'm more than 95% sure that I'd recommend the temp table solution, but I can't be sure about that without knowing more about the problem.

    -PatP

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    95%?

    That low?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Feb 2002
    Location
    Assam, India
    Posts
    55
    For looping through a recordset i.e., record by record, you will need a cursor. Ofcourse, you can bring the data from many tables into a #temp table, and use a cursor on it, that is to say, the SELECT statement of the cursor could be on the #temp table.

    It may affect the performance of the stored procedure, but it is negligible. It depends on what you want.

    Roshmi Choudhury

  5. #5
    Join Date
    Oct 2003
    Posts
    706
    It partly depends upon what you plan on doing with the records. If you do a simple SELECT, then most-likely the DBMS is already creating a temporary-table to store the results. If you are requesting a live query-view for updating purposes, the SELECT is effectively a filter on the main table.

    Your choice probably won't make any difference to the DBMS server; the impact, if any, will be upon the simultaneous users of the table. ("Large things" and "long-time things" are generally "bad things.")

    My rule-of-thumb recommend is K.I.S.S. ... keep it simple. Choose the approach that makes the most sense to your application and let the DBMS take care of its own business. If in the future you can prove that it is a problem, then you can change it.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  6. #6
    Join Date
    Jan 2004
    Location
    Atlanta
    Posts
    18

    Smile

    I disagree, for looping thru a record set you most definitely do not need a cursor, using an IF WHILE loop is just as effective, and carries none of the problems associated with cursors.. Many shops - mine included - do not allow cursors on their servers...Seems like the only people who want to use a cursor are those people who can't write one that won't get hung and take a server down with it.... Just my .02

    Nick

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You don't ALLOW cursors? That's (unnecessarily) extreme.

    If you truly need to "loop" through your dataset, then a cursor is an appropriate solution. The larger issue is WHETHER you need to loop through the dataset. I'm sure Pat would agree that "95%" of the time when somebody uses a cursor it's because they are not familiar enough with set-based solutions, and in these cases cursors are virtually always less efficient.

    Post the problem you are trying to solve, and I'd bet that somebody here can show you how to solve it using set-base operations.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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