aspnetpager的分页存储过程问题?

Question

我有三张表关联,取数据,数据量共为537823条记录，
现用了aspnetpager 6.0版本的分页控件，
现在分页超时，无法打开,
我想请问一下什么原因？这个分页不是很快的吗？是不是我的存储过程有问题呢？
我的存储过程如下：（其中V_memlist为视图）CREATE PROCEDURE sp_GetMemberList
(
--@ColList  VARCHAR(4000)        --显示的字段
--@Table   VARCHAR(4000)           --查询的表,from后面的语句
--@Where  VARCHAR(4000)          --查询条件,where后面的语句
--@Sort nVARCHAR(255)='',                     --排序条件,不带表名的字段名

@PageSize INT, --每页记录数
@PageIndex INT, --当前页码

--@ID nVARCHAR(255)='', --ID字段，例如"memberid"
--@ID_WithTableName    nVARCHAR(255)='', --带有表名前缀的ID字段名,例如"t_member_member.memberid"
@DoCount int=0, --是否获取记录数 

@ParentID                                   varchar(10),
@tbxKeyWord VARCHAR(50),                            --查询参数
@ddlKeyField VARCHAR(20), --查询参数
@ddlCheckStatus      VARCHAR(10),                           --^
@ChkNumber INT   ,                                             --^
@StartTime VARCHAR(20),                                     --^
@EndTime VARCHAR(20),                                       --^
@OutCount                                 int  output
)AS 
DECLARE @Sql       nVARCHAR(4000)
DECLARE @Where  VARCHAR(5000)        
DECLARE @Sort      VARCHAR(255)
DECLARE @ID        nVARCHAR(255) --ID字段，例如"memberid"
DECLARE @ID_WithTableName  nVARCHAR(255) --带有表名前缀的ID字段名,例如"t_member_member.memberid"
SET @Where = ' 1=1 '
SET @ID = '  ID '
SET @Sort = '  ID '
IF (@tbxKeyWord <> '')
BEGIN
IF(@ddlKeyField = 'cCode')
BEGIN
SET @Where = @Where + '  AND cCode  like ''%'+@tbxKeyWord+'%'''
END
ELSE
BEGIN
SET @Where = @Where + '  AND '+@ddlKeyField+'  like ''%'+@tbxKeyWord+'%'''
END
END
IF (@ddlCheckStatus <> '-1')
BEGIN
SET @Where = @Where + '  AND CheckStatus = '''+@ddlCheckStatus+''''
ENDIF(@ChkNumber = 1)
BEGIN
SET @Where = @Where + '  AND ( code1='' OR code1 IS NULL)'
END
IF(@StartTime <> '' AND @EndTime <> '')
BEGIN
SET @Where = @Where + '  AND BeginTime>='''+@StartTime+'''  and  EndTime <= '''+@EndTime+''''
ENDIF(@ParentID != '0')
BEGIN
SET @Where = @Where + '  AND  Cast(LevelID as varchar(20)) ='+@ParentID
ENDIF(@DoCount=1)
BEGIN
SET @Sql='SELECT COUNT(*)   AS countint  FROM V_memlist   WHERE  '+@Where 
--print (@Sql)
EXEC(@Sql)

END
ELSE
BEGIN
SET @Sql='SELECT *  FROM  V_memlist  WHERE   '+@Where 
SET @Sql='SELECT   TOP  '+CAST(@PageSize AS VARCHAR(20))+'    *  FROM ('+@Sql+') T WHERE T.'+@ID+' NOT IN (SELECT   TOP '+CAST((@PageSize*(@PageIndex-1)) AS VARCHAR(20))+'  '+@ID+'  FROM ('+@Sql+')  S  ORDER BY  '+@Sort+')   ORDER BY  '+@Sort
--print  (@sQL2)
EXEC (@Sql)
END
GO
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
分页过程如下：
private void btn_Search_Click(object sender, System.EventArgs e)
{

Bind_Count("0");
Bind_List("0");
}private void Bind_Count(string ParentID)
{
int CheckIs = 0;
if(ChkNumber.Checked)
CheckIs = 1; SqlParameter[] sps=new SqlParameter[11];

sps[0] = new SqlParameter("@PageSize", pager1.PageSize);
sps[1] = new SqlParameter("@PageIndex",pager1.StartRecordIndex);
sps[2] = new SqlParameter("@DoCount", 1);
sps[3] = new SqlParameter("@ParentID",ParentID);
sps[4] = new SqlParameter("@tbxKeyWord", tool.SqlEncode(txt_S_KeyWord.Value.Trim()));
sps[5] = new SqlParameter("@ddlKeyField",ddlKeyField.SelectedValue);
sps[6] = new SqlParameter("@ddlCheckStatus", ddlCheckStatus.SelectedValue);
sps[7] = new SqlParameter("@ChkNumber",CheckIs);
sps[8] = new SqlParameter("@StartTime",txt_S_StartTime.Text.Trim());
sps[9] = new SqlParameter("@EndTime",txt_S_EndTime.Text.Trim()); SqlParameter outPara = new SqlParameter("@OutCount",SqlDbType.Int,4);
outPara.Direction = ParameterDirection.Output;
sps[10] = outPara;
pager1.RecordCount = (int)Config.ExecuteScalar(Config.CONN_STRING,CommandType.StoredProcedure,"sp_GetMemberList",sps);
}
private void Bind_Count(string ParentID)
{
int CheckIs = 0;
if(ChkNumber.Checked)
CheckIs = 1; SqlParameter[] sps=new SqlParameter[11];

sps[0] = new SqlParameter("@PageSize", pager1.PageSize);
sps[1] = new SqlParameter("@PageIndex",pager1.StartRecordIndex);
sps[2] = new SqlParameter("@DoCount", 1);
sps[3] = new SqlParameter("@ParentID",ParentID);
sps[4] = new SqlParameter("@tbxKeyWord", tool.SqlEncode(txt_S_KeyWord.Value.Trim()));
sps[5] = new SqlParameter("@ddlKeyField",ddlKeyField.SelectedValue);
sps[6] = new SqlParameter("@ddlCheckStatus", ddlCheckStatus.SelectedValue);
sps[7] = new SqlParameter("@ChkNumber",CheckIs);
sps[8] = new SqlParameter("@StartTime",txt_S_StartTime.Text.Trim());
sps[9] = new SqlParameter("@EndTime",txt_S_EndTime.Text.Trim()); SqlParameter outPara = new SqlParameter("@OutCount",SqlDbType.Int,4);
outPara.Direction = ParameterDirection.Output;
sps[10] = outPara;
pager1.RecordCount = (int)Config.ExecuteScalar(Config.CONN_STRING,CommandType.StoredProcedure,"sp_GetMemberList",sps);
} private void Bind_List(string ParentID)
{
int CheckIs = 0;
if(ChkNumber.Checked)
CheckIs = 1; SqlParameter[] sps=new SqlParameter[11];

sps[0] = new SqlParameter("@PageSize", pager1.PageSize);
sps[1] = new SqlParameter("@PageIndex",pager1.StartRecordIndex);
sps[2] = new SqlParameter("@DoCount", 0);
sps[3] = new SqlParameter("@ParentID",ParentID);
sps[4] = new SqlParameter("@tbxKeyWord", tool.SqlEncode(txt_S_KeyWord.Value.Trim()));
sps[5] = new SqlParameter("@ddlKeyField",ddlKeyField.SelectedValue);
sps[6] = new SqlParameter("@ddlCheckStatus", ddlCheckStatus.SelectedValue);
sps[7] = new SqlParameter("@ChkNumber",CheckIs);
sps[8] = new SqlParameter("@StartTime",txt_S_StartTime.Text.Trim());
sps[9] = new SqlParameter("@EndTime",txt_S_EndTime.Text.Trim()); SqlParameter outPara = new SqlParameter("@OutCount",SqlDbType.Int,4);
outPara.Direction = ParameterDirection.Output;
sps[10] = outPara; DataSet ds = Config.ExecuteDataset(Config.CONN_STRING,CommandType.StoredProcedure,"sp_GetMemberList",sps);
dll_Member.DataSource=ds.Tables[0].DefaultView;
dll_Member.DataBind();


//pager1.RecordCount = ds.Tables[0].Rows.Count;
totalRecord.Text = pager1.RecordCount.ToString();
totalPage.Text = pager1.PageCount.ToString();
pager1.FirstPageText="首页";
pager1.LastPageText="尾页";
pager1.NextPageText="下一页";
pager1.PrevPageText="上一页";
pager1.InputBoxStyle="width:19px";
pager1.TextAfterInputBox="页";
pager1.TextBeforeInputBox="转到第";
}

Answer 1

先把你的PROC在查询分析器里试试

Answer 2

我试了，
exec sp_GetMemberList 30,1,1,0,'','name','-1','','','',''
exec sp_GetMemberList 30,1,0,0,'','name','-1','','','',''同时执行两条用10秒以上，单执行
exec sp_GetMemberList 30,1,0,0,'','name','-1','','','','' 用六秒

Answer 3

我的存储过程没有用临时表，用的是not in 的方试，

Answer 4

i think you'd better to use temp table and "not exists()" as key word. as i have no idea about your logic and table structure, so have no more help to you. you must test your SP with diff way, in order to improve its quality. good luck !

Answer 5

SQL Server 存储过程的分页，这个问题已经讨论过几年了，很多朋友在问我，所以在此发表一下我的观点
建立表：CREATE TABLE [TestTable] (
 [ID] [int] IDENTITY (1, 1) NOT NULL ,
 [FirstName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [LastName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [Country] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [Note] [nvarchar] (2000) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY]
GO 插入数据：(2万条，用更多的数据测试会明显一些)
SET IDENTITY_INSERT TestTable ONdeclare @i int
set @i=1
while @i<=20000
begin
    insert into TestTable([id], FirstName, LastName, Country,Note) values(@i, 'FirstName_XXX','LastName_XXX','Country_XXX','Note_XXX')
    set @i=@i+1
endSET IDENTITY_INSERT TestTable OFF -------------------------------------分页方案一：(利用Not In和SELECT TOP分页)
语句形式：
SELECT TOP 10 *
FROM TestTable
WHERE (ID NOT IN
          (SELECT TOP 20 id
         FROM TestTable
         ORDER BY id))
ORDER BY ID
SELECT TOP 页大小 *
FROM TestTable
WHERE (ID NOT IN
          (SELECT TOP 页大小*页数 id
         FROM 表
         ORDER BY id))
ORDER BY ID-------------------------------------分页方案二：(利用ID大于多少和SELECT TOP分页）
语句形式：
SELECT TOP 10 *
FROM TestTable
WHERE (ID >
          (SELECT MAX(id)
         FROM (SELECT TOP 20 id
                 FROM TestTable
                 ORDER BY id) AS T))
ORDER BY ID
SELECT TOP 页大小 *
FROM TestTable
WHERE (ID >
          (SELECT MAX(id)
         FROM (SELECT TOP 页大小*页数 id
                 FROM 表
                 ORDER BY id) AS T))
ORDER BY ID
-------------------------------------分页方案三：(利用SQL的游标存储过程分页)
create  procedure XiaoZhengGe
@sqlstr nvarchar(4000), --查询字符串
@currentpage int, --第N页
@pagesize int --每页行数
as
set nocount on
declare @P1 int, --P1是游标的id
 @rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页 
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize 
exec sp_cursorclose @P1
set nocount off其它的方案：如果没有主键，可以用临时表，也可以用方案三做，但是效率会低。
建议优化的时候，加上主键和索引，查询效率会提高。通过SQL 查询分析器，显示比较：我的结论是:
分页方案二：(利用ID大于多少和SELECT TOP分页）效率最高，需要拼接SQL语句
分页方案一：(利用Not In和SELECT TOP分页)   效率次之，需要拼接SQL语句
分页方案三：(利用SQL的游标存储过程分页)    效率最差，但是最为通用在实际情况中，要具体分析。CREATE PROCEDURE sp_GetPaginationData
@tablename varchar(200) ,   --表名
@strGetFields varchar(200) = '*',  --查询列名
@PageIndex int = 1 ,         --页码
@pageSize int = 15,         --页面大小
@strWhere  varchar(100) = '',     --查询条件
@strOrder varchar(100) = '', --排序列名
@intOrder bit = 0,        --排序类型  1为升序
@CountAll bigint output              --返回纪录总数用于计算页面数    
as
begin
declare @strSql varchar(500)  --主语句
declare @strTemp varchar(100) --临时变量
declare @strOrders varchar(50) --排序语句
declare @table varchar(70)
declare   @SQL   nvarchar(1000)
declare   @R bigintset   @SQL=  N'select @R=count(*) from  '+convert(nvarchar(200),@TableName)
exec  SP_EXECUTESQL   @SQL,  N' @R BIGINT OUTPUT',  @R OUTPUT
set   @CountAll=  @R
if @intOrder = 0
begin
    --为0是升序
    set @strTemp = '>(select max'
    set @strOrders =  ' order by  '+@strOrder+' asc '
end
else
begin
    --否则为降序
    set @strTemp = '<(select min'
    set @strOrders = ' order by  '+@strOrder+' desc '
end
if @PageIndex =1        --第一页直接读出纪录
begin
    if @strWhere = ''
    begin
         set @strSql = 'select top '+str(@pageSize)+' '+@strGetFields+' from '+@tablename+' '+@strOrders
     end
    else  
    begin
         set @strSql = 'select top '+str(@pageSize)+' '+@strGetFields+ ' from '+@tablename+' where '+@strWhere+' '+@strOrders
    end
end
else
begin
    set @strSql = 'select top'+str(@pageSize)+' '+@strGetFields+' from '+@tablename+' where '+@strOrder+' '+@strTemp+' ('+@strOrder+')'
                  +' from (select top '+str((@pageIndex-1)*@pageSize)+' '+@strGetFields+' from '+@tablename+ ' '+@strOrders+ ') as tempTable ) '+@strOrders
         
    if @strWhere != ' '
    begin
       set @strSql = 'select top '+str(@pageSize)+ ' '+@strGetFields+' from '+@tablename+ ' where '+@strOrder+ ' '+@strTemp+' ('+@strOrder+') '
                   +' from(select top '+str((@pageIndex-1)*@pageSize)+' '+@strGetFields+' from '+@tablename+' where '+@strWhere+' ' +@strOrders+') as tempTable) where '+@strWhere+' '+@strOrders
   
    end
end 
exec(@strSql)    
end
GO

Answer 6

你的存储过程写的不容易看懂，有点晕！
你首先备份一下数据库，然后将这张表中数据保留<=100条记录在测试一下看看有没有问题（并输出调用储存过程执行的时间差）；如果没有任何问题执行速度比较快的话；那就需要你优化你的存储过程了。否则就是你的程序那边出了问题。
附（例子）：
CREATE PROCEDURE spu_SELECT_PHOTO_BY_ALBUMID_AND_PAGE
    @albumID int ,
    @PageSize     int = 10,           -- 页尺寸
    @PageIndex    int = 1,            -- 页码
    @IsReCount    bit = 0            -- 返回记录总数, 非 0 值则返回   
ASdeclare @strSQL   varchar(300)       -- 主语句
declare @strTmp   varchar(100)        -- 临时变量
declare @strOrder varchar(40)        -- 排序类型
declare @OrderType    bit           -- 设置排序类型, 非 0 值则降序
declare @strWhere varchar(50)     -- 查询条件 (注意: 不要加 where)
declare @tblName  varchar(10)       -- 表名
declare @fldName  varchar(20)       -- 主键字段名--add by yy
set @tblName='photo'
set @fldName='UploadTime'
set @OrderType='1'
set @strWhere='[albumID]='+str(@albumID)
--end addif @OrderType != 0
begin
    set @strTmp = '<(select min'
    set @strOrder = ' order by [' + @fldName +'] desc'
end
else
begin
    set @strTmp = '>(select max'
    set @strOrder = ' order by [' + @fldName +'] asc'
endset @strSQL = 'select top ' + str(@PageSize) + ' pictureID, photoURL, PictureTitle from ['
    + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
    + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
    + @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
    + @strOrderif @strWhere != ''
    set @strSQL = 'select top ' + str(@PageSize) + ' pictureID, photoURL, PictureTitle  from ['
        + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
        + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
        + @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
        + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrderif @PageIndex = 1
begin
    set @strTmp =''
    if @strWhere != ''
        set @strTmp = ' where ' + @strWhere    set @strSQL = 'select top ' + str(@PageSize) + ' pictureID, photoURL, PictureTitle  from ['
        + @tblName + ']' + @strTmp + ' ' + @strOrder
endif @IsReCount != 0
    set @strSQL = 'select count(pictureID) as Total from [' + @tblName + ']'+' where ' + @strWhere
print(@strSQL)
exec (@strSQL)GO

Answer 7

keystudio(抽刀断水)说得很对，和分页控件没有任何关系，如果你的过程在查询分析器中就得执行六秒的话，那么必须想办法优化才行，这方面得多花些时间好好琢磨琢磨

Answer 8

CREATE PROCEDURE P_TopWinCMS_PageList
@tablename varchar(100),
@fields varchar(800),@condition varchar(8000),
@orderfield varchar(100),@ordertype varchar(100),
@pagesize int,@PageNo int AS
declare @SQL varchar(8000)if (@ordertype = 'Desc')
begin
if (@condition = 'None')
begin
if (@PageNo = 1)
begin
set @SQL= 'Select Top '+cast(@pagesize as varchar(50)) +' '+ @fields  +' From '+ @tablename +' 
Order By '+ @orderfield +' Desc'
end
if (@PageNo > 1)
begin
set @SQL= 'Select Top '+cast(@pagesize as varchar(50)) +' '+ @fields +' From '+ @tablename +' 
Where '+ @orderfield +'<(Select Min('+ @orderfield +') From (Select Top '+ cast(@pagesize * (@PageNo - 1) as varchar(50)) +' '+ @orderfield 
+' From '+ @tablename +' Order By '+ @orderfield +' Desc) Temp) Order By '+ @orderfield +' Desc'
end
end
else
begin
if (@PageNo = 1)
begin
set @SQL= 'Select Top '+cast(@pagesize as varchar(50)) +' '+ @fields +' From '+ @tablename +' '+ @condition +' Order By '+ @orderfield +' Desc'
end
if (@PageNo > 1)
begin
set @SQL= 'Select Top '+cast(@pagesize as varchar(50)) +' '+ @fields +' From '+ @tablename +' '+ @condition +' And '+ @orderfield +'<(Select Min('+ @orderfield +') From (Select Top '+ cast(@pagesize * (@PageNo - 1) as varchar(50)) +' '+ @orderfield +' From '+ @tablename +' '+ @condition +' Order By '+ @orderfield +' Desc) Temp) Order By '+ @orderfield +' Desc'
end
end
end
else if (@condition = 'None')
begin
if (@PageNo = 1)
begin
set @SQL= 'Select Top '+cast(@pagesize as varchar(50)) +' '+ @fields +' From '+ @tablename +' Order By '+ 
@orderfield +' Asc'
end
if (@PageNo > 1)
begin
set @SQL= 'Select Top '+cast(@pagesize as varchar(50)) +' '+ @fields +' From '+ @tablename +' Where '+ 
@orderfield +'>(Select Max('+ @orderfield +') From (Select Top '+ cast(@pagesize * (@PageNo - 1) as varchar(50)) +' '+ @orderfield +' From '+ 
@tablename +' Order By '+ @orderfield +' Asc) Temp) Order By '+ @orderfield +' Asc'
end
end
else
begin
if (@PageNo = 1)
begin
set @SQL= 'Select Top '+cast(@pagesize as varchar(50)) +' '+ @fields +' From '+ @tablename +' '+ 
@condition +' Order By '+ @orderfield +' Asc'
end
if (@PageNo > 1)
begin
set @SQL= 'Select Top '+cast(@pagesize as varchar(50)) +' '+ @fields +' From '+ @tablename +' '+ 
@condition +' And '+ @orderfield +'>(Select Max('+ @orderfield +') From (Select Top '+ cast(@pagesize * (@PageNo - 1) as varchar(50)) +' '+ 
@orderfield+' From '+ @tablename +' '+ @condition +' Order By '+ @orderfield +' Asc) Temp) Order By '+ @orderfield +' Asc'
end
end
exec (@SQL)
GO

Answer 9

你好：
我的存储过程问题
找到原因了，我觉如果从一张表中取数据，只用1秒就能查出来了，
但现在是从三张表关联取数据一张表５３８７７７　条记录
一张表５３８５７７　条记录
一张表 5　          条由于数据项太多，我建了个视图，从里面取出相应的字段，发现打开视图就很慢，返回到页面更不用说了，点查询就会超时，我用单个表做实验，很快的，
我的视图如下？
请问如何优化呢？
CREATE VIEW dbo.V_MemberList
AS
SELECT dbo.t_Member_Member.ID, dbo.t_Member_Member.UserName, 
      dbo.t_Member_Member.Name, dbo.t_Member_Member.csMoney, 
      dbo.t_Member_Member.getMoney, dbo.t_Member_Member.Code1, 
      dbo.t_Member_Member.Code1 + dbo.t_Member_Member.Code2 + dbo.t_Member_Member.Code3
       + dbo.t_Member_Member.Code4 AS cCode, 
      dbo.CheckStatusName(dbo.t_Member_Info.CheckStatus) AS CheckStatus, 
      dbo.MemberStatusName(dbo.t_Member_Info.MemberStatus) AS MemberStatus, 
      dbo.t_Member_Info.RegisterTime, dbo.t_Member_Info.BeginTime, 
      dbo.t_Member_Info.EndTime, ISNULL(dbo.t_Member_Level.Name, '无') 
      AS LevelName, dbo.t_Member_Info.LevelID, 
      dbo.t_Member_Info.CheckStatus AS CheckStatus1, 
      dbo.t_Member_Info.MemberStatus AS MemberStatus1
FROM dbo.t_Member_Member INNER JOIN
      dbo.t_Member_Info ON 
      dbo.t_Member_Member.ID = dbo.t_Member_Info.MemberID INNER JOIN
      dbo.t_Member_Level ON dbo.t_Member_Info.LevelID = dbo.t_Member_Level.ID

Answer 10

CREATE   proc up_GetTopicList
@PageNo varchar(4)='1',@PageSize varchar(4)='20'
as
/*定义局部变量*/
declare @a_intPageNo int
declare @a_intPageSize int
declare @intBeginID int
declare @intEndID int
declare @intRecordCount int
declare @intPageCount int
declare @intRowCount intset @a_intPageNo = cast(@PageNo as bigint)
set @a_intPageSize = cast(@PageSize as int)/*关闭计数*/
set nocount on
/*求该分类总数 */
select @intRecordCount = count(*) from company
if @intRecordCount = 0
return 0
/*判断页数是否正确*/
if(@a_intPageNo -1)* @a_intPageSize > @intRecordCount
return (-1)/*求开始ID*/
set @intRowCount = (@a_intPageNo -1)*@a_intPageSize +1
/*限制条数*/
set rowcount @intRowCount
select @intBeginID = companyID from company order by companyID desc/*结束ID*/
set @intRowcount = @a_intPageNo * @a_intPageSize
/*限制条数*/
set rowcount @intRowcount
select @intEndID = companyID from company order by companyID desc/*恢复系统变量*/
set rowcount 0 
set nocount offselect companyID,companyName,re,editDate from company where companyID between @intEndID and @intBeginID order by companyID desc
return (@intRecordCount)
GO 
我用一千万条记录测试过，我的垃圾电脑都不会超过6秒！