我写的两个存储过程,其中都是传开始时间和结束时间两个值.在sql server 中全好用,一般查询只需2秒左右,但在.net中一个好用,而别一个就不好用,出现超时问题.研究两天都没想明白,请指点一个我哪里有问题了,先谢谢了.部分程序如下:
DataSet ds;
ds= SqlHelper.ExecuteDataset(connstr,"KQ_GetBrushCardDetail1",BeginTimeText,EndTimeText);
grdDetail.DataSource = ds;
grdDetail.DataBind();
一运行就出现超时时间已到.而这个记录并不多,只有几十条记录.其中把ds换成下面的这个一问题都没有,而这里有几百条记录.
ds= SqlHelper.ExecuteDataset(connstr,"KQ_StatisticsParsedataByDuty1",BeginTimeText,EndTimeText);
下面分别是这两个存储过程
CREATE procedure KQ_GetBrushCardDetail1
@BeginTime varchar(20),
@EndTime varchar(20)--with encryption
as
select EmployeeID,
d.CardID,
dw.DeptName,
t.TypeOfWork,
Name,
CardTime,
bc.Class,
d.OffdutyFlag,
d.MatchFlag,
ParseID,
p.Place
from kq_detail d inner join vw_kqcodefull c
on d.CardID = c.CardID inner join KQ_Dept dw
on c.DeptID = dw.DeptID inner join KQ_TypeOfWork t
on c.TypeOfWorkID = t.TypeOfWorkID left join KQ_Class bc
on d.ClassID = bc.ClassID left join KQ_Place p
on d.PlaceID = p.PlaceID
where c.EmployeeID='333' and
d.CardTime between @BeginTime and @EndTime and
(isnull(MatchFlag*1,2)=-1 or -1 = -1)
order by CardTime
GO
CREATE procedure KQ_StatisticsParsedataByDuty1
@BeginTime varchar(20),
@EndTime varchar(20)
--with encryption
as
select
c.EmployeeID,
c.CardID,
c.DeptName,
c.Typeofwork,
c.Name,
p.Qty,
p.TotalHours
--p.TotalHours/p.Qty as AverageHours
from vw_kqcode c inner join
(select EmployeeID,
sum(Workdays) as Qty,
sum(minutes)/60 as TotalHours
from kq_parsedata
where OnTime between @BeginTime and @EndTime
group by EmployeeID) p on c.EmployeeID = p.EmployeeID
where DutykindID = 1
GO错误提示:超时时间已到。在操作完成之前超时时间已过或服务器未响应。
说明: 执行当前 Web 请求期间,出现未处理的异常。请检查堆栈跟踪信息,以了解有关该错误以及代码中导致错误的出处的详细信息。 异常详细信息: System.Data.SqlClient.SqlException: 超时时间已到。在操作完成之前超时时间已过或服务器未响应。源错误:
行 95: {
行 96: DataSet ds;
行 97: ds= SqlHelper.ExecuteDataset(connstr,"KQ_GetBrushCardDetail1",BeginTimeText,EndTimeText);
行 98:
行 99: //ds= SqlHelper.ExecuteDataset(connstr,"KQ_StatisticsParsedataByPy1",BeginTimeText,EndTimeText);
源文件: e:\kq\tflb.aspx.cs 行: 97 堆栈跟踪:
[SqlException: 超时时间已到。在操作完成之前超时时间已过或服务器未响应。]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) +44
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +5
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +38
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters)
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters)
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String connectionString, String spName, Object[] parameterValues)
Aizhe.Applications.Kaoqin.tflb.bindData() in e:\kq\tflb.aspx.cs:97
Aizhe.Applications.Kaoqin.tflb.Button1_ServerClick(Object sender, EventArgs e) in e:\kq\tflb.aspx.cs:118
System.Web.UI.HtmlControls.HtmlInputButton.OnServerClick(EventArgs e) +108
System.Web.UI.HtmlControls.HtmlInputButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +138
System.Web.UI.Page.ProcessRequestMain() +1277
DataSet ds;
ds= SqlHelper.ExecuteDataset(connstr,"KQ_GetBrushCardDetail1",BeginTimeText,EndTimeText);
grdDetail.DataSource = ds;
grdDetail.DataBind();
一运行就出现超时时间已到.而这个记录并不多,只有几十条记录.其中把ds换成下面的这个一问题都没有,而这里有几百条记录.
ds= SqlHelper.ExecuteDataset(connstr,"KQ_StatisticsParsedataByDuty1",BeginTimeText,EndTimeText);
下面分别是这两个存储过程
CREATE procedure KQ_GetBrushCardDetail1
@BeginTime varchar(20),
@EndTime varchar(20)--with encryption
as
select EmployeeID,
d.CardID,
dw.DeptName,
t.TypeOfWork,
Name,
CardTime,
bc.Class,
d.OffdutyFlag,
d.MatchFlag,
ParseID,
p.Place
from kq_detail d inner join vw_kqcodefull c
on d.CardID = c.CardID inner join KQ_Dept dw
on c.DeptID = dw.DeptID inner join KQ_TypeOfWork t
on c.TypeOfWorkID = t.TypeOfWorkID left join KQ_Class bc
on d.ClassID = bc.ClassID left join KQ_Place p
on d.PlaceID = p.PlaceID
where c.EmployeeID='333' and
d.CardTime between @BeginTime and @EndTime and
(isnull(MatchFlag*1,2)=-1 or -1 = -1)
order by CardTime
GO
CREATE procedure KQ_StatisticsParsedataByDuty1
@BeginTime varchar(20),
@EndTime varchar(20)
--with encryption
as
select
c.EmployeeID,
c.CardID,
c.DeptName,
c.Typeofwork,
c.Name,
p.Qty,
p.TotalHours
--p.TotalHours/p.Qty as AverageHours
from vw_kqcode c inner join
(select EmployeeID,
sum(Workdays) as Qty,
sum(minutes)/60 as TotalHours
from kq_parsedata
where OnTime between @BeginTime and @EndTime
group by EmployeeID) p on c.EmployeeID = p.EmployeeID
where DutykindID = 1
GO错误提示:超时时间已到。在操作完成之前超时时间已过或服务器未响应。
说明: 执行当前 Web 请求期间,出现未处理的异常。请检查堆栈跟踪信息,以了解有关该错误以及代码中导致错误的出处的详细信息。 异常详细信息: System.Data.SqlClient.SqlException: 超时时间已到。在操作完成之前超时时间已过或服务器未响应。源错误:
行 95: {
行 96: DataSet ds;
行 97: ds= SqlHelper.ExecuteDataset(connstr,"KQ_GetBrushCardDetail1",BeginTimeText,EndTimeText);
行 98:
行 99: //ds= SqlHelper.ExecuteDataset(connstr,"KQ_StatisticsParsedataByPy1",BeginTimeText,EndTimeText);
源文件: e:\kq\tflb.aspx.cs 行: 97 堆栈跟踪:
[SqlException: 超时时间已到。在操作完成之前超时时间已过或服务器未响应。]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) +44
System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +5
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +38
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters)
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters)
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String connectionString, String spName, Object[] parameterValues)
Aizhe.Applications.Kaoqin.tflb.bindData() in e:\kq\tflb.aspx.cs:97
Aizhe.Applications.Kaoqin.tflb.Button1_ServerClick(Object sender, EventArgs e) in e:\kq\tflb.aspx.cs:118
System.Web.UI.HtmlControls.HtmlInputButton.OnServerClick(EventArgs e) +108
System.Web.UI.HtmlControls.HtmlInputButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +138
System.Web.UI.Page.ProcessRequestMain() +1277
d.CardID,
dw.DeptName,
t.TypeOfWork,
Name,
CardTime,
bc.Class,
d.OffdutyFlag,
d.MatchFlag,
ParseID,
p.Place
from kq_detail d inner join vw_kqcodefull c
on d.CardID = c.CardID inner join KQ_Dept dw
on c.DeptID = dw.DeptID inner join KQ_TypeOfWork t
on c.TypeOfWorkID = t.TypeOfWorkID left join KQ_Class bc
on d.ClassID = bc.ClassID left join KQ_Place p
on d.PlaceID = p.PlaceID
where c.EmployeeID='333' and
d.CardTime between @BeginTime and @EndTime and
(isnull(MatchFlag*1,2)=-1 or -1 = -1)
order by CardTime
可能是运行时间太长了
-----------------------------------------------------------------------
我也遇到过这样的问题,主在原因是因为你的视图条件太多了,系统执行时间太长了,造成系统超时,你可以先将此视图内容放入数据表中,然后再从表中取数据,你试试,我以前是这样的.select EmployeeID,
d.CardID,
dw.DeptName,
t.TypeOfWork,
Name,
CardTime,
bc.Class,
d.OffdutyFlag,
d.MatchFlag,
ParseID,
p.Place into aaa
from kq_detail d inner join vw_kqcodefull c
on d.CardID = c.CardID inner join KQ_Dept dw
on c.DeptID = dw.DeptID inner join KQ_TypeOfWork t
on c.TypeOfWorkID = t.TypeOfWorkID left join KQ_Class bc
on d.ClassID = bc.ClassID left join KQ_Place p
on d.PlaceID = p.PlaceID
where c.EmployeeID='333' and
d.CardTime between @BeginTime and @EndTime and
(isnull(MatchFlag*1,2)=-1 or -1 = -1)
order by CardTime