用分区表和分页存储过程:
ALTER procedure [dbo].[Pagination](
@pagesize int, -- 页大小
@pageindex int, -- 当前页
@identity varchar(100), -- 唯一列
@cells varchar(1000)='*', -- 显示列
@tables varchar(1000), -- 表名称
@condition varchar(2000)=NULL, -- 查询条件
@top int = -1, --
@orderby varchar(200)=NULL -- 排序 desc
)
as
set nocount on
declare @rowcount int;
declare @SQL nvarchar(4000); set @SQL = N'select @rowcount = count(0) from ' + @tables + ' where 1=1 ';
if(@condition IS NOT NULL) set @SQL = @SQL + @condition;
exec sp_executesql @SQL,N'@rowcount int output',@rowcount output; if(@top != -1 and @rowcount > @top) set @rowcount = @top; set @SQL = N'declare @___lb int;';
set @SQL = @SQL + N'declare @___ub int;';
set @SQL = @SQL + N'set @___lb=(@pageindex-1)*@pagesize;';
set @SQL = @SQL + N'set @___ub=@___lb+@pagesize;';
set @SQL = @SQL + N'set ROWCOUNT @___ub;'; set @SQL = @SQL + N'with ___t as (select *'; if(@orderby is not null and @orderby != '')
set @SQL = @SQL + N',row_number() over(order by ' + @orderby + ',' + @identity + ' asc) as ___p from (';
else
set @SQL = @SQL + N',row_number() over(order by ' + @identity + ' asc) as ___p from ('; if(@top != -1)
set @SQL = @SQL + N'select top ' + cast(@top as nvarchar(10)) + ' ';
else
set @SQL = @SQL + N'select '; if(@cells IS NOT NULL)
set @SQL = @SQL + @cells;
else
set @SQL = @SQL + N'*'; set @SQL = @SQL + ' from ' + @tables + ' where 1=1'; if(@condition IS NOT NULL and @condition != '') set @SQL = @SQL + ' ' + @condition; set @SQL = @SQL + ') as ___q) select * from ___t where ___p>@___lb and ___p<=@___ub;'; exec sp_executesql @SQL,N'@pagesize int,@pageindex int',@pagesize = @pagesize,@pageindex = @pageindex; return @rowcount;
set nocount off
ALTER procedure [dbo].[Pagination](
@pagesize int, -- 页大小
@pageindex int, -- 当前页
@identity varchar(100), -- 唯一列
@cells varchar(1000)='*', -- 显示列
@tables varchar(1000), -- 表名称
@condition varchar(2000)=NULL, -- 查询条件
@top int = -1, --
@orderby varchar(200)=NULL -- 排序 desc
)
as
set nocount on
declare @rowcount int;
declare @SQL nvarchar(4000); set @SQL = N'select @rowcount = count(0) from ' + @tables + ' where 1=1 ';
if(@condition IS NOT NULL) set @SQL = @SQL + @condition;
exec sp_executesql @SQL,N'@rowcount int output',@rowcount output; if(@top != -1 and @rowcount > @top) set @rowcount = @top; set @SQL = N'declare @___lb int;';
set @SQL = @SQL + N'declare @___ub int;';
set @SQL = @SQL + N'set @___lb=(@pageindex-1)*@pagesize;';
set @SQL = @SQL + N'set @___ub=@___lb+@pagesize;';
set @SQL = @SQL + N'set ROWCOUNT @___ub;'; set @SQL = @SQL + N'with ___t as (select *'; if(@orderby is not null and @orderby != '')
set @SQL = @SQL + N',row_number() over(order by ' + @orderby + ',' + @identity + ' asc) as ___p from (';
else
set @SQL = @SQL + N',row_number() over(order by ' + @identity + ' asc) as ___p from ('; if(@top != -1)
set @SQL = @SQL + N'select top ' + cast(@top as nvarchar(10)) + ' ';
else
set @SQL = @SQL + N'select '; if(@cells IS NOT NULL)
set @SQL = @SQL + @cells;
else
set @SQL = @SQL + N'*'; set @SQL = @SQL + ' from ' + @tables + ' where 1=1'; if(@condition IS NOT NULL and @condition != '') set @SQL = @SQL + ' ' + @condition; set @SQL = @SQL + ') as ___q) select * from ___t where ___p>@___lb and ___p<=@___ub;'; exec sp_executesql @SQL,N'@pagesize int,@pageindex int',@pagesize = @pagesize,@pageindex = @pageindex; return @rowcount;
set nocount off
如where 1=1 不建议写,宁可多写2句来构造也比这个强。
-_-||
我的机器512内存,1.7CPU,有张50多万数据的表,1秒不到declare @i int set rowcount 200001select @i = autoid from kjmain order by autoidset rowcount 300select * from kjmain where autoid >= @iset rowcount 0
但是加个 WHERE CHARINDEX() 或 LIKE 就要 8秒左右..不知道大家这样操作一次,一般是多少秒
我才23万多的记录呀
问题就是用了CHARINDEX或LINK之后就会慢的,这是自然的,所以,改用全文索引了,很快.