Asp.Net Gives a Timeout Error While Running a Stored Procedure

Question

Hello Friends,

I am using asp.net, .NET 3.5, C#, and SQL Server Express 2005.

I have created a stored procedure in SQL, and when I run SP from SQL server it takes less than 1 second to return results. I have also tried that query in query analyzer and it also gives me results in less than 1 second. But when I try to call this SP from .NET (C#), it takes a long time, and then gives a timeout error.

Here is the code I am using to call the stored procedure :

SqlConnection con = new SqlConnection();

con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
con.Open();

SqlCommand command = new SqlCommand("spReport_SiteUsage_KP", con);

command.CommandType = CommandType.StoredProcedure;

command.Parameters.Add(new SqlParameter("@fromDate", SqlDbType.DateTime));

command.Parameters.Add(new SqlParameter("@toDate", SqlDbType.DateTime));

command.Parameters[0].Value = Convert.ToDateTime(DatePicker1.SelectedDate.ToShortDateString());

command.Parameters[1].Value = DatePicker2.SelectedDate;

int i = command.ExecuteNonQuery();

con.Close();

Is any body have any idea please ??? It's very urgent ..

Store Procedure : set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[spReport_SiteUsage_KP] @fromDate datetime = null, @toDate datetime = null AS truncate table dbo.RPT_SiteUsage

IF (@FromDate is not null and @ToDate is not null) --Hourly Report, grouped by hour Begin

insert into RPT_SiteUsage 
select '' as ReportType, 
'Site Usage for '+ datename(mm,@fromDate)+' '+datename(dd,@fromDate)+', '+datename(yy,@fromDate) + 
' To '+datename(mm,@toDate)+' '+datename(dd,@toDate)+', '+datename(yy,@toDate) as ReportTitle,
min(@fromDate) as FromDate,max(@toDate) as ToDate,
isnull(count(s.SessionId),0) VisitsTotal,
isnull(count(distinct(s.cookieid)),0) VisitsUnique,
isnull(sum(PagesVisited),0) PageViews, 
isnull(round(avg(convert(decimal(10,2),PagesVisited)),2),0) PagePerVisit,  
isnull(min(PagesVisited),0) MinNoPageViews, 
isnull(max(PagesVisited),0) MaxNoPageViews,
isnull(round(avg(convert(decimal(10,2),TimeInSiteMinutes)),2),0) AvgTimeInSite, 
isnull(min(TimeInSiteMinutes),0) MinTimeSpent, 
isnull(max(TimeInSiteMinutes),0) MaxTimeSpent, 
isnull(sum(NewPeople),0) as NewVisitors
from
dbo.UMM_UserAction ua inner join dbo.UMM_Session s on ua.SessionId=s.Sessionid
left join
 (select ua.sessionId, datediff(ss,min(Actiondate),max(Actiondate))/60 TimeInSiteMinutes
  from dbo.UMM_UserAction ua
  where ActionDate between @fromDate and @toDate
  group by ua.sessionid
  ) sessionTime on ua.sessionId = sessionTime.sessionid
left join
 (select ua.sessionId, 0 as NewPeople
 from dbo.UMM_UserAction ua 
  inner join dbo.UMM_Session s on ua.SessionId=s.SessionId
  inner join dbo.UMM_Cookie c on s.CookieId=c.CookieId
  where ua.actiondate< @fromDate --this is the from date
 group by UserId,ua.sessionId 
  ) Old on ua.sessionId = Old.sessionid
left join
 (select ua.sessionId,count(distinct(uaP.PageEntryId)) as PagesVisited
 from dbo.UMM_UserAction ua,
 dbo.UMM_UserActionPageReview uaP 
 where ua.UserActionId=uaP.UserActionId
 and ActionDate between @fromDate and @toDate
 group by ua.sessionId
 )pVisited on ua.sessionId = pVisited.sessionId
where ActionDate between @fromDate and @toDate

IF (select count(*) from RPT_SiteUsage)=0
 insert into RPT_SiteUsage 
 select '(1 day)' as ReportType, 
 'Site Usage for '+datename(mm,@fromDate)+' '+datename(dd,@fromDate)+', '+datename(yy,@fromDate) + 
 ' To '+datename(mm,@toDate)+' '+datename(dd,@toDate)+', '+datename(yy,@toDate) as ReportTitle,
 min(@fromDate) as FromDate,max(@toDate) as ToDate,
 0 as VisitsTotal,
 0 as VisitsUnique,
 0 as PageViews, 
 0 as PagePerVisit,  
 0 as MinNoPageViews, 
 0 as MaxNoPageViews,
 0 as AvgTimeInSite, 
 0 as MinTimeSpent, 
 0 as MaxTimeSpent, 
 0 as NewVisitors

END

Answer 1

Well - I would say there's an error in your connection string. Please check it.

Answer 2

If it takes long before the query returns an error, there is probably something wrong with your connection (string).

Answer 3

It could be the fun issue with a bad query plan cached on the proc. Especially if the guts of the proc just as a Query in Query Analyzer runs fine. Check this link out on how to resolve the situation: http://www.mssqltips.com/tip.asp?tip=1304

Answer 4

Another idea, the TimeOut of each SqlCommand is also controlled individually, so, you can control it with CommandTimeOut property.

command.CommandTimeout = 120;

However, I would check the execution plan to see where is it wasting or hogging db resources, I suggest this just for experiment, not on production.