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测试: XiaoZhengGe '任何条件的SQL语句',2,10
@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测试: XiaoZhengGe '任何条件的SQL语句',2,10
不要说游标不好，要看在什么情况下，如何取数据的思路，你不妨试试在几百万甚至更多的数据记录下，用它试试看速度的快慢，和你以前的分页存储过程比较一下！
to:lihonggen0(李洪根，MS MVP，标准答案来了)
我现在所有的DataGrid分页都是用的它,比以前用的那个快,以前的那个在这里:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
--应用举例：Nikko_KF DataGrid_Main.aspx
--exec DataGrid_Page 'iteminfo',23,1,'id,model_no','',' where id>56 'ALTER Proc DataGrid_Page
@QueryStr nvarchar(100), --表名、视图名、查询语句
@PageSize int=10, --每页的大小(行数)
@PageCurrent int=1, --要显示的页
@FdShow nvarchar (1000)='', --要显示的字段列表,如果查询结果有标识字段,需要指定此值,且不包含标识字段
@FdOrder nvarchar (100)='', --排序字段列表
@WhereStr nvarchar (200)='' --内容是' id=3 and model_no like '%24%' and '
as
/*--用存储过程实现的分页程序 显示指定表、视图、查询结果的第X页
对于表中主键或标识列的情况,直接从原表取数查询，其它情况使用临时表的方法
如果视图或查询结果中有主键,不推荐此方法--李政 2003.10.27--*/
declare @FdName nvarchar(250) --表中的主键或表、临时表中的标识列名
,@Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号
,@Obj_ID int --对象ID
--表中有复合主键的处理
declare @strfd nvarchar(2000) --复合主键列表
,@strjoin nvarchar(4000) --连接字段
,@strwhere nvarchar(2000) --查询条件
select @Obj_ID=object_id(@QueryStr)
,@FdShow=case isnull(@FdShow,'') when '' then ' *' else ' '+@FdShow end
,@FdOrder=case isnull(@FdOrder,'') when '' then '' else ' order by '+@FdOrder end
,@QueryStr=case when @Obj_ID is not null then ' '+@QueryStr else ' ('+@QueryStr+') a' end--如果显示第一页，可以直接用top来完成
if @PageCurrent=1
begin
select @Id1=cast(@PageSize as varchar(20))
exec('select top '+@Id1+@FdShow+' from '+@QueryStr+@WhereStr+@FdOrder)
return
end--如果是表,则检查表中是否有标识更或主键
if @Obj_ID is not null and objectproperty(@Obj_ID,'IsTable')=1
begin
select @Id1=cast(@PageSize as varchar(20))
,@Id2=cast((@PageCurrent-1)*@PageSize as varchar(20)) select @FdName=name from syscolumns where id=@Obj_ID and status=0x80
if @@rowcount=0 --如果表中无标识列,则检查表中是否有主键
begin
if not exists(select 1 from sysobjects where parent_obj=@Obj_ID and xtype='PK')
goto lbusetemp --如果表中无主键,则用临时表处理 select @FdName=name from syscolumns where id=@Obj_ID and colid in(
select colid from sysindexkeys where @Obj_ID=id and indid in(
select indid from sysindexes where @Obj_ID=id and name in(
select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
)))
if @@rowcount>1 --检查表中的主键是否为复合主键
begin
select @strfd='',@strjoin='',@strwhere=''
select @strfd=@strfd+',['+name+']'
,@strjoin=@strjoin+' and a.['+name+']=b.['+name+']'
,@strwhere=@strwhere+' and b.['+name+'] is null'
from syscolumns where id=@Obj_ID and colid in(
select colid from sysindexkeys where @Obj_ID=id and indid in(
select indid from sysindexes where @Obj_ID=id and name in(
select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
)))
select @strfd=substring(@strfd,2,2000)
,@strjoin=substring(@strjoin,5,4000)
,@strwhere=substring(@strwhere,5,4000)
goto lbusepk
end
end
end
else
goto lbusetemp/*--使用标识列或主键为单一字段的处理方法--*/
lbuseidentity:
if len(@WhereStr)>10
begin
exec('select top '+@Id1+@FdShow+' from '+@QueryStr
+@WhereStr+' and '+@FdName+' not in(select top '
+@Id2+' '+@FdName+' from '+@QueryStr+@WhereStr+@FdOrder
+')'+@FdOrder
)
return
end
else
begin
exec('select top '+@Id1+@FdShow+' from '+@QueryStr
+' where '+@FdName+' not in(select top '
+@Id2+' '+@FdName+' from '+@QueryStr+@FdOrder
+')'+@FdOrder
)
return
end/*--表中有复合主键的处理方法--*/
lbusepk:
exec('select '+@FdShow+' from(select top '+@Id1+' a.* from
(select top 100 percent * from '+@QueryStr+@FdOrder+') a
left join (select top '+@Id2+' '+@strfd+'
from '+@QueryStr+@FdOrder+') b on '+@strjoin+'
where '+@strwhere+') a'
)
return
/*--用临时表处理的方法--*/
lbusetemp:
select @FdName='[ID_'+cast(newid() as varchar(40))+']'
,@Id1=cast(@PageSize*(@PageCurrent-1) as varchar(20))
,@Id2=cast(@PageSize*@PageCurrent-1 as varchar(20))exec('select '+@FdName+'=identity(int,0,1),'+@FdShow+'
into #tb from'+@QueryStr+@FdOrder+'
select '+@FdShow+' from #tb where '+@FdName+' between '
+@Id1+' and '+@Id2
)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SELECT Top 10 * FROM Customers WHERE Customerid NOT IN (SELECT TOP 20 Customerid from Customers )declare @SQLStr varchar(8000)
set @SQLStr='SELECT Top '+cast(@每页大小 as varchar)+' * FROM 表 WHERE 主键列 NOT IN (SELECT TOP '+cast(@每页大小*@第几页 as varchar)+' 主键列 from 表 )'
exec(@SQLStr)我个人认为最好的分页方法是:
Select top 10 * from table where id>200
写成存储过程,上面的语句要拼一下sql语句,要获得最后大于的哪一个ID号
郁闷:我们公司的好多数据表竟然没有主键列!
先是运行存储过程insert to mssql表中,然后分页显示出来.速度还真是蛮快的.不过真的能满足任何表,没有id号和主键的表都行.分页很方便!
聚集索引速度才快select 的时候,尽量不用select *,只查询你需要的列
http://www.yibiz.com/trade/search.asp?sortid=5&type=供应&page=3&country=&sf=&cs=&txtitle=&DateTime= 这里的分页速度确实是快!配服!
我的这个存储过程确实非常的通用，任何表者行，语句也可以任意写，只要符合sql语句规范！数据量不大时建立采用！
Select top 10 * from table where id>200
写成存储过程,上面的语句要拼一下sql语句,要获得最后大于的哪一个ID号
用Select top 10 * from table where C1>@value Order by C1
@value 值取上一页的最后一条记录C1值
那么这样的表写存储过程就太方便了.select 字段列表 from 表名 where id>=(页数-1)*页宽+1 and id<=页数*页宽
几万的数据还是用不到一秒！
我用select top 要花26妙再说了要是没有id怎么办
不要见笑啊。用了你得代码不够，要看懂！
但是通用的效率一般不好用select top 要花26秒? 你top多少条数据,有主键吗?
如果你有主键,top 10 到 20,用了不那么长时间,各位可以用十几万行的表试一下.---------------------------------------没有ID,只能用临时表,但效率不高
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where)
ASdeclare @strSQL varchar(2000) -- 主语句
declare @strTmp varchar(1000) -- 临时变量
declare @strOrder varchar(1000) -- 排序类型if @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) + " * 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) + " * 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) + " * from ["
+ @tblName + "]" + @strTmp + " " + @strOrder
endif @IsCount != 0
set @strSQL = "select count(*) as Total from [" + @tblName + "]"exec (@strSQL)
GO要注意看,修改后的存储过程在使用@strWhere时,都在其前后加上了(),这样,就防止嵌套的()出现错误下面的代码是引用该存储过程的一个范例 SqlConnection MyConnection=new SqlConnection(ConfigurationSettings.AppSettings["dsn"]);
DataSet MyDataSet=new DataSet();
string strKeyword=Keyword.Text.Trim().Replace("\'","\'\'");
string strSalesId=Sales.SelectedItem.Value;
int RecordCount=CalcRecordCount();
RecordNumber.Text=RecordCount.ToString();
LblRecordNumber.Text=RecordCount.ToString();
string strExpress="Id<>0";
if (strKeyword!="")
strExpress=strExpress+" and (companyenname like '%"+strKeyword+"%' or companychname like '%"+strKeyword+"%' or Companyshortname like '%"+strKeyword+"%' or web like '%"+strKeyword+"%' or mainproduct like '%"+strKeyword+"%' or phone like '%"+strKeyword+"%' or memo like '%"+strKeyword+"%' or address like '%"+strKeyword+"%' or linkmanphone like '%"+strKeyword+"%')";
if (strSalesId!="")
strExpress=strExpress+" and salesid="+strSalesId;
SqlCommand MyCommand=new SqlCommand();
MyCommand.Connection=MyConnection;
MyCommand.CommandText="GetRecordFromPage";
MyCommand.CommandType=CommandType.StoredProcedure;
MyCommand.Parameters.Add("@tblName","customerview");
MyCommand.Parameters.Add("@fldName","id");
MyCommand.Parameters.Add("@strWhere",strExpress);
MyCommand.Parameters.Add("@PageSize",Int32.Parse(CustomerList.PageSize.ToString()));
MyCommand.Parameters.Add("@PageIndex",Int32.Parse(ViewState["PageIndex"].ToString())+1);
SqlDataReader MyReader;
MyConnection.Open();
MyReader=MyCommand.ExecuteReader();
CustomerList.VirtualItemCount=RecordCount;
CustomerList.DataSource=MyReader;
CustomerList.DataKeyField="id";
CustomerList.DataBind();
MyReader.Close();
MyConnection.Close();在这里,要注意的是存储过程使用的PAGEINDEX变量是从1开始就算条件再复杂,也能搞定
然后再发表自己的意见
好处是通用
这样的应用很多一个表中,你不会没有主键,索引吧.如果没有查询会更慢如果主键不是数字,是字符,可用临时表报表都有非常复杂的排序,在SQL上体现无非就是order by
要是我按某一列排序或某几列，
然后怎么个 select top . id 都乱了啊！难道另外在select的时候 另外弄出个替增id出来？
排序也是可以的!用Select top 10 * from table where C1>@value Order by C1
@value 值取上一页的最后一条记录C1值如;
Select top 10 * from orders where orderid<11074 Order by orderid desc
或者是Report Service 里,很容易就可以!如果写在存储过程里,也是可以的,但是不是我推荐的方法!
提供的那个存储过程,我看已经相当好了
速度快不快,要看你
select top n * from 表
那个n的值到底多大,就是取决于你的PageSize
Select top 10 * from table where id>200
写成存储过程,上面的语句要拼一下sql语句,要获得最后大于的哪一个ID号
我看这个方法,不就是邹建的那个很长的存储过程?核心不也是它
经典的程序，非常的通用和使用。多谢搂主。另外我也是比较关注在大数据量的情况下的结果，所以简单测试了一下（仅供参考），2CPU,512M memory，sqlserver 2000,
小数据量（<1W）的情况下, <1秒
40W数据量，top1000，page30，size15： Result: >20秒
160W数据量 >1分钟
SELECT TOP 10 * FROM Products where ProductName>'Chang' order by ProductName不一定要按照ID来吧
比如这个
PagedDataSource pds = new PagedDataSource();
pds.DataSource = data.Tables[0].DefaultView; // 某 ADO.net DataView
pds.AllowPaging = true;
pds.PageSize = 10; // 大小
pds.CurrentPageIndex = (Request.QueryString["Page"] == null) ? 0 : Int32.Parse(Request.QueryString["Page"]); // 现在的页号
然后用
binder.DataSource = pds;
binder.DataBind();
就可以绑喽如果内存足够多尤其是IA的EBA模式的话，速度比你那个存储过程要快（注意：前提是内存足够！比如10G啦——理论上讲可以处理一万行每行1k的数据）
比如：用户点 col1然后按col1排序显示，然后用户在点一下col2 按col1，col2排序头是不是要大了！不可能用id>常量
因为在当前页里 肯定会有id比常量小的行！这个常量也就是上页的最后一行的id号这个常量不一定比当前页中的id号都大或都小，因此不能用id来比较除非有自动产生一个有顺序的id号，好像是不现实的
id>2000这种形式速度是快,可是2000这个数字还是要计算的,要么用临时表,要么用NOT in
你的这个效率真的是很差的.其实一句就搞定了,你写了那么多做什么
/*
aothor wuchen
date 2003/11/25
name NewsItemList
descreption:
新闻分页显示，可通过TypeId来指定选择新闻的类型，不指定则全部显示
*/CREATE PROCEDURE dbo.NewsItemList (
@PageIndex int , --索引页码号
@PageSize int, --每页记录数
@TypeId int=0 --新闻类型id
)AS
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
-- Set the page bound
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
-- Create a temp Table to store the select results
CREATE TABLE #PageIndexFornewslist (
IndexId int IDENTITY(1,1) NOT NULL ,
newsId int
)
-- Insert into #PageIndexFornewslist
if @TypeId = 0
BEGIN
INSERT INTO #PageIndexFornewslist(newsId)
SELECT n.ID FROM newslist as n
ORDER BY n.CreateDate DESC
END
else
BEGIN
INSERT INTO #PageIndexFornewslist(newsId)
SELECT n.ID FROM newslist as n
where n.typeid=@TypeId
ORDER BY n.CreateDate DESC
END
-- select record
if @TypeId = 0
BEGIN
select
n.ID,
n.Subject,
n.CreateDate,
n.Content,
n.Author,
n.click,
n.img,
n.typeid,
(DateDiff(day,ISNULL(n.CreateDate,0),getdate())) AS diffday
from
newslist n (nolock),
#PageIndexFornewslist AS t
where
n.ID = t.newsId and
t.IndexId >@PageLowerBound and
t.IndexId<@PageUpperBound
order by t.IndexId
END
else
begin
select
n.ID,
n.Subject,
n.CreateDate,
n.Content,
n.Author,
n.click,
n.img,
n.typeid ,
(DateDiff(day,ISNULL(n.CreateDate,0),getdate())) AS diffday
from
newslist n (nolock),
#PageIndexFornewslist AS t
where
n.ID = t.newsId and
n.typeid=@TypeId and
t.IndexId >@PageLowerBound and
t.IndexId<@PageUpperBound
order by t.IndexId
end
http://www.bizlands.com/trade/search.asp?type=s&sortid=15
http://www.bizlands.com/trade/search.asp?type=s&sortid=14象这种需求,我是不用临时表和游标的.根据我的测试NOT IN的性能不如select top的方式,在我的测试环境中是这样.两年前为了这个问题,我还提交了一份10页的报告(因为表中可能会上百万行).如果要考虑到多个字段的排序.我会用not in的方式,写一个通用的存储过程.实在达不到需求的时候,才考虑用临时表.游标一般是最后考虑的.(因为用临时表的方法,可以代替游标.)
还有就是SQL语句里尽量不要用not in，这样效率很低
to xinyulou:你可以看一下:
http://www.bizlands.com/trade/search.asp?type=s&sortid=15这个ID就是不连续的
这样才能看出来!
where datetime>=starttime and datetime<=endtime and sitename in("name1","name2",
"name3")
in里面的列表是不定的，由用户选择的。这样写又没有办法用到索引。不知道如何取舍，大家能不能帮我想个办法啊？还有就是这种情况，索引如何建立？
@operation int, --操作類型,0為取得當前頁記錄集，1為取得記錄總數(RecordCount)
@TableName varchar(100), --要操作的表名
@FieldLists varchar(8000), --字段列表，可以為'*",也可為空
@KeyField varchar(100), --關鍵字段名
@Critical varchar(200), --查詢條件
@SortType varchar(4), --排序類型,為'DESC'或者'ASC',為空是默認為'ASC'
@PageSize int, --每頁大小，大於0的整數
@ShowPageNo int --當前要顯示的頁碼，大於0的整數，頁數從1開始
AS
declare @SelectSqlString varchar(8000),@tempSql varchar(8000)
if @operation=0
begin
--要返回的記錄
select @SelectSqlString='SELECT TOP '+STR(@PageSize)
if rtrim(@FieldLists)=''
select @FieldLists='*' select @SelectSqlString=@SelectSqlString+' '+@FieldLists+' from '+@TableName if rtrim(@SortType)='ASC'
select @tempSql='>(select max(Out.'
else
select @tempSql='<(select min(Out.' select @tempSql=@tempSql+@KeyField+') from (select top '+str(@PageSize*(@ShowPageNo-1))+' '+@KeyField+' from '+@TableName if rtrim(@Critical)!=''
select @tempSql=@tempSql+' where '+@Critical if upper(rtrim(@SortType))!='DESC'
select @SortType='ASC' select @tempSql=@tempSql+' order by '+@KeyField+' '+@SortType+') as Out) '
if rtrim(@Critical)!=''
begin
select @Critical=' where '+@Critical
if @ShowPageNo>1
select @Critical=@Critical+' and '+@KeyField+@tempSql
end
else
if @ShowPageNo>1
select @Critical=' where '+@KeyField+@tempSql select @SelectSqlString=@selectSqlString+@Critical+' order by '+@KeyField+' '+@SortType execute(@SelectSqlString)
--select @SelectSqlString as sqlstr
end else begin
select @SelectSqlString='select count(*) as RecordCount from '+@TableName
if rtrim(@Critical)!=''
select @SelectSqlString=@SelectSqlString+' where '+@Critical
execute(@SelectSqlString)
end
GO