Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: ASP.NETSQL-Server
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
Posted 20-Nov-12 23:16pm

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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

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

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 Sergey Alexandrovich Kryukov 457
1 OriginalGriff 430
2 Shai Vashdi 240
3 Emre Ataseven 165
4 Peter Leow 146
0 Sergey Alexandrovich Kryukov 8,944
1 OriginalGriff 5,280
2 Peter Leow 3,985
3 Maciej Los 3,535
4 Abhinav S 3,218


Advertise | Privacy | Mobile
Web02 | 2.8.140415.2 | Last Updated 6 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Use
Layout: fixed | fluid