长时间运行SQL CURSOR [英] long running SQL CURSOR

问题描述

我有一个使用CURSOR的存储过程，大约需要3分钟才能运行。它在Management Studio中运行良好，但在我的Application.I中已将命令超时增加到600.现在，有时我将完整的结果输入应用程序（当我执行程序4或5次时）



任何人都可以帮我在第一次执行时检索完整的结果....





现在我从查询中删除了所有游标，并将执行时间减少到20秒或更短。但问题仍然存在。结果不完整，我的结果中删除了很多行。（我只获得11965条记录）

解决方案

如果有帮助，请看这个链接...

如何设置CommandTimeout和ConnectionTimeout



问候，

问题可能不足索引，可能锁定问题，格式错误的SQL等。



尝试使用SSMS活动监视器来查看没有锁定干扰： http://msdn.microsoft.com/en-us/library/ms175518.aspx [ ^ ]



和优化查询和/或基础表/索引。在没有看到这些陈述的情况下，关于优化没有多大帮助。

如果你的光标是Forward_Only（限制最少）然后你需要检查别的东西。请告诉我们您在光标中写的内容，



请查看库存类型



并检查逻辑中最适合哪种类型的游标。



希望使用合适的类型你可以解决你的问题



谢谢

-amit。

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)

解决方案

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.

这篇关于长时间运行SQL CURSOR的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！