新建网站 超时总是

[hailiu123]

System.Web.HttpUnhandledException (0x80004005): Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at DBUtility.MSSqlHelpler.ExecuteDataSet(String connectionString, CommandType cmdType, String cmdText, IDbDataParameter[] parms) in E:\源代码\20140925\前台\DBUtility\MSSqlHelpler.cs:line 84
at DAL.ProductInfoDAL.ListByPage(Int32 SubShopID, String SearchSQL, Int32 PageIndex, Int32 PageSize, String Field, String SortField, Int32& TotalRecord, Int32& TotalPage) in E:\源代码\20140925\前台\DAL\ProductInfoDAL.cs:line 371
at View.ProductInfoView.ListByPage(String SubShopURL, String SearchSQL, Int32 PageIndex, Int32 PageSize, String Field, String SortField, Int32& TotalRecord, Int32& TotalPage) in E:\源代码\20140925\前台\View\ProductInfoView.cs:line 179
at Web.List.Page_Load(Object sender, EventArgs e) in E:\源代码\20140925\前台\Web\Programs\List.aspx.cs:line 155
at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)
at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)
at System.Web.UI.Control.OnLoad(EventArgs e)
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.HandleError(Exception e)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest()
at System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context)
at System.Web.UI.Page.ProcessRequest(HttpContext context)
at ASP.programs_list_aspx.ProcessRequest(HttpContext context) in c:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\Temporary ASP.NET Files\root\4ef738d3\726fbed6\App_Web_ovduguwq.3.cs:line 0
at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

请教下 应该怎么调整 没找到 是哪里的问题 请教下 应该怎么 改

[娃都会打酱油了]

DAL.ProductInfoDAL.ListByPage执行超时，设置下DbCommand的TimeOut

[exception92]

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. -> 服务端超时，没有响应。 放点代码卡哪款

[rtdb]

SqlCommand.ExecuteReader 超时，应该是某个查询的结果数据量太大了

[hailiu123]

DBUtility.MSSqlHelpler.ExecuteDataSet(String connectionString, CommandType cmdType, String cmdText, IDbDataParameter[] parms) in E:\源代码\20140925\前台\DBUtility\MSSqlHelpler.cs:line 84 /// <summary> /// 查询数据并返回DataSet /// </summary> /// <param name="connectionString">sql连接语句</param> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="parms"></param> /// <returns></returns> public override DataSet ExecuteDataSet(string connectionString, CommandType cmdType, string cmdText, IDbDataParameter[] parms) { using (SqlConnection conn = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, conn, null, cmdType, cmdText, parms); DataSet ds = new DataSet(); SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; da.Fill(ds); return ds; } } at DAL.ProductInfoDAL.ListByPage(Int32 SubShopID, String SearchSQL, Int32 PageIndex, Int32 PageSize, String Field, String SortField, Int32& TotalRecord, Int32& TotalPage) in E:\源代码\20140925\前台\DAL\ProductInfoDAL.cs:line 371 /// <summary> /// 商品信息列表-翻页 /// </summary> /// <param name="SQL"></param> /// <param name="PageIndex"></param> /// <param name="PageSize"></param> /// <param name="Field"></param> /// <param name="SortField"></param> /// <param name="TotalRecord"></param> /// <param name="TotalPage"></param> /// <returns></returns> public List<ProductInfoModel> ListByPage(int SubShopID, string SearchSQL, int PageIndex, int PageSize, string Field, string SortField, ref int TotalRecord, ref int TotalPage) { SqlParameter[] param = PageViewParam(); param[0].Value = string.Format(m_ListByPage, SearchSQL, SubShopID.ToString()); param[1].Value = PageIndex; param[2].Value = PageSize; param[3].Value = Field; param[4].Value = SortField; DataTable dt = m_DB.ExecuteDataSet(m_DB.ConnectionString, CommandType.StoredProcedure, m_PageView, param).Tables[0]; TotalRecord = DataConvert.ConvertInt(param[5].Value); TotalPage = DataConvert.ConvertInt(param[6].Value); List<ProductInfoModel> lst = new List<ProductInfoModel>(); foreach (DataRow row in dt.Rows) { lst.Add(new ProductInfoModel(row)); } return lst; } at View.ProductInfoView.ListByPage(String SubShopURL, String SearchSQL, Int32 PageIndex, Int32 PageSize, String Field, String SortField, Int32& TotalRecord, Int32& TotalPage) in E:\源代码\20140925\前台\View\ProductInfoView.cs:line 179 /// <summary> /// 商品信息列表-翻页 /// </summary> /// <param name="SQL"></param> /// <param name="PageIndex"></param> /// <param name="PageSize"></param> /// <param name="Field"></param> /// <param name="SortField"></param> /// <param name="TotalRecord"></param> /// <param name="TotalPage"></param> /// <returns></returns> public static List<ProductInfoModel> ListByPage(string SubShopURL, string SearchSQL, int PageIndex, int PageSize, string Field, string SortField, ref int TotalRecord, ref int TotalPage) { string _CacheKey = string.Format(SubShopURL + "-" + CacheKey + "ListByPage-{0}-{1}-{2}-{3}", Common.SecurityWork.MD5Encryp.Md5(SearchSQL, 32), PageIndex.ToString(), Field, Common.SecurityWork.MD5Encryp.Md5(SortField, 32)); string _TotalPageKey = _CacheKey + "-TotalPage"; string _TotalRecordKey = _CacheKey + "-TotalRecord"; ///////////////////////////////////////////////////// int SubShopID = m_DAL_SystemSite.GetSystemSite(SubShopURL).SubShop_ID; List<ProductInfoModel> lst = CacheBase.GetCacheValue(_CacheKey) as List<ProductInfoModel>; TotalPage = DataConvert.ConvertInt(CacheBase.GetCacheValue(_TotalPageKey), 0); TotalRecord = DataConvert.ConvertInt(CacheBase.GetCacheValue(_TotalRecordKey), 0); if (lst == null) { lst = m_DAL.ListByPage(SubShopID, SearchSQL, PageIndex, PageSize, Field, SortField, ref TotalRecord, ref TotalPage); CacheBase.AddCache(_CacheKey, lst); CacheBase.AddCache(_TotalPageKey, TotalPage); CacheBase.AddCache(_TotalRecordKey, TotalRecord); } return lst; } at Web.List.Page_Load(Object sender, EventArgs e) in E:\源代码\20140925\前台\Web\Programs\List.aspx.cs:line 155 int TotalRecord = 0; int TotalPage = 0; int PageSize = 50; PageControl1.PageIndex = DataConvert.ConvertInt(DataRequest.GetQueryString("Page"), 1); List<ProductInfoModel> lst = ProductInfoView.ListByPage(URL_Authority, SearchStr, PageControl1.PageIndex, PageSize, "Product_ID", SortOrder, ref TotalRecord, ref TotalPage); rptList.DataSource = lst; rptList.DataBind();

[hailiu123]

分页存储过程 ALTER PROCEDURE [dbo].[sp_Pagination] ( @SQL NVarChar(max), --SQL语句 @Page Int, --当前第几页 @Pagesize Int, --每页大小 @Field VarChar(100), --检索字段 @SortField VarChar(100), --排序字段 @TotalRecord Int OutPut, --输出总记录数 @TotalPage Int OutPut --输出总页数 ) AS ---定义变量------------------------------------------------ Declare @Str NVarChar(max) Declare @Alls Int ----------------------------------------------------------- ------------------------------------------------------------------------------------------------ --计算总记录条数和页数 ------------------------------------------------------------------------------------------------ Set @Str = 'Select @All = Count(*) From ('+ @SQL + ') t' Exec sp_ExecuteSQL @Str, N'@All Int OutPut', @TotalRecord OutPut If @TotalRecord % @Pagesize = 0 Set @TotalPage = @TotalRecord / @Pagesize Else Set @TotalPage = @TotalRecord / @Pagesize + 1 If @Page < 1 set @Page = 1 If @Page >= @TotalPage Set @Page = @TotalPage If @Page <= 1 Begin Set @Str = 'Select Top ' + Cast(@Pagesize as VarChar(10)) + ' * From (' + @SQL + ') t Order By ' + @SortField End Else Begin --返回记录集 Set @Str = 'Select Top ' + cast(@Pagesize as VarChar(10)) + ' * From (' + @SQL + ') t Where ' + @Field + ' Not In (Select Top ' + Cast((@Page - 1) * @Pagesize as VarChar(10)) + ' ' + @Field + ' From (' + @SQL + ') t9 Order By ' + @SortField + ') Order By ' + @SortField End Print @Str Exec sp_ExecuteSQL @Str

[rtdb]

这个分页也太那啥了，完全没考虑性能啊，表稍大就肯定回不来了

[rtdb]

哈哈笑死了， 为了分页， SELECT TOP 1000 not in SELECT TOP 900 那怕你 SELECT TOP 1000 然后扔掉前900个数据也比这个要快10倍好吧 正经说，请用ROW_NUMBER分页.

[hailiu123]

别人的代码 。。。。 ，能帮忙 弄个 分页存储过程吗

[暗尘掩月]

数据库 和网站 在同一服务器？

[hailiu123]

ALTER PROCEDURE [dbo].[PROCE_PAGECHANGE] ( @SQL NVarChar(max), --表名,sql语句 @Page int , --指定当前为第几页 @PageSize int, --每页多少条记录 @Field varchar(500) =N'', --条件语句(不用加where) @SortField varchar(200), --排序字段(必须!支持多字段不用加order by) @TotalRecord int output, --返回总记录数 @TotalPage int output ) AS BEGIN --处理开始点和结束点 Declare @StartRecord int; Declare @EndRecord int; Declare @TotalCountSql nvarchar(500); Declare @SqlString nvarchar(max); Declare @ReFieldsStr varchar(200) ; --字段名(全部字段为 *) set @SQL=N' ('+@SQL+') as tb2 ' set @ReFieldsStr='*' set @StartRecord = (@Page-1)*@PageSize + 1 set @EndRecord = @StartRecord + @PageSize - 1 SET @TotalCountSql= N'select @TotalRecord = count(*) from ' + @SQL;--总记录数语句 SET @SqlString = N'(select row_number() over (order by '+ @SortField +') as rowId,'+@ReFieldsStr+' from '+ @SQL;--查询语句 -- --IF (@Field! = '' or @Field!=null) -- BEGIN -- SET @TotalCountSql=@TotalCountSql + ' where '+ @Field; -- SET @SqlString =@SqlString+ ' where '+ @Field; -- END --第一次执行得到 --IF(@TotalRecord is null) -- BEGIN EXEC sp_ExecuteSQL @totalCountSql,N'@TotalRecord int out',@TotalRecord output;--返回总记录数 -- END ----执行主语句 set @SqlString ='select * from ' + @SqlString + ') as t where rowId between ' + ltrim(str(@StartRecord)) + ' and ' + ltrim(str(@EndRecord)); Print @SqlString EXEC sp_ExecuteSQL @SqlString END 这是改的 存储过程 执行 出现以下 问题 请帮忙看下 消息 102，级别 15，状态 1，第 7 行 'Jo' 附近有语法错误。 select * from (select row_number() over (order by product_id desc) as rowId,* from (Select a.Product_ID, a.Product_Name,a.Product_BuyNum, b.Org_Price, b.Sale_Price, c.Img_Small, c.Img_Middle,isnull((select SubShop_Price from subshop_product where subshop_id=0 and product_id=a.product_id),b.Sug_Price) as SubShop_Price, b.Sug_Price,a.Product_SeeNum,a.AddTime,a.orderid From Product_Info a left join Product_Price b On a.Product_ID = b.Product_ID left join Product_Detail c On a.Product_ID = c.Product_ID Inner Join SubShop_ProductRight d on a.Class_ID = d.Class_ID AND d.SubShop_ID=0 Where ((a.SubShop_ID is null or a.SubShop_ID=-1) or ((a.SubShop_ID=0 or a.Is_AllShow=0 ) and Is_Audit=0)) and a.IsHidden = 0 And a.IsDelete = 0 And a.Product_ID In (Select Product_ID From Product_mClass Where Class_ID In (80) Group By Product_ID) and a.Product_ID not in (select product_id from SubShop_Product where IsHidden=1 and SubShop_ID=0) AND d.IsStart = 0 and (a.Product_Type = 0 or a.Product_Type is null ) ) as tb2 ) as t where rowId between 1 and 25 (25 行受影响) (1 行受影响) (1 行受影响) 复制 上面的sql 语句 能正常执行的