SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Cursor fetch loops endlessly


Cursor fetch loops endlessly

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218225 Visits: 41995
LutzM (11/17/2013)
Wait a minute, Jeff!
You suggest to "simply" add a FROM clause to the update statement? (The alias isn't mandatory, is it?)
Interesting, what kind of "previous experience" you can refer to... (I'm not sure if I'm supposed to feel glad not to have faced such a situation. Yet!?! ;-))


Oddly enough, yes... that's pretty much what I'm suggesting. And update the alias like I did instead of the table. It was an undocumented trick in older versions of SQL Server long before the documentation in Books Online caught up with it and it was also the easiest way to do a self-joined update using two aliases for the same table in the FROM clause.

The reason I'm suggesting it is because we discovered, quite by accident, that it can make substantial improvements in performance by giving the optimizer better direction through code. Normally this problem has only appeared on improperly formed JOINed updates where the target table wasn't included in the FROM clause but was included in the WHERE clause. And when I say "appeared", I mean appeared in Spades. My first experience (more years ago) with the problem caused a normally 20 second update to pin 4 CPUs to the proverbial wall for two hours!

A long time ago, certain folks took exception to me blaming this type of problem on "Halloweening" because MS supposedly has code behind the UPDATE statement to prevent it, but it sure does have all the ear marks of "Halloweening" especially since this particular problem in generating thousands of unwanted rows.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Atradius
Atradius
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 53
Lutz - I am going to rewrite it set-based, sure.
As I see it, cursors are really meant for interaction between SQL Server
and applications which just cannot understand set-based operations.
Not within a stored procedure.

As I said, I didn't write it, I am having to dig into it as it suddenly blocked
the application.

To your other points - the sproc is not being called multiple times, I have
verified this.
I did execute it directly from Mgmt Studio and it did the same - it never returns for
minutes, meanwhile adding hundreds of thousands of records to the lpn table.

I already created a new procedure with the same code, just to be sure
that the sproc object isn't corrupt in some way. Didn't change a thing.

Good idea to put a WHERE condition.
There SHOULD not be a point trying to filter the records - based on what
the app is doing with it. Yes, if you run this twice, it will update these
records again. (The 'HR' is my rewrite, it is a parameter of the sproc
really). This is "allowable" in the application because t it won't ever
(presumably) call the sproc with the same parameter twice.

Jeff - yes I can put the FROM Addresses in the query, but it doesn't
change the behaviour. It also should not.

I'll solve the problem by rewriting. It still bugs me why on earth this
seemingly innocent code just overnight decided to mess up?
Matt Miller (4)
Matt Miller (4)
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29929 Visits: 19009
Are you SURE nothing changed in the code?

The likely culprit looks to be here:


DECLARE cursorlpn CURSOR FOR
select AccountID from Addresses
where country = 'HR';




I'm making a giant assumption, but how many addresses have the same accountID? Not sure what kind of account this might be- but the accounts where I work have many addresses. Assuming yours did too, then the cursor would "loop" for each duplicated accountID in your addresses table.


To check for duplicates:

select accountID, count(*) from addresses where
country = 'HR'
group by accountid
having count(*)>1



Edit: fixed the missing GROUP BY clause.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218225 Visits: 41995
Peter Schulz-485500 (11/17/2013)
Jeff - yes I can put the FROM Addresses in the query, but it doesn't
change the behaviour. It also should not.

I'll solve the problem by rewriting. It still bugs me why on earth this
seemingly innocent code just overnight decided to mess up?



Thanks for the feedback, Peter. It was a shot in the dark from successes for slightly similar problems where such things go crazy on their own.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
LutzM
LutzM
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24091 Visits: 13559
Is there any way you can provide a setup that could be used to reproduce the odd behavior of the sproc on a standalone machine (meaning that we can test against)?
There has to be a reason for the repetitive call.
And I agree: it's always good to know WHY it occured instead of just making it disappear by "just" rewriting the code...



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
jcb
jcb
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3376 Visits: 997
Strange behaviour happens with update cursors.

First, make sure triggers are not firing in those tables, triggers are the best away to create bugs and strange behaviours in legacy systems.
Second, caution with null! If there are some rows with null values at AccountID you ill get a bad day at work.
Third, to catch infinite loops try to select to a temp table and fetch from that temp table, it can ill give you some insight about the data the cursor actually fetched and updating it ill no affect that fetched rows.
Finally, after you fix the bug (understand why this happens) try to rewrite that query to get rid of that cursor.
Atradius
Atradius
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 53
jcb - thanks, I checked, in fact there are no triggers anywhere in the database. Actually in the production database the AccountID column is an Identity column, sorry, I did not specify this in my sample code.

Lutz, I'll try to set up a test environment. So far I did not succeed. The weird behaviour did not occur when I set up any test environment. Only thing I am sure is that the sproc is called only once, it's not a repeated invocation, it does the turn-around all by itself.

Anyone knows if VB-6 code may be the culprit if it retrieves recordsets and then loops through these via Recordset.MoveNext and so on? That these somehow get counted by SQL Server in the same @@fetch_status variable and thus trick SQL Server into believing fetch_status is always zero?
(Still does not explain why it then turns around to the first row and starts counting anew though)

The apps running here are using VB-6 recordsets extensively, it looks even like they are hardly doing anything else ... As it is a distributed application with various parts running on different stations which must be online almost constantly, I have a bit of a hard time disconnecting everybody. But I'll try this next.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218225 Visits: 41995
Peter Schulz-485500 (11/18/2013)
I have a bit of a hard time disconnecting everybody. But I'll try this next.


Disconnecting everyone and everything is the easy part. Just make sure that you don't lose the connection after you run the following or some nasty ol' web service will snatch up the only connection available.

USE [master]
GO
ALTER DATABASE [YourDatabaseNameHere] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;




If you're sure that high prived users/apps/servers aren't going to barge in, then you could move to a safer multi-connection mode that still keeps most people out and lets you back in if you lose the connection.

USE [master]
GO
ALTER DATABASE [YourDatabaseNameHere] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;




When you're done, let everyone else back in.

USE [master]
GO
ALTER DATABASE [YourDatabaseNameHere] SET MULTI_USER;




--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
jcb
jcb
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3376 Visits: 997
VB6 argh... it smells as a old poor designed legacy app.


Give it a look.
http://technet.microsoft.com/pt-br/library/ms187308.aspx

"Because @@FETCH_STATUS is global to all cursors on a connection, use @@FETCH_STATUS carefully."

Other possible problems are:

someone changed the isolation level and that fetched data is dirty, the application was poor designed and cannot handle some unexpected weird data like duplicated rows.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218225 Visits: 41995
jcb (11/19/2013)
VB6 argh... it smells as a old poor designed legacy app.


Hey, Hey, HEY! I'm old, I used to be a great VB6 programmer, I AM a legacy app, and I'm RIGHT HERE! :-D

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search