如果更有效的实现大量数据的查询与分页
我的库才20多万条记录,现在打开一次都要2秒到4秒.
我用了各种的优化的写法均无成效!请教各位高手,你们的大记录是速度如何,是怎么做的.



------解决方案--------------------
用分区表和分页存储过程：


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

------解决方案--------------------
LZ是不是你表的索引建的有问题,或者服务器性能有无正常运行~

分页用 SET ROWCOUNT 效率不错的
------解决方案--------------------
ShineLC() ( 一级(初级)) 信誉：100的这个有不少地方要修改，有的地方写得太懒惰了。
如where 1=1 不建议写，宁可多写2句来构造也比这个强。