• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 411
  • Last Modified:

Urgent! Cursor Error?

Hi,

After 1 hr and 40 minutes, I stopped the script below, since it hadn't finished executing. The table tOrder contains 1271369 rows. Is it an endless loop? Or is some code wrong, taking too long to execute?

best regards,

henrik



declare @varDistrikt int
declare @varLineCount int
declare curDistrikt CURSOR  for SELECT DISTINCT Distrikt FROM tOrder


  OPEN curDistrikt
  fetch next from curDistrikt INTO @varDistrikt
  WHILE @@FETCH_STATUS = 0
  SELECT @varLineCount = (SELECT Count(*) FROM tOrder WHERE Distrikt = @varDistrikt)
  INSERT INTO tComplaintIntermediary (District, TotalLines)
    SELECT @varDistrikt, @varLineCount
  CLOSE curDistrikt
-- use this method to deallocate the cursor after use
DEALLOCATE curDistrikt
0
henrikatwork
Asked:
henrikatwork
  • 8
  • 7
  • 4
  • +2
2 Solutions
 
adatheladCommented:
You don't need a cursor for this task. It'll be much more efficient/quicker to do an INSERT SELECT like this:

INSERT tComplaintIntermediary (District, TotalLines)
SELECT Distrikt, COUNT(1) FROM tOrder GROUP BY Distrikt
0
 
BillAn1Commented:
yes, you are stuck in a endless loop. You need to keep fetching from the cursor......
declare @varDistrikt int
declare @varLineCount int
declare curDistrikt CURSOR  for SELECT DISTINCT Distrikt FROM tOrder


  OPEN curDistrikt
  fetch next from curDistrikt INTO @varDistrikt
  WHILE @@FETCH_STATUS = 0
  begin
  SELECT @varLineCount = (SELECT Count(*) FROM tOrder WHERE Distrikt = @varDistrikt)
  INSERT INTO tComplaintIntermediary (District, TotalLines)
    SELECT @varDistrikt, @varLineCount
    fetch next from curDistrikt INTO @varDistrikt
  end
  CLOSE curDistrikt
-- use this method to deallocate the cursor after use
DEALLOCATE curDistrikt
0
 
RejojohnyCommented:
yp .. it looks like a endless loop as u are missing the next fetch statement

 
   
   
  Page Options  
 Reload This Question
 Microsoft SQL Area
 Send To A Friend
 Ask a Question
 
 
   
   
  Search 1M Solutions  
 
 Keywords  
 
     
 Advanced Search...    
 
 
   
   
      Page Editor  
Microsoft SQL  
ScottPletcher  
 
   
   
      Featured Expert  
rafrancisco  
Ask An Expert Now!  
 
   
   
 Top 15
Overall
   Microsoft SQL Server  
 
 
  arbert  2141651
 
  ScottPletcher  1724689
 
  acperkins  1197075
 
  Hilaire  1144645
 
  angelIII  847718
 
  Lowfatspread  838377
 
  bhess1  500518
 
  BillAn1  495199
 
  nigelrivett  486339
 
  EugeneZ  398929
 
  adathelad  395653
 
  ShogunWade  377258
 
  SjoerdVerweij  349985
 
  danblake  341517
 
  rafrancisco  336806
Hall of Fame  
 
   
   
  What is your expert ranking?  
Topic Area

   
Certified Expert
Your Level
 
 
Get Certified Now  
Master  50,000
Guru  150,000
Wizard  300,000
Sage  500,000
Genius  1,000,000
 
 
   
   
 Top 15
Yearly
   Microsoft SQL Server  
 
 
  ScottPletcher  497612
 
  acperkins  417160
 
  rafrancisco  336806
 
  Hilaire  321405
 
  BriCrowe  258408
 
  arbert  257390
 
  EugeneZ  191401
 
  KarinLoos  174690
 
  adathelad  142817
 
  BillAn1  134565
 
  Kevin3NF  128852
 
  amit_g  127695
 
  ptjcb  115355
 
  itsvtk  86860
 
  Lowfatspread  86578
 
 
   
 Home All Topics Databases Microsoft SQL Viewing a Question


Search 1,240,431 Solutions    
 
   Restrict to this Topic Area
Advanced Search NEW!  

 Question Title: Urgent! Cursor Error?
asked by henrikatwork on 05/11/2005 11:25AM AST  
This question is worth  400 Points  
 


Hi,

After 1 hr and 40 minutes, I stopped the script below, since it hadn't finished executing. The table tOrder contains 1271369 rows. Is it an endless loop? Or is some code wrong, taking too long to execute?

best regards,

henrik



declare @varDistrikt int
declare @varLineCount int
declare curDistrikt CURSOR  for SELECT DISTINCT Distrikt FROM tOrder


  OPEN curDistrikt
  fetch next from curDistrikt INTO @varDistrikt
  WHILE @@FETCH_STATUS = 0
  BEGIN --- missing this
        SELECT @varLineCount = (SELECT Count(*) FROM tOrder WHERE Distrikt = @varDistrikt)
        INSERT INTO tComplaintIntermediary (District, TotalLines)
        SELECT @varDistrikt, @varLineCount
         fetch next from curDistrikt INTO @varDistrikt --- missing this
  END --- missing this
  CLOSE curDistrikt
-- use this method to deallocate the cursor after use
DEALLOCATE curDistrikt
 
 
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
BillAn1Commented:
I didn't examine your logic closely enough, but as adathelad syay, you don't actually need a cursor at all.
0
 
RejojohnyCommented:
i do not know wht happened here :-) the comments I  had posted only were

declare @varDistrikt int
declare @varLineCount int
declare curDistrikt CURSOR  for SELECT DISTINCT Distrikt FROM tOrder


  OPEN curDistrikt
  fetch next from curDistrikt INTO @varDistrikt
  WHILE @@FETCH_STATUS = 0
  BEGIN --- missing this
        SELECT @varLineCount = (SELECT Count(*) FROM tOrder WHERE Distrikt = @varDistrikt)
        INSERT INTO tComplaintIntermediary (District, TotalLines)
        SELECT @varDistrikt, @varLineCount
         fetch next from curDistrikt INTO @varDistrikt --- missing this
  END --- missing this
  CLOSE curDistrikt
-- use this method to deallocate the cursor after use
DEALLOCATE curDistrikt
0
 
henrikatworkAuthor Commented:
Ok, I added
 fetch next from curDistrikt INTO @varDistrikt

The query is running since 25 minutes - how long should I expect it to run before I assume something is wrong? The machine is a P4 1,6 GH, win server 2003 with no other services/programs running actively.
0
 
henrikatworkAuthor Commented:
0
 
RejojohnyCommented:
SELECT DISTINCT Distrikt FROM tOrder .. how much records does this give? also did u give a try to what adathelad had suggested?
0
 
henrikatworkAuthor Commented:
No, I didn't try adathelad's suggestion, since I wanted to make the script work.

DISTINCT Distrikts will return approx. 1500 records
0
 
adatheladCommented:
henrik - please read my first post. You'll find that the query I posted will perform better than a cursor. Cursor's are notoriously poor for performance and I'd recommend only using them as a last resort
0
 
RejojohnyCommented:
how many records are there in tOrder and tComplaintIntermediary and do u have any triggers in these tables ... could u also post the latest code ...
0
 
RejojohnyCommented:
ok u have mentioned that tOrder has 1271369 records .. so how much time does the statement

SELECT DISTINCT Distrikt FROM tOrder
take
0
 
RejojohnyCommented:
do u have an index on field Distrikt ?
0
 
henrikatworkAuthor Commented:
@Rejojohny: 37seconds, 194 rows, no indexes, latest code below:

declare @varDistrikt int
declare @varLineCount int
declare curDistrikt CURSOR  for SELECT DISTINCT Distrikt FROM tOrder


  OPEN curDistrikt
  fetch next from curDistrikt INTO @varDistrikt
  WHILE @@FETCH_STATUS = 0
  SELECT @varLineCount = (SELECT Count(*) FROM tOrder WHERE Distrikt = @varDistrikt)
  INSERT INTO tComplaintIntermediary (District, TotalLines)
    SELECT @varDistrikt, @varLineCount
    fetch next from curDistrikt INTO @varDistrikt
  CLOSE curDistrikt

0
 
RejojohnyCommented:
u still do not have the begin and end statement ....
0
 
RejojohnyCommented:
Current code would again be a endless loop .. as mentioned above .. change it to

declare @varDistrikt int
declare @varLineCount int
declare curDistrikt CURSOR  for SELECT DISTINCT Distrikt FROM tOrder


  OPEN curDistrikt
  fetch next from curDistrikt INTO @varDistrikt
  WHILE @@FETCH_STATUS = 0
  BEGIN --- U R MISSING THIS
        SELECT @varLineCount = (SELECT Count(*) FROM tOrder WHERE Distrikt = @varDistrikt)
        INSERT INTO tComplaintIntermediary (District, TotalLines)
        SELECT @varDistrikt, @varLineCount
         fetch next from curDistrikt INTO @varDistrikt --- missing this
  END --- U R MISSING THIS
  CLOSE curDistrikt
-- use this method to deallocate the cursor after use
DEALLOCATE curDistrikt
0
 
henrikatworkAuthor Commented:
@adathelad: Your query works excellently, done in 22 sec
0
 
Anthony PerkinsCommented:
>>Your query works excellently, done in 22 sec<<
Great, now close that question and while you are at it please maintain your abandoned questions:
1 03/22/2005 500 Encrypt IE pwd:s  Open Windows Security
2 04/13/2005 300 disconnect master document  Open Microsoft Word
8 01/30/2005 500 Suitable bandwidth for Age of Empires 2  Open Games
0
 
henrikatworkAuthor Commented:
cheers everyone!

@acperkins: I will close the question whenever I feel appropriate and I have no abandandoned questions, read the questions and you will see the following:

1. Encrypt IE - closing recommendation posted
2. Disconnect master - Will be done (as stated in my last comment in that thread) when my current report is close to finished
3. The CD broke and I'm working on getting a new one

0
 
Anthony PerkinsCommented:
>>I will close the question whenever I feel appropriate<<
I am sorry that is not the way it works here and you have been here long enough to know that.  If you need a refresher course on that take the time to re-read the EE guidelines that you agree to every time that you ssign on to this site.

>>and I have no abandandoned questions<<
I beg to differ with you on this.

>>read the questions and you will see the following<<
I was hoping you would not bring me to that, but here it is:

01/30/2005 500 Suitable bandwidth for Age of Empires 2  Open Games
http://www.experts-exchange.com/Miscellaneous/Games/Q_21294346.html
You were reminded by a Page Editor as recent as April that this question was considered abandoned.

03/22/2005 500 Encrypt IE pwd:s  Open Windows Security
http://www.experts-exchange.com/Security/Win_Security/Q_21359914.html
That is not the way it works here and again you should know that.  In case you have forgotten, here's how you close it:
Nobody answered my question.  What do I do?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi71

Thanks.
0
 
Anthony PerkinsCommented:
Take a look at the comments here:
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21387671.html#13979053
Notice the different attitude taken by the questioner when reminded.  You may want to learn from that.  It makes the difference between a healthy community and one that is not as good.
0
 
henrikatworkAuthor Commented:
This site will loose it's value for money (talking about us paying this site) when uncorrect (or not tested) answers are accepted as solutions - leading to an uncorrect database. I understand your viewpoint as an expert, that you wish to get your points as soon as possible and I have a deep respect for all experts spending their sparetime with helping others, enough to give points straight away to the solutions I test. Please forgive me for being a paying member, no't wanting to accept solutions unless they are correct.

I appreciate if you remember me if I forget to close questions, but remembering me the same day as I posted this question, from my point of view, felt very unnecessary (when did I last time posted "yeah worked great" and then didn't close the question close to that?)

Maybe you could spend some time on finding better solutions to my open questions, so that I can have those problems solved AND close those questions, that would make us all happy :)


cheers,

henrik
0
 
Anthony PerkinsCommented:
>>when uncorrect (or not tested) answers are accepted as solutions - leading to an uncorrect database<<
I could not agree with you more.  So request the question be deleted, that way we do not pollute the PAQ any further.

>>I understand your viewpoint as an expert, that you wish to get your points as soon as possible <<
I am afraid you don't.  I could not care less.  I have enought as it is :)

>> I have a deep respect for all experts spending their sparetime with helping others<<
Than show it by abiding by the EE Guidelines.  Or better still forget about feigning that you care and instead think about the value that this site brings to you when you keep up to date with your open questions.  Again, think of it from a purely selfish point of view and jsut remember that many experts will be more responsive if you comply with the site agreement.

>>no't wanting to accept solutions unless they are correct.<<
I am not asking you to do so.

>>when did I last time posted "yeah worked great" and then didn't close the question close to that?)<<
How about this  (http://www.experts-exchange.com/Miscellaneous/Games/Q_21294346.html#13576895) posted March 18:
"It's not abandoned, I will examine this problem sometime in april."

>>Maybe you could spend some time on finding better solutions to my open questions,<<
Sorry, not interested.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 7
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now