数据库为sql 2000查询产品,分页显示,默认按企业的等级排序。
sql="select ProdName,ID from ProdList Where status=2 and CorpID in(select ID from Corplist where status=2) Order by isnull((select max(class) from Corplist Where id=ProdList.corpid),0) Desc, AddDate Desc"
rs.open SQL,conn,1,3 Rs.PageSize = 20
MaxPages = Rs.PageCount
Rs.absolutepage = Page
for i = 1 to 20
'显示内容rs.MoveNext
next
____________________________________________________________________
目前两张数据表的记录都快十万条记录,这个分页查询显的比较慢,影响体验。用网上的存储过程尝试了,排序是不能带条件的max和top的写法,都要依靠主键ID来分页,我也行不通求高手指点
sql="select ProdName,ID from ProdList Where status=2 and CorpID in(select ID from Corplist where status=2) Order by isnull((select max(class) from Corplist Where id=ProdList.corpid),0) Desc, AddDate Desc"
rs.open SQL,conn,1,3 Rs.PageSize = 20
MaxPages = Rs.PageCount
Rs.absolutepage = Page
for i = 1 to 20
'显示内容rs.MoveNext
next
____________________________________________________________________
目前两张数据表的记录都快十万条记录,这个分页查询显的比较慢,影响体验。用网上的存储过程尝试了,排序是不能带条件的max和top的写法,都要依靠主键ID来分页,我也行不通求高手指点
go
set nocount on
go
set showplan_text on
go
--2012的OFFSET分页方式
select number
from spt_values
where type='p'
order by number offset 10 rows fetch next 5 rows only;
go
--2005的ROW_NUMBER分页方式
select number from (
select number,row_number() over(order by number) as num from spt_values where type='p'
) t where num between 11 and 15
order by number asc
go
--2000的TOP分页方式
select number from (
select top 5 number from (
select top 15 number from spt_values where type='p' order by number asc
) t
order by number desc
) t
order by number asc
go
set showplan_text off
go
建立合适的索引