Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Before I tried to avoid using cursor because of performance issues. But now I have to do some calculations to reach some goals. I have a stored procedure that has a code like this:

DECLARE  Outer_Cursor CURSOR FOR...

   OPEN Outer_Cursor
   FETCH NEXT FROM Outer_Cursor INTO ...

   WHILE @@FETCH_STATUS = 0
   BEGIN

      DECLARE  Inner_Cursor CURSOR  FOR...

      OPEN Outer_Cursor
      FETCH NEXT FROM Inner_Cursor INTO ...

      WHILE @@FETCH_STATUS = 0
      BEGIN

        ...

        FETCH NEXT FROM Inner_Cursor INTO ...
      END

      CLOSE Inner_Cursor
      DEALLOCATE Inner_Cursor    

      FETCH NEXT FROM Outer_Cursor INTO ...

  END

  CLOSE Outer_Cursor
  DEALLOCATE Outer_Cursor

My question is: is it possible to avoid (minimize) so expensive cursor recreation for inner cursor. How can I reuse inner cursor declaration, is it possible?

Thank you.

share|improve this question
    
Without a bit more information, it's hard to know how to help you. The code you laid out looks like a fairly standard nested cursor implementation, but as you noted those can perform terribly. There may be a way to reach the same goal without nested cursors, even if it makes the code more complex. –  ajk May 16 '11 at 15:03
    
@AJ01 I have to use inner cursor. :( –  garik May 16 '11 at 15:16
    
why do you *have" to use a cursor? You say you do (homework?) but why don't you give us the problem. Nested cursors will never run OK –  gbn May 16 '11 at 18:23
    
@gbn homework?! :) no. nested code has a lot of logical expressions, multiply stored procedure calls. What should I do in this case? :) –  garik May 16 '11 at 19:12

1 Answer 1

up vote 3 down vote accepted

I'd suggest that instead of using the inner cursor, if you want to avoid its creation and release for every step of the outer cursor, to use a temp table to fetch data and truncate it for each step.

That way you'll create it just once, and the truncation operation is not logged, so it will not cause any problems.

PS: in this situation I'm always in favor of temp tables with an identity column that will be looped for each record (or batches :-). But I agree with the comments, better tell us the problem and maybe we'll find another solution.

share|improve this answer
    
O! thanks! while (1 = 1)... I will compare these two variants. –  garik May 16 '11 at 20:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.