存储过程执行时间过长。 [英] Stored procedure taking too long to execute.

问题描述

您好!!!

我创建了一个存储过程，但执行时间太长。当我在sql server 2008中执行它时，执行时需要30秒，但是当我从asp.net调用它时，它表示连接超时。我将连接超时增加到120，但它没有帮助。



我重建了所有索引，但问题依然存在。



这里是程序

Hello!!!
I have created a stored procedure but it is taking too long to execute. when i execute it in sql server 2008 it takes exactly 30 seconds for execution but when i call it from asp.net it says connection timeout. i increased the connection timeout to 120 but it didn''t helped.

I rebuild all the index but the problem remains the same.

here is the procedure

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[sp_getSummary_S]
AS
BEGIN
(
  Select usr_nm,
  usr_cd,
  (select count(*) from action_taken where status = 'Open' and sent_to_usr_code=userdet.usr_cd ) as [Letters To Receive],

  (
  ((SELECT COUNT(*) FROM action_taken AS at
INNER JOIN userdet AS u ON u.usr_cd = at.sender_usr_code
INNER JOIN register AS r ON at.receiver_reg_code = r.reg_code
INNER JOIN let_entry AS e ON at.let_no = e.let_no
WHERE (at.sent_to_usr_code = userdet.usr_cd) AND (at.status = 'Received') and (e.status = 'Received')
and (e.cur_usr_code = userdet.usr_cd) and let_file_status=18 and isnull(at.multiple_marked,'')=0
and e.let_no not in (Select let_no from file_let_det ) )
+
(SELECT COUNT(*) FROM let_entry AS e
INNER JOIN userdet AS u ON u.usr_cd = e.org_usr_code
INNER JOIN register AS r ON e.reg_code = r.reg_code
WHERE (e.org_usr_code = userdet.usr_cd) AND (e.status = 'Created')
and e.let_no not in (Select let_no from file_let_det))
+
(SELECT COUNT(*) FROM action_taken AS at
INNER JOIN userdet AS u ON u.usr_cd = at.sender_usr_code
INNER JOIN register AS r ON at.receiver_reg_code = r.reg_code
INNER JOIN let_entry AS e ON at.let_no = e.let_no
INNER JOIN let_mult_marked M on at.let_no=M.let_no
WHERE (at.sent_to_usr_code = userdet.usr_cd) AND (at.status = 'Received') and (e.status = 'Received')
and ( M.cur_usr_code=userdet.usr_cd) and let_file_status=18 and at.multiple_marked=1))) as [Letters To Mark],
  (
   (SELECT count(*) from file_entry WHERE (section_code =userdet.usr_sec_code) and file_movement='Yes' AND (file_status = 'Open' or file_status ='Gaurd File/Close File') and file_creation_usr =userdet.usr_cd and (select count(*) from file_action_taken where file_code=file_entry.file_code and file_part=file_entry.file_part) = 0 )
   + (SELECT count(*) from file_action_taken fat inner join file_entry fe on fat.file_code = fe.file_code  WHERE fat.sent_to_usr_code = userdet.usr_cd AND (fat.status = 'Received') and isnull(fe.file_movement,'') = 'Yes')
  ) as [Files To Mark],
  (
   (SELECT COUNT(*) AS Expr1 FROM file_action_taken WHERE (sent_to_usr_code = userdet.usr_cd) AND (status = 'Open'))
  ) as [Files To Receive]
  From userdet WHERE usr_type='SU' and usr_cd<>29
  group by usr_nm,usr_cd,usr_sec_code

  )
END

推荐答案

使用SQLCommand.TI你的asp.net应用程序中的meOut = 0，你编写了调用你的SP的方法，

Use SQLCommand.TImeOut =0 in your asp.net application, where you written the method for calling your SP,

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