烦人的存储过程执行超时问题!解决了可以另外加分!!!
代码如下,在if (error="") 设置断点,当执行到30秒的时候就停止,error="超时时间已到。在操作完成之前超时时间已过或服务器未响应。"
但是手工在sql query执行不会超时(执行过程大概要8分钟左右),在非本机的sql query中执行也不会超时。
在数据库连接字符串中timeout=40000;
页面server.timeout=4000;
private void Button2_ServerClick(object sender, System.EventArgs e)
{
//统计数据
sqltext = "DELETE FROM PCheckResultStatTwo ";
sqltext += "WHERE CheckID='"+Request["select"].ToString()+"'";
mypublic.QueryExec(out error,Session["SqlConnectionString"].ToString(),sqltext);
//procedure
Server.ScriptTimeout = 4000;
BuildCheckStatTwo(out error,Request["select"].ToString());
if (error == "")
{
//successful
//Response.Write("<script>alert('统计数据成功!');</script>");
Response.Redirect("./MCheckResultStatTwo.aspx?CheckID="+Request["select"].ToString());
}
}
public void BuildCheckStatTwo(out string error,string values)
{
Server.ScriptTimeout = 4000;
//key = "";
error = "";
//sqlconnection
SqlConnection cn = new SqlConnection(Session["SqlConnectionString"].ToString());
//sqlcommand
SqlCommand cm = new SqlCommand("BuildCheckStatTwo",cn);
cm.CommandType = CommandType.StoredProcedure;
//output parameters
cm.Parameters.Add("@CheckID",SqlDbType.VarChar,16);
cm.Parameters["@CheckID"].Value=values;
cm.Parameters["@CheckID"].Direction = ParameterDirection.Input;
try
{
//open connection
cn.Open();
//executereader()
//SqlDataReader dr = cm.ExecuteReader();
cm.ExecuteNonQuery();
}
catch(Exception err)
{
error=err.Message.ToString() ; }
finally
{
cm.Dispose();
cn.Close();
cn.Dispose();
}
}
另外我觉得这个存储过程的执行时间太长了,记录只有10000多条,却要执行8分钟左右。能否优化一下。存储过程代码如下:
CREATE Procedure BuildCheckStatTwo @CheckID varchar(16) --统计结果的存储过程
As
Declare @CheckResultStatTwoID varchar(16)
Declare @BeCheckedUserID varchar(16)
Declare @CheckTableItemID varchar(16)
Declare @CheckUserClassID varchar(16)
Declare @CheckTableItemOptionID varchar(16)
Declare @CheckTableItemOptionScore int
Declare @ResultCount varchar(16)
Declare BeCheckedUser Cursor For Select BeCheckedUserID From PBeCheckedUserInfo Where CheckID=@CheckID
Declare CheckTableItem Cursor For Select ItemID From PCheckTableItem
Where TypeID In ( Select TypeID From PCheckTableType
Where TitleID=( Select TitleID From PCheckIns Where CheckID=@CheckID ) )
Declare CheckUserClass Cursor For Select CheckUserClassID From PCheckUserClass Where CheckID=@CheckID
--Delete From PcheckResultStatTwo Where CheckID=@CheckID
Open BeCheckedUser
Fetch Next From BeCheckedUser Into @BeCheckedUserID
While @@Fetch_status=0
Begin
Open CheckTableItem
Fetch Next From CheckTableItem Into @CheckTableItemID
While @@Fetch_status=0
Begin
Declare CheckTableItemOption Cursor For Select LevelID,LevelScore From PCheckTableItemOption Where ItemID=@CheckTableItemID
Open CheckUserClass
Fetch Next From CheckUserClass Into @CheckUserClassID
While @@Fetch_status=0
Begin
Open CheckTableItemOption
Fetch Next From CheckTableItemOption Into @CheckTableItemOptionID,@CheckTableItemOptionScore
While @@Fetch_status=0
Begin
Select @ResultCount=Count(b.ItemScore) From PCheckTableInsTwo a,PCheckResultTwo b
Where b.CheckInsTwoID=a.CheckInsTwoID And a.CheckID=@CheckID And a.BeCheckedUserID=@BeCheckedUserID
And a.CheckUserClassID=@CheckUserClassID And b.ItemID=@CheckTableItemID And b.ItemScore=@CheckTableItemOptionScore
--Exec GetPrimaryKey @CheckResultStatTwoID output
insert into PCheckResultStatTwo (CheckID,BeCheckedUserID,CheckTableItemID,CheckUserClassID,CheckTableItemOptionID,ResultCount) values
(@CheckID,@BeCheckedUserID,@CheckTableItemID,@CheckUserClassID,@CheckTableItemOptionID,@ResultCount)
Fetch Next From CheckTableItemOption Into @CheckTableItemOptionID,@CheckTableItemOptionScore
End
Close CheckTableItemOption
Fetch Next From CheckUserClass Into @CheckUserClassID
End
Close CheckUserClass
--全部userclass类别
Open CheckTableItemOption
Fetch Next From CheckTableItemOption Into @CheckTableItemOptionID,@CheckTableItemOptionScore
While @@Fetch_status=0
Begin
Select @ResultCount=Count(b.ItemScore) From PCheckTableInsTwo a,PCheckResultTwo b
Where b.CheckInsTwoID=a.CheckInsTwoID And a.CheckID=@CheckID And a.BeCheckedUserID=@BeCheckedUserID
And b.ItemID=@CheckTableItemID And b.ItemScore=@CheckTableItemOptionScore
--Exec GetPrimaryKey @CheckResultStatTwoID output
insert into PCheckResultStatTwo (CheckID,BeCheckedUserID,CheckTableItemID,CheckUserClassID,CheckTableItemOptionID,ResultCount) values
(@CheckID,@BeCheckedUserID,@CheckTableItemID,'0000000000000000',@CheckTableItemOptionID,@ResultCount)
Fetch Next From CheckTableItemOption Into @CheckTableItemOptionID,@CheckTableItemOptionScore
End
Close CheckTableItemOption
--全部类别end
Deallocate CheckTableItemOption
Fetch Next From CheckTableItem Into @CheckTableItemID
End
Close CheckTableItem
Fetch Next From BeCheckedUser Into @BeCheckedUserID
End
Close BeCheckedUser
Deallocate BeCheckedUser
Deallocate CheckTableItem
Deallocate CheckUserClass
GO