Click here to Skip to main content
15,868,141 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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

SQL
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
Posted

1 solution

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

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