Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
I have a Stored Procedure with CURSOR which takes around 3 minutes to run. It works fine in the Management Studio, but not in my Application.I have increased its command timeout to 600. Now, Sometimes I gets full results into the Application(When I am executing the procedure 4 or 5 times)
 
Can any one help me to retrieve full result on first time execution itself....
 

Now I removed all cursors from the query and reduced the execution time to "20 secs or less ". But Still the problem exists. The result is not complete, many rows are removed from my result.(I gets only 11965 records)
Posted 20-Dec-11 21:04pm
Edited 1-Jan-12 21:53pm
v4
Comments
Al Moje at 21-Dec-11 2:07am
   
Did you increased also your connection timeout?... If not then do it...
thatraja at 21-Dec-11 3:28am
   
Show the SQL script
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi,
 
See this link if could help...
How set CommandTimeout and ConnectionTimeout
 
Regards,
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Quote:
I have a Stored Procedure with CURSOR which takes around 5 minutes to run.

5 Mins is tooo much time to wait for a user at front end.
 
My suggestion is run a Sql-Job on that procedure. so that there is no burden on your application.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

The problem may be insufficient indexing, possibly locking issues, badly formed SQL etc.
 
Try to use SSMS Activity monitor to see that no locks interfere: http://msdn.microsoft.com/en-us/library/ms175518.aspx[^]
 
And optimize the query and/or the underlying tables/indexes. Without seeing the statements, there's not much to help with concerning the optimization.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

Hi,
 
if your cursor is Forward_Only(least restrictive) then you need to check something else. please let us know what you have written in your cursor,
 
Please check Cursor Type
 
and check which type of cursor is best suite in your logic.
 
hope using proper type you can solve your problem
 
thanks
-amit.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 475
1 Maciej Los 339
2 Richard MacCutchan 225
3 BillWoodruff 219
4 Suraj Sahoo | Coding Passion 155
0 OriginalGriff 8,759
1 Sergey Alexandrovich Kryukov 7,407
2 DamithSL 5,639
3 Maciej Los 5,269
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web04 | 2.8.1411028.1 | Last Updated 2 Jan 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100