22,210
社区成员
发帖
与我相关
我的任务
分享分页 ~求救
数据库分页,我找了2种分页方式,哪个更好?为什么?有没其他方法?
写了个查询测试,这个数值是否合理?
select * from (select *,row_number() over(order by uid desc) as rn FROM r_user_class) as sp where rn > 10000 and rn <= 20000
select top 10000 * from r_user_class where ugid not in(select top 10000 ugid from r_user_class order by uid desc) order by uid desc
写了个查询测试,这个数值是否合理?
declare @starttime datetime,
@endtime datetime
set @starttime = getdate()
select * from (select *,row_number() over(order by uid desc) as rn FROM r_user_class) as sp where rn > 10000 and rn <= 20000
set @endtime = getdate()
select datediff(ms,@starttime,@endtime)
set @starttime = getdate()
select top 10000 * from r_user_class where ugid not in(select top 10000 ugid from r_user_class order by uid desc) order by uid desc
set @endtime = getdate()
select datediff(ms,@starttime,@endtime)
...全文
请发表友善的回复…
发表回复
wsh236 2009-01-19
- 打赏
- 举报
加排序，效率肯定不会高的！
LaPoPvLe 2009-01-19
- 打赏
- 举报
谢谢各位的帮助啊.
小弟先慢慢消化下,帖子等下再结
有问题继续呈上
结果如下,注意前面6个的顺序
小弟先慢慢消化下,帖子等下再结
有问题继续呈上
SELECT * FROM T_USERS WHERE USERPOWER = 1 ORDER BY USERNICKNAME
SELECT TOP 3 * FROM T_USERS WHERE USERPOWER = 1 ORDER BY USERNICKNAME
SELECT TOP 3 * FROM T_USERS WHERE USERPOWER = 1 AND USERID NOT IN
(SELECT TOP 3 USERID FROM T_USERS ORDER BY USERNICKNAME) ORDER BY USERNICKNAME
结果如下,注意前面6个的顺序
USERID USERNICKNAME
----------- --------------------------------
3 333
11 guff
9 huazai
5 huazai
4 LaPoPvLe
0 LaPoPvLe
(6 行受影响)
USERID USERNICKNAME
----------- --------------------------------
3 333
11 guff
5 huazai
(3 行受影响)
USERID USERNICKNAME
----------- --------------------------------
9 huazai
4 LaPoPvLe
0 LaPoPvLe
(3 行受影响)
dawugui 2009-01-19
- 打赏
- 举报
--TOP n 实现的通用分页存储过程(转自邹建)
CREATE PROC sp_PageView
@tbname sysname, --要分页显示的表名
@FieldKey nvarchar(1000), --用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段
@PageCurrent int=1, --要显示的页码
@PageSize int=10, --每页的大小(记录数)
@FieldShow nvarchar(1000)='', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder nvarchar(1000)='', --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC
用于指定排序顺序
@Where nvarchar(1000)='', --查询条件
@PageCount int OUTPUT --总页数
AS
SET NOCOUNT ON
--检查对象是否有效
IF OBJECT_ID(@tbname) IS NULL
BEGIN
RAISERROR(N'对象"%s"不存在',1,16,@tbname)
RETURN
END
IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0
AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0
AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0
BEGIN
RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname)
RETURN
END
--分页字段检查
IF ISNULL(@FieldKey,N'')=''
BEGIN
RAISERROR(N'分页处理需要主键（或者惟一键）',1,16)
RETURN
END
--其他参数检查及规范
IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1
IF ISNULL(@PageSize,0)<1 SET @PageSize=10
IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'
IF ISNULL(@FieldOrder,N'')=N''
SET @FieldOrder=N''
ELSE
SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder)
IF ISNULL(@Where,N'')=N''
SET @Where=N''
ELSE
SET @Where=N'WHERE ('+@Where+N')'
--如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)
IF @PageCount IS NULL
BEGIN
DECLARE @sql nvarchar(4000)
SET @sql=N'SELECT @PageCount=COUNT(*)'
+N' FROM '+@tbname
+N' '+@Where
EXEC sp_executesql @sql,N'@PageCount int OUTPUT',@PageCount OUTPUT
SET @PageCount=(@PageCount+@PageSize-1)/@PageSize
END
--计算分页显示的TOPN值
DECLARE @TopN varchar(20),@TopN1 varchar(20)
SELECT @TopN=@PageSize,
@TopN1=(@PageCurrent-1)*@PageSize
--第一页直接显示
IF @PageCurrent=1
EXEC(N'SELECT TOP '+@TopN
+N' '+@FieldShow
+N' FROM '+@tbname
+N' '+@Where
+N' '+@FieldOrder)
ELSE
BEGIN
--处理别名
IF @FieldShow=N'*'
SET @FieldShow=N'a.*'
--生成主键(惟一键)处理条件
DECLARE @Where1 nvarchar(4000),@Where2 nvarchar(4000),
@s nvarchar(1000),@Field sysname
SELECT @Where1=N'',@Where2=N'',@s=@FieldKey
WHILE CHARINDEX(N',',@s)>0
SELECT @Field=LEFT(@s,CHARINDEX(N',',@s)-1),
@s=STUFF(@s,1,CHARINDEX(N',',@s),N''),
@Where1=@Where1+N' AND a.'+@Field+N'=b.'+@Field,
@Where2=@Where2+N' AND b.'+@Field+N' IS NULL',
@Where=REPLACE(@Where,@Field,N'a.'+@Field),
@FieldOrder=REPLACE(@FieldOrder,@Field,N'a.'+@Field),
@FieldShow=REPLACE(@FieldShow,@Field,N'a.'+@Field)
SELECT @Where=REPLACE(@Where,@s,N'a.'+@s),
@FieldOrder=REPLACE(@FieldOrder,@s,N'a.'+@s),
@FieldShow=REPLACE(@FieldShow,@s,N'a.'+@s),
@Where1=STUFF(@Where1+N' AND a.'+@s+N'=b.'+@s,1,5,N''),
@Where2=CASE
WHEN @Where='' THEN N'WHERE ('
ELSE @Where+N' AND ('
END+N'b.'+@s+N' IS NULL'+@Where2+N')'
--执行查询
EXEC(N'SELECT TOP '+@TopN
+N' '+@FieldShow
+N' FROM '+@tbname
+N' a LEFT JOIN(SELECT TOP '+@TopN1
+N' '+@FieldKey
+N' FROM '+@tbname
+N' a '+@Where
+N' '+@FieldOrder
+N')b ON '+@Where1
+N' '+@Where2
+N' '+@FieldOrder)
END
wsh236 2009-01-19
- 打赏
- 举报
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PROC_SQL_COMP]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PROC_SQL_COMP]
GO
/*--测试两组SQL的平均时间
利用osql.exe来测试两组 SQL 语句的执行时间
测试的存储过程中,调用了事务处理,所以如果测试的SQL语句有数据修改行为
会在调用结束后自动回滚事务,确保测试不会修改数据
已知的问题:
1.由于 osql 调用不允许使用多行,所以存储过程中会把回车换行替换成空格
对于 SQL 字符串中的回车换行,一样会被替换,所以对于字符串中包含回
车换行的 SQL 语句,可能会产生问题,除非回车换行不对 SQL 语句产生影响
2.由于是调用 xp_cmdshell 来执行 osql,所以要求有系统管理员的权限(比如sa)
3.调用 osql 使用了windows身份验证,这样省去了指定用户名及密码的麻烦
但如果你的 sql server 禁用了windows身份验证,则需要修改存储过程的 osql 调用
--邹建 2004.08(引用请保留此信息)--*/
/*--调用示例
exec dbo.PROC_SQL_COMP
'select top 1 * from sysobjects a,sysobjects b',
'select re=''1''
union all select 2'
--*/
create proc dbo.PROC_SQL_COMP
@sql1 varchar(7000), --测试的第一个sql语句
@sql2 varchar(7000), --测试的第二个sql语句
@t int=3 --测试次数
as
set nocount on
declare @s1 varchar(8000),@s2 varchar(8000),@tt varchar(20),@head varchar(8000)
declare @tb sysname
set @tb='tempdb.dbo.[temp_'+cast(newid() as varchar(36))+']'
exec('create table '+@tb+'(id int identity(0,1),m int)')
select @s1=replace(replace(@sql1,'''',''''''''''),char(13)+char(10),' ')
,@s2=replace(replace(@sql2,'''',''''''''''),char(13)+char(10),' ')
,@tt=cast(@t as varchar)
,@head='exec master..xp_cmdshell ''osql /E /d"'+db_name()+'"'
--使用了windows身份验证,如果不支持,则改 /E 为 /U"sa" /P"密码"
exec(@head+' /Q"set xact_abort on;declare @i int,@a datetime;set @i=0;while @i<'
+@tt+' begin select @i=@i+1,@a=getdate();begin tran;exec('''''+@s1
+''''');rollback tran;insert '+@tb+' select datediff(ms,@a,getdate());set @a=getdate();begin tran;exec('''''
+@s2+''''');rollback tran;insert '+@tb+' select datediff(ms,@a,getdate()) end"'',no_output')
exec('select 组号=''SQL''+cast(id%2+1 as varchar),[平均时间(毫秒)]=avg(m),[总时间(毫秒)]=sum(m),测试次数='+@tt+'
from '+@tb+' group by ''SQL''+cast(id%2+1 as varchar)
drop table '+@tb)
go
wsh236 2009-01-19
- 打赏
- 举报
用工具呗。你写SQL语句，也可以用包括实际的运行计划（2005的）
水族杰纶 2009-01-19
- 打赏
- 举报
[Quote=引用楼主 LaPoPvLe 的帖子:]
数据库分页,我找了2种分页方式,哪个更好?为什么?有没其他方法?
SQL code
select * from (select *,row_number() over(order by uid desc) as rn FROM r_user_class) as sp where rn > 10000 and rn <= 20000
select top 10000 * from r_user_class where ugid not in(select top 10000 ugid from r_user_class order by uid desc) order by uid desc
写了个查询测试,这个数值是否合理?
SQL code
declare @sta…
[/Quote]
可以看時間,也可以分析執行計劃
数据库分页,我找了2种分页方式,哪个更好?为什么?有没其他方法?
SQL code
select * from (select *,row_number() over(order by uid desc) as rn FROM r_user_class) as sp where rn > 10000 and rn <= 20000
select top 10000 * from r_user_class where ugid not in(select top 10000 ugid from r_user_class order by uid desc) order by uid desc
写了个查询测试,这个数值是否合理?
SQL code
declare @sta…
[/Quote]
可以看時間,也可以分析執行計劃
LaPoPvLe 2009-01-19
- 打赏
- 举报
谢谢,[wufeng4552]
很好的资料
性能方面怎么看呢?
很好的资料
性能方面怎么看呢?
水族杰纶 2009-01-19
- 打赏
- 举报
--方法1：
--适用于 SQL Server 2000/2005
SELECT TOP 页大小 *
FROM table1
WHERE id NOT IN
(
SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id
)
ORDER BY id
--方法2：
--适用于 SQL Server 2000/2005
SELECT TOP 页大小 *
FROM table1
WHERE id >
(
SELECT ISNULL(MAX(id),0)
FROM
(
SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id
) A
)
ORDER BY id
--方法3：
--适用于 SQL Server 2005
SELECT TOP 页大小 *
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1
) A
WHERE RowNumber > 页大小*(页数-1)
--方法四 适用于 SQL Server 2005
DECLARE @pagenum AS INT, @pagesize AS INT
SET @pagenum = 2
SET @pagesize = 3
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY newsid DESC) AS rownum,
newsid, topic, ntime, hits
FROM news) AS D
WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize
ORDER BY newsid DESC
说明，页大小：每页的行数；页数：第几页。使用时，请把“页大小”和“页大小*(页数-1)”替换成数字。
