Cursor already exists problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Cursor already exists problem

Author

Topic

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2008-04-11 : 16:27:00

To preface, I rewrote the procedure in question so that it works correctly. However, I'm still befuddled why the procedure wasn't working properly, in its original form.

I have two cursors: A main cursor and a sub cursor (that is, the sub cursor is declared, opened, closed, and deallocated inside the main cursor).

The flow looks something like this (pseudocode):


--Start of Proc

...

SET @SQL = 'Declare Main Cursor Fast_Forward For ...'

exec(@SQL)

Open Main

Fetch Next From Main Into ...

IF @@Fetch_Status = 0
BEGIN

-- Do some stuff with Main

SET @SQL = 'Declare Sub Cursor Fast_Forward For...'

exec(@SQL)

Open Sub

If @@Fetch_Status = 0 
BEGIN

Fetch Next From Sub Into ...

-- Do some stuff with  Sub

Close Sub
DeAllocate Sub

END -- Sub if 
END -- Main if

While @@Fetch_Status = 0
BEGIN

-- Do some stuff with Main

SET @SQL = 'Declare Sub Cursor Fast_Forward For...'

exec(@SQL)

Open Sub

If @@Fetch_Status = 0 
BEGIN

Fetch Next From Sub Into ...

-- Do some stuff with Sub

Close Sub
DeAllocate Sub

END -- Sub if 
END -- Main while

...

-- End of Proc

Again, I decided to veer off into a different direction. I'm really looking for a lesson on what went wrong and why. The main contention is I don't understand why the second cursor is causing problems. I run the proc once and it's fine. It's only on the second execution and above that it raises the error of Sub cursor already exists. Now both cursors are global; I changed the Sub cursor from global to local and a new error appeared (on the first execution) that Sub cursor does not exist. I put Sub cursor back to global.

I decided to load up SQL Profiler and examine the cause, turning in StmtStarting and StmtCompleted, amongst other events. I discovered that, after the first time I declared the Sub cursor (before the while loop!), I saw a StmtStarting event for processing Dynamic SQL and opening of the Sub cursor. The problem is I never saw StmtCompleted for either action.

The question I'd like to know is why?

I re-edited the proc, such that the If loop which encompasses the main and sub cursors was eliminated, leaving just the while loop. This worked like a charm. The question is why does the former methodology fail to work.

I'd really appreciate any knowledge. Thank you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-11 : 16:40:50

You are using cursors in dynamic queries?
What could ever be the reason for this design?

E 12°55'05.25"
N 56°04'39.16"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-11 : 16:42:33

quote:
Originally posted by SQLIsTheDevil

I'm really looking for a lesson on what went wrong and why.

Here's what went wrong:

1. You used a cursor!
2. You used two cursors!
3. You used cursors with dynamic SQL!
4. You didn't close and deallocate the Main cursor. All I see is Sub being closed and deallocated twice.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2008-04-11 : 16:50:04

Forgot to list Close and deallocation of the Main cursor in the first post but it's in the proc.

Look, I didn't come here to argue the merits of cursors. I'd rather not get into that discussion. I just want to understand why taking out the IF loop enclosing the Main and Sub cursors makes the proc work fine.

As an aside, however, how could I employ dynamic SQL and not declare the cursor inside of it? That might be the culprit.

Thank you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-11 : 16:54:31

Hard to tell since we can't see the actual code.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-11 : 17:07:07

Or you could try to rewrite the complete code as a set-based solution?

E 12°55'05.25"
N 56°04'39.16"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-11 : 17:09:37

quote:
Originally posted by SQLIsTheDevil

Forgot to list Close and deallocation of the Main cursor in the first post but it's in the proc.

Then show us the real code so that we don't have to go back and forth with this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-04-11 : 17:33:01

quote:
Originally posted by Peso

Hard to tell since we can't see the actual code.

Yeah, it's hard to tell since it's only pseudocode. I assume there is a loop in there somewhere but I can't tell. Are not looping through the sub-cursor? If not, then using a cursor in that way seems rather pointless.

But again hard to say with out the actual code. It also looks like you could of have a problem checking the fetch_status...?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-11 : 18:10:51

quote:
Originally posted by tkizer

Here's what went wrong:

1. You used a cursor!
2. You used two cursors!
3. You used cursors with dynamic SQL!
4. You didn't close and deallocate the Main cursor. All I see is Sub being closed and deallocated twice.

2.5 You used nested cursors!

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-11 : 18:12:50

quote:
SQLIsTheDevil

T-SQL is your best friend if you know you to use it.

E 12°55'05.25"
N 56°04'39.16"

sfortner
Yak Posting Veteran

63 Posts

Posted - 2008-04-16 : 12:43:07

Well... There's nothing really wrong with a cursor or a nested cursor, especially when you're learning and are looking to understand better how something is working. Even the gurus use cursors, just go ahead and admit it. When the complexity requires it, it's really hard to avoid. There's a reason MS included them. It also helps sometimes when you need to get something done quickly and are looking to be more productive and just get something finished and don't particularly care how long it takes to run. Why don't all you gurus take a look at my post "How to efficiently break apart cumulative data" if you want a real problem to solve. :) No one's given me a better solution yet... All of us don't always have the time to optimize and tune everything we do. Anyway, SITD, here's a silly example/template I'd use for a nested cursor setup that you may consider:

DECLARE @ThemeName varchar(50),
		@TemplateName varchar(50)

DECLARE ffcursor CURSOR FAST_FORWARD FOR
  select ThemeName from Themes

OPEN ffcursor

FETCH NEXT FROM ffcursor
  INTO @ThemeName

WHILE @@FETCH_STATUS = 0
BEGIN
  -- do something with @ThemeName

  -- inner cursor
  DECLARE innercursor CURSOR FAST_FORWARD FOR
    select t.TemplateName from Templates t join Themes th on (t.ThemeID = th.ThemeID)
    where th.ThemeName = @ThemeName

  OPEN innercursor

  FETCH NEXT FROM innercursor
    INTO @TemplateName

  WHILE @@FETCH_STATUS = 0
  BEGIN
    -- do something with @TemplateName

    FETCH NEXT FROM innercursor
      INTO @TemplateName
  END

  CLOSE innercursor
  DEALLOCATE innercursor

  FETCH NEXT FROM ffcursor
    INTO @ThemeName
END

CLOSE ffcursor
DEALLOCATE ffcursor

--Steve

chandan_joshi80
Starting Member

30 Posts

Posted - 2008-04-17 : 06:35:15

NOTHING WRONG,I HAVE USE NESTED CURSOR.JUST REMEMBER PROPERLY CLOSE & DEALLOCATE CURSOR AFTER ITS USE.THEN REOPEN WHEN YOU NEED AGAIN.

chandan

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2008-04-17 : 15:18:41

I fixed the problem. I tried using local cursors but then recalled dynamic sql is in its own scope. The problem was I forgot that @@Fetch_Status, which is global to the connection, only cares about the last fetch from any cursor and thus confusion ensued. Perhaps sp_cursor_list provides a functionality to confirm the status of a particular cursor, but I decided to modify the above using one while loop and one @@Fetch_Status. It works now so I'm content.

Maybe this is a question for another thread, but a set logic solution is not necessarily faster than cursors. I recall an example on the interweb where row-by-row processing with, maybe, 2 millions rows was carried out using set logic and cursors, respectively. The cursor logic was faster. I'll have to find the example, but I just don't understand why some believe cursors have no use and are always slower than set logic. It's just not true.

edit: http://searchsqlserver.techtarget.com/general/0,295582,sid87_gci1176816,00.html

This was not THE article to which I was referring but it makes its point about while loops vs cursors on row-by-row operations for massive data sets.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-17 : 15:21:01

It is extremely rare for a cursor to be faster than a set-based solution. It is doubtful that your problem fit that condition.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-17 : 15:31:44

quote:
Originally posted by SQLIsTheDevil

edit: http://searchsqlserver.techtarget.com/general/0,295582,sid87_gci1176816,00.html

This was not THE article to which I was referring but it makes its point about while loops vs cursors on row-by-row operations for massive data sets.

We were not saying to replace the cursor with a while loop. While loops are not set-based.

And you should note this from the article:

quote:

SQL developers are often under the gun to write code fast. Writing a cursor requires less mental effort than writing its set-based equivalent. Unfortunately these shortcuts often remain in production and cause problems further down the line.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-17 : 15:55:51

I have been working with databases the last 26 years. With Microsoft SQL Server since early 1990 (version 4.21).
How many times do you think a CURSOR has been the last resort for me?

The simple answer is: One time only.

That one time was autumn 2006 when a CURSOR was the fastest solution to a very special algorithm.
All other times a set-based solution have been faster, cleaner and more maintainable.

Also have a look at this topic regarding CURSORs and set-based solution.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93911

E 12°55'05.25"
N 56°04'39.16"

sfortner
Yak Posting Veteran

63 Posts

Posted - 2008-04-18 : 13:47:45

I think the key is experience. Peso with 26 years of experience. Wow. I don't think I have that much experience with anything! If you have a query, whatever it is, and it's not performing as well as it should or as well as you'd like, the approach is to always try new index combinations, or run it through the index tuning wizard for suggestions. Try different ways of rewriting it. Run the query through the estimated execution plan and see if it has any index scans or table reads and try to correct those. Then look at the number of I/O reads and try to reduce everything. Then if you still can't get it to run faster, post it here for people with more experience.

On my question, Lamprey had a slick solution that replaced the cursor I had written, and I've used derived tables and self-joins before, but hadn't really had it gel in my head yet that it appears that cursors can often be replaced with derived tables. Seems I'd read that somewhere. It makes sense anyway that it would. A cursor is used to iterate over some query only to have another query/statement do something with that data retrieved by the cursor. Again, experience shows its ugly head. We just have to keep learning and improving.

--Steve

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-04-18 : 15:18:02

quote:

SQL developers are often under the gun to write code fast. Writing a cursor requires less mental effort than writing its set-based equivalent. Unfortunately these shortcuts often remain in production and cause problems further down the line.

To me using a cursor requires MORE mental effort. Cursors are a complete PITA.

I have only found one situation where a cursor was faster than a set based solution. That's in 10 years of work with SQL server.

quote:

There's nothing really wrong with a cursor or a nested cursor, especially when you're learning and are looking to understand better how something is working.

WRONG. There's nothing really right about cursors or nested cursors. Cursors are especially bad if you're learning and looking to better understand how something is working. Why is that? Because it gives you a false understanding.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources