为什么存储过程查询有时需要很少的时间来执行 [英] why stored procedures query sometime taking little time to execute

问题描述

我在存储的proc中有一个更新的查询.它在应用程序中快速运行了一段时间，即在一秒钟之内将执行，几分钟后将花费大约40秒.同样，如果我们刷新应用程序并使用它，它将快速运行.

查询如下，

Hi,

i have a updated query in stored proc.It runs fastly for a while in application ie within a second it will execute,after some few minutes it will take around 40 seconds.Again if we refresh the application and use,it will run fastly.

query as below,

ALTER PROCEDURE [dbo].[UpdateSatckWithBookID] 

@movie_ID int,
@screen_ID tinyint,
@class_ID  tinyint,
@movie_ShowDate smalldatetime,
@show_ID tinyint,
@row_No smallint,
@col_No smallint,
@is_Available bit,
@is_Counter char(10),
@book_DateTime smalldatetime,
@book_IPAddress varchar(15)

as
set nocount on
set transaction isolation level serializable
begin transaction


Update movie_Show_Stack set is_Available=@is_Available,is_Counter=@is_Counter,book_DateTime=convert(smalldatetime,@book_DateTime,103),book_IPAddress=@book_IPAddress  where row_no=@row_No and col_No = @col_No and screen_id=@screen_ID  and class_id=@class_ID  and Show_ID=@show_ID and Movie_ID=@movie_ID and movie_ShowDate =convert(smalldatetime,@movie_ShowDate,103)


if(@@error<>0)
begin
 rollback transaction
 return -1
end
else
begin
 commit transaction
 return 1
end

请举一个例子

please reply with an example

推荐答案

有几种可能性:

正如Amir已经指出的那样，一个原因可能会干扰锁等.使用SSMS检查不同类型的冲突:使用SQL Server进行监视Management Studio [^ ]

如果没有锁定问题，并且您写了刷新应用程序有帮助，那么这听起来也可能是在调用方.检查瓶颈是否存在以及您的连接设置(包括连接池)是否正确.

There are several possibilities:

As Amir already pointed out one reason could interfering locks etc. Use SSMS to check different kinds conflicts: Monitoring with SQL Server Management Studio[^]

If there are no locking issues and you wrote that refreshing application helps, it also sounds that the problem may be on the calling side. Check if the bottleneck is there and that your connection settings including connection pool are correct.

您好，

由于这是限制性最强的隔离级别，它将尝试获取表行范围上的锁.因此可能需要花费一些时间.但是请检查分析器中是否有其他查询正在后台运行.

还检查您是否已全新安装SQL Server?

谢谢
-amit.

Hi,

as this is the most restrictive isolation level , it will try to acquire lock on your table rows range. so it may so happen it takes some time for that. but check in profiler if anyother query is running on background or not.

also check if you have fresh installation of SQL server ?

thanks
-amit.

确保movie_Show_Stack在下面的列中具有正确的索引

row_no
col_No
screen_id
class_id
Show_ID
Movie_ID
movie_ShowDate

Make sure that movie_Show_Stack has proper index on below columns

row_no
col_No
screen_id
class_id
Show_ID
Movie_ID
movie_ShowDate

这篇关于为什么存储过程查询有时需要很少的时间来执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！