听说用ROW_NUMBER()分页100W条数据翻页只要2-3秒,有时间测试下
--
NORTHWND.MDF库
DECLARE @pagenum AS INT , @pagesize AS INT
SET @pagenum = 2
SET @pagesize = 3
SELECT *
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY EmployeeID DESC ) AS RowNum,
*
FROM Employees) AS Employees
WHERE RowNum BETWEEN ( @pagenum - 1 ) * @pagesize + 1 AND @pagenum * @pagesize
ORDER BY EmployeeID DESC
DECLARE @pagenum AS INT , @pagesize AS INT
SET @pagenum = 2
SET @pagesize = 3
SELECT *
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY EmployeeID DESC ) AS RowNum,
*
FROM Employees) AS Employees
WHERE RowNum BETWEEN ( @pagenum - 1 ) * @pagesize + 1 AND @pagenum * @pagesize
ORDER BY EmployeeID DESC
2009年7月3日 23:30:20 测试
CREATE
procedure
[
dbo
]
.
[
p_test
]
@startIndex int ,
@endIndex int ,
@docount bit ,
@strwhere varchar ( 20 )
as
if ( @docount = 1 )
select count (id) from tb_testtable
else
begin
SET @strwhere = CHAR ( 34 ) + CHAR ( 42 ) + @strwhere + CHAR ( 42 ) + CHAR ( 34 );
with temptbl as ( SELECT ROW_NUMBER() OVER ( ORDER BY id desc ) AS Row, * from tb_testtable where CONTAINS ( * , @strwhere ))
SELECT * FROM temptbl where Row between @startIndex and @endIndex
end
@startIndex int ,
@endIndex int ,
@docount bit ,
@strwhere varchar ( 20 )
as
if ( @docount = 1 )
select count (id) from tb_testtable
else
begin
SET @strwhere = CHAR ( 34 ) + CHAR ( 42 ) + @strwhere + CHAR ( 42 ) + CHAR ( 34 );
with temptbl as ( SELECT ROW_NUMBER() OVER ( ORDER BY id desc ) AS Row, * from tb_testtable where CONTAINS ( * , @strwhere ))
SELECT * FROM temptbl where Row between @startIndex and @endIndex
end
全文索引(模糊搜索)测试,共五百万条数据,耗时436毫秒。
begin
set nocount on ;
declare @timediff datetime
select @timediff = Getdate ()
select count (id) as 总行数 from tb_testtable
exec P_test @startIndex = 1 , @endIndex = 20 , @docount = 0 , @strwhere = ' 爱情 '
select datediff (ms, @timediff , GetDate ()) as 耗时
set nocount off ;
end
/*
总行数
-----------
5000001
id userName userText
----------- -------------------- -----------------------------
5080 admin 人们争执不休的爱情道理只是些没
耗时
-----------
446
*/
set nocount on ;
declare @timediff datetime
select @timediff = Getdate ()
select count (id) as 总行数 from tb_testtable
exec P_test @startIndex = 1 , @endIndex = 20 , @docount = 0 , @strwhere = ' 爱情 '
select datediff (ms, @timediff , GetDate ()) as 耗时
set nocount off ;
end
/*
总行数
-----------
5000001
id userName userText
----------- -------------------- -----------------------------
5080 admin 人们争执不休的爱情道理只是些没
耗时
-----------
446
*/