Asp.Net在运行存储过程时给出超时错误 [英] Asp.Net Gives a Timeout Error While Running a Stored Procedure

问题描述

我正在使用asp.net，.NET 3.5，C#和SQL Server Express 2005.

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

我已经在SQL中创建了一个存储过程，当我从SQL Server运行SP时，不到1秒即可返回结果.我还尝试在查询分析器中查询该查询，它还可以在不到1秒的时间内为我提供结果.但是，当我尝试从.NET(C#)调用此SP时，它花费了很长时间，然后给出了超时错误.

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();

存储过程:

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

推荐答案

另一个想法，每个SqlCommand的TimeOut也可以单独控制，因此，可以使用CommandTimeOut属性对其进行控制.

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.

这篇关于Asp.Net在运行存储过程时给出超时错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！