Click here to Skip to main content
15,065,644 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Updated 1-Jan-12 20:53pm
v4
Comments
Al Moje 21-Dec-11 2:07am
   
Did you increased also your connection timeout?... If not then do it...
thatraja 21-Dec-11 3:28am
   
Show the SQL script

Hi,

See this link if could help...
How set CommandTimeout and ConnectionTimeout

Regards,
   
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.
   
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.
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900