34,873
社区成员
发帖
与我相关
我的任务
分享sql 分页存储过程问题？
我用的是下面的分页存储过程，有两个问题。
一、查询条件如果是where key=1 and smt_id in(select smt_id from bb where key=1)
要怎么写才能成功。
二、为什么第一页的时候那么慢，有没有办法再提高速度。
小弟今天第一次接触存储过程，看了一天的资料百度和本论坛，一次次测试，终于有点小成果了，希望哥哥姐姐们能帮帮小弟。
一、查询条件如果是where key=1 and smt_id in(select smt_id from bb where key=1)
要怎么写才能成功。
二、为什么第一页的时候那么慢，有没有办法再提高速度。
小弟今天第一次接触存储过程，看了一天的资料百度和本论坛，一次次测试，终于有点小成果了，希望哥哥姐姐们能帮帮小弟。
--TOP n 实现的通用分页存储过程(转自邹建)
CREATE PROC fpage
@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
GO
...全文
请发表友善的回复…
发表回复
s32702 2009-01-12
- 打赏
- 举报
存储过程第一次需要编译，第二次直接在内存中执行。
zhongjingbao 2009-01-12
- 打赏
- 举报
我只想知道有没有好的解决第一次慢的问题？
真的没有人知道吗？还是没有办法实现。
真的没有人知道吗？还是没有办法实现。
叶子 2009-01-12
- 打赏
- 举报
第一次执行时数据放入缓存,所以慢。
bbb332 2009-01-11
- 打赏
- 举报
学习
-狙击手- 2009-01-11
- 打赏
- 举报
[Quote=引用 12 楼 zhongjingbao 的回复:]
顶，我不相信没有办法解决。
[/Quote]
顶，我不相信没有办法解决。
[/Quote]
if object_id('OutputReport') is not null
drop table OutputReport
go
create table OutputReport ( id int ,ShortName varchar(20), Rtype int ,AllCount int ,CurrentMonth int)
insert OutputReport select 1,'承建二',11,500,5
union all select 2,'承建二',11,600,4
union all select 3,'承建二',12,300,1
union all select 4,'承建二',14,456,5
union all select 5,'承建二',2,453,12
union all select 6,'承建二',2,322,3
union all select 7,'承建二',3,422,22
union all select 8,'承建二',4,458,7
union all select 9,'承建二',5,4234,3
union all select 10,'承建一',11,533,5
union all select 11,'承建一',11,443,3
union all select 12,'承建一',12,255,3
union all select 13,'承建一',14,523,3
union all select 14,'承建一',2,423,7
union all select 15,'承建一',2,533,3
union all select 16,'承建一',3,534,332
union all select 17,'承建一',4,9763,54
union all select 18,'承建一',5,3132,3
go
CREATE PROC sp_PageView
@tbname sysname, --要分页显示的表名
@FieldKey nvarchar(1000), --用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段
@PageCurrent int=1, -->0表示要显示的页码,如果为0表示仅清理缓存数据的临时表,不返回数据,其他值代表重建缓存数据的临时表
@PageSize int=10, --每页的大小(记录数)
@FieldShow nvarchar(1000)='', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@Where nvarchar(1000)='', --查询条件
@UserName sysname='', --调用查询的用户名
@PageCount int OUTPUT , --总页数
@orderby nvarchar(1000)=N' ORDER BY NEWID()' --eg: order by 1
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(@PageSize,0)<1 SET @PageSize=10
IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'
IF ISNULL(@Where,N'')=N''
SET @Where=N''
ELSE
SET @Where=N'WHERE ('+@Where+N')'
--分页数据缓存临时表状态检测
DECLARE @tempTable sysname,@TempField sysname,@TempTableDate datetime
SET @tempTable=QUOTENAME(N'##'
+RTRIM(LEFT(HOST_NAME(),50))
+N'_'+RTRIM(LEFT(CASE WHEN ISNULL(@UserName,N'')=N'' THEN SUSER_SNAME() ELSE @UserName END,50))
+N'_'+RTRIM(@tbname))
SELECT @TempField=QUOTENAME(c.name),
@TempTableDate=DATEADD(Hour,1,o.crdate) --临时表的有效缓存时间为1小时,创建时间超过1小时的临时表会被重建
FROM tempdb..sysobjects o,tempdb..syscolumns c
WHERE o.id=c.id
AND o.id=OBJECT_ID(N'tempdb..'+@tempTable)
AND c.status=0x80
IF @@ROWCOUNT>0
-- IF ISNULL(@PageCurrent,0)<1 --OR @TempTableDate<GETDATE()
BEGIN
EXEC('DROP TABLE '+@tempTable)
IF @PageCurrent=0 RETURN
END
-- ELSE
-- GOTO lb_TempTable_Created
ELSE
SELECT @TempField=QUOTENAME(NEWID())
--创建分页数据缓存临时表
exec(N'SELECT '+@FieldKey+',IDENTITY(decimal(18,0),0,1) as '+@TempField
+N' INTO '+@tempTable
+N' FROM '+@tbname
+N' '+@Where
+@orderby)
SET @PageCount=(@@ROWCOUNT+@PageSize-1)/@PageSize
GOTO lb_ShowData
lb_TempTable_Created:
--如果@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
lb_ShowData:
IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1
--计算分页显示的TOPN值
DECLARE @TopN varchar(20),@TopN1 varchar(20)
SELECT @TopN=(@PageCurrent-1)*@PageSize,
@TopN1=@PageCurrent*@PageSize-1
--生成主键(惟一键)处理条件
DECLARE @Field sysname
SET @Where=N''
WHILE CHARINDEX(N',',@FieldKey)>0
SELECT @Field=LEFT(@FieldKey,CHARINDEX(N',',@FieldKey)-1),
@FieldKey=STUFF(@FieldKey,1,CHARINDEX(N',',@FieldKey),N''),
@Where=@Where
+N' AND a.'+@Field+N'=b.'+@Field,
@FieldShow=REPLACE(@FieldShow,@Field,N'a.'+@Field)
SELECT @Where=STUFF(@Where+N' AND a.'+@FieldKey+N'=b.'+@FieldKey,1,5,N''),
@FieldShow=REPLACE(@FieldShow,@FieldKey,N'a.'+@FieldKey)
IF @FieldShow=N'*' SET @FieldShow=N'a.*'
--执行查询
exec(N'SELECT '+@FieldShow
+N' FROM '+@tbname
+N' a,'+@tempTable
+N'b WHERE (b.'+@TempField
+N' BETWEEN '+@TopN
+N' AND '+@TopN1
+N') AND ('+@Where
+N') ORDER BY b.'+@TempField)
go
declare @PageCount int
exec sp_PageView 'OutputReport','id',3,2,'*','','sa',@PageCount OUTPUT,' order by Rtype desc '
/*
id ShortName Rtype AllCount CurrentMonth
----------- -------------------- ----------- ----------- ------------
1 承建二 11 500 5
2 承建二 11 600 4
*/
drop proc sp_PageViewzhongjingbao 2009-01-11
- 打赏
- 举报
顶，我不相信没有办法解决。
flyerwing 2009-01-10
- 打赏
- 举报
up!
zhongjingbao 2009-01-10
- 打赏
- 举报
谢谢楼上的朋友，可是还是没有我想要。
7楼的朋友，那个存储过程真的加不了自定义排序，只要能加上这个条件就好了。
有没有朋友帮帮忙，小弟感激不尽。
7楼的朋友，那个存储过程真的加不了自定义排序，只要能加上这个条件就好了。
有没有朋友帮帮忙，小弟感激不尽。
dawugui 2009-01-09
- 打赏
- 举报
[Quote=引用 3 楼 zhongjingbao 的回复:]
为什么第一页的时候会比其他页慢那么多，我测试18000万数据，都要3秒多，而其他页面只要0.2秒。
[/Quote]
第一次执行需要把数据放入缓存中,以后的查询不需要进行前面那个步骤,所有快得多.
为什么第一页的时候会比其他页慢那么多，我测试18000万数据，都要3秒多，而其他页面只要0.2秒。
[/Quote]
第一次执行需要把数据放入缓存中,以后的查询不需要进行前面那个步骤,所有快得多.
-狙击手- 2009-01-09
- 打赏
- 举报
http://blog.csdn.net/happyflystone/archive/2008/07/18/2668872.aspxzhongjingbao 2009-01-09
- 打赏
- 举报
楼上的兄弟这个不错，只是少了自定义的排序功能，要怎么加上呢？
wsh236 2009-01-09
- 打赏
- 举报
CREATE PROC sp_PageView
@tbname sysname, --要分页显示的表名
@FieldKey nvarchar(1000), --用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段
@PageCurrent int=1, -->0表示要显示的页码,如果为0表示仅清理缓存数据的临时表,不返回数据,其他值代表重建缓存数据的临时表
@PageSize int=10, --每页的大小(记录数)
@FieldShow nvarchar(1000)='', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@Where nvarchar(1000)='', --查询条件
@UserName sysname='', --调用查询的用户名
@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(@PageSize,0)<1 SET @PageSize=10
IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'
IF ISNULL(@Where,N'')=N''
SET @Where=N''
ELSE
SET @Where=N'WHERE ('+@Where+N')'
--分页数据缓存临时表状态检测
DECLARE @tempTable sysname,@TempField sysname,@TempTableDate datetime
SET @tempTable=QUOTENAME(N'##'
+RTRIM(LEFT(HOST_NAME(),50))
+N'_'+RTRIM(LEFT(CASE WHEN ISNULL(@UserName,N'')=N'' THEN SUSER_SNAME() ELSE @UserName END,50))
+N'_'+RTRIM(@tbname))
SELECT @TempField=QUOTENAME(c.name),
@TempTableDate=DATEADD(Hour,1,o.crdate) --临时表的有效缓存时间为1小时,创建时间超过1小时的临时表会被重建
FROM tempdb..sysobjects o,tempdb..syscolumns c
WHERE o.id=c.id
AND o.id=OBJECT_ID(N'tempdb..'+@tempTable)
AND c.status=0x80
IF @@ROWCOUNT>0
IF ISNULL(@PageCurrent,0)<1 OR @TempTableDate<GETDATE()
BEGIN
EXEC('DROP TABLE '+@tempTable)
IF @PageCurrent=0 RETURN
END
ELSE
GOTO lb_TempTable_Created
ELSE
SELECT @TempField=QUOTENAME(NEWID())
--创建分页数据缓存临时表
EXEC(N'SELECT *,IDENTITY(decimal(18,0),0,1) as '+@TempField
+N' INTO '+@tempTable
+N' FROM(SELECT TOP 100 PERCENT '+@FieldKey
+N' FROM '+@tbname
+N' '+@Where
+N' ORDER BY NEWID())a')
SET @PageCount=(@@ROWCOUNT+@PageSize-1)/@PageSize
GOTO lb_ShowData
lb_TempTable_Created:
--如果@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
lb_ShowData:
IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1
--计算分页显示的TOPN值
DECLARE @TopN varchar(20),@TopN1 varchar(20)
SELECT @TopN=(@PageCurrent-1)*@PageSize,
@TopN1=@PageCurrent*@PageSize-1
--生成主键(惟一键)处理条件
DECLARE @Field sysname
SET @Where=N''
WHILE CHARINDEX(N',',@FieldKey)>0
SELECT @Field=LEFT(@FieldKey,CHARINDEX(N',',@FieldKey)-1),
@FieldKey=STUFF(@FieldKey,1,CHARINDEX(N',',@FieldKey),N''),
@Where=@Where
+N' AND a.'+@Field+N'=b.'+@Field,
@FieldShow=REPLACE(@FieldShow,@Field,N'a.'+@Field)
SELECT @Where=STUFF(@Where+N' AND a.'+@FieldKey+N'=b.'+@FieldKey,1,5,N''),
@FieldShow=REPLACE(@FieldShow,@FieldKey,N'a.'+@FieldKey)
IF @FieldShow=N'*' SET @FieldShow=N'a.*'
--执行查询
EXEC(N'SELECT '+@FieldShow
+N' FROM '+@tbname
+N' a,'+@tempTable
+N'b WHERE (b.'+@TempField
+N' BETWEEN '+@TopN
+N' AND '+@TopN1
+N') AND ('+@Where
+N') ORDER BY b.'+@TempField)
fanyuanwaifdl 2009-01-09
- 打赏
- 举报
up
fanyuanwaifdl 2009-01-09
- 打赏
- 举报
[Quote=引用 4 楼 dawugui 的回复:]
引用 3 楼 zhongjingbao 的回复:
为什么第一页的时候会比其他页慢那么多，我测试18000万数据，都要3秒多，而其他页面只要0.2秒。
[/Quote]
up第一次执行需要把数据放入缓存中,以后的查询不需要进行前面那个步骤,所有快得多.
引用 3 楼 zhongjingbao 的回复:
为什么第一页的时候会比其他页慢那么多，我测试18000万数据，都要3秒多，而其他页面只要0.2秒。
[/Quote]
up第一次执行需要把数据放入缓存中,以后的查询不需要进行前面那个步骤,所有快得多.
zhongjingbao 2009-01-08
- 打赏
- 举报
为什么第一页的时候会比其他页慢那么多，我测试18000万数据，都要3秒多，而其他页面只要0.2秒。
zhongjingbao 2009-01-08
- 打赏
- 举报
为什么我加了and smt_id in(select smt_id from bb where key=1)只能第一页显示正常，后面不管翻到第几页，没都记录显示？
-狙击手- 2009-01-08
- 打赏
- 举报
declare @i int
exec fpage @tbname='ta',@FieldKey='id',@Where=' key=1 and smt_id in(select smt_id from bb where key=1) ',@PageCount = @i out
