4
\$\begingroup\$

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 Inner_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

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

\$\endgroup\$

closed as off-topic by 200_success Jul 2 '14 at 5:26

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions must involve real code that you own or maintain. Questions seeking an explanation of someone else's code are off-topic. Pseudocode, hypothetical code, or stub code should be replaced by a concrete example." – 200_success
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    \$\begingroup\$ it might be easier for someone to give you an answer if you actually post what is going into the cursors \$\endgroup\$ – DForck42 Jun 1 '11 at 18:05
5
\$\begingroup\$

Best practice is to do away with cursors. Both of them. There is always a better-performing solution.

If possible, reduce the problem to one that can be solved with sets instead of iteration.

But, if this nested iteration cannot be avoided, you will still get far better performance using table variables and WHILE loops instead of a cursor. For example:

DECLARE @outerData TABLE (outerID INT, outerColumn VARCHAR(50), PRIMARY KEY (outerID))
DECLARE @innerData TABLE (innerID INT, innerColumn VARCHAR(50), PRIMARY KEY (innerID))
DECLARE @currOuter INT
DECLARE @currInner INT

INSERT INTO @outerData
SELECT  EmployeeID, EmployeeName
FROM dbo.Employee

WHILE EXISTS (SELECT outerID FROM @outerData)
BEGIN
    SELECT @currOuter = TOP outerID FROM @outerData

    INSERT INTO @innerData
    SELECT SaleID, SaleDesc
    FROM EmployeeSales 
    WHERE EmployeeID = @outerID

    WHILE EXISTS (SELECT innerID FROM @innerData)
    BEGIN
        SELECT @currInner = TOP innerID FROM @innerData

        --Do work with inner data

        ...blah-de-blah...

        --Delete after work is finished
        DELETE FROM @innerData   WHERE innerID = @currInner
    END

    --Delete current outerID and empty the innerdata table 
    --to prepare to move to the next record

    DELETE FROM @outerData WHERE outerID = @currOuter
    DELETE FROM @innerData
END
\$\endgroup\$
2
\$\begingroup\$

If the inner cursor can be run through one time, create a table variable and store the results there to be re-used before working within the outer cursor.

And as far as calculations go, is it possible to do away with the cursors by using common table expressions?

\$\endgroup\$

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