求救多字段排序的分页存储过程问题？

wgybb 2008-03-19 11:23:40

我目前用的存储过程是利有临时表来实现的，这是以前直接在网上找的，那时候对存储过程不太熟悉，也不在意效率这个问题，现在的数据表里面将近有3万条记录，这几天发现网站有时打开很慢，通过SQL事件探查器我发现，执行存储过程的时候占用很大的CPU，主要是向临时表添加数据那个操作，CPU执行的时间最高时有1800(毫秒)左右，我想是不是用临时表来实现分页存储过程效率不高，必竞它每次都要把数据向临时表添加一次，然后还有一个删除临时表的操作．这两天上网找了一些有关存储过程的代码再看，但大部分是通过单一字段或主键排序的，也有一些多字段排序是利用IN和NOT IN来实现的(感觉这个效率也不不会太高吧)，所以在这儿想请教一下，我把我的表结构和用的存储过程代码贴出来，希望各位老大耐心地看一下，帮我分析一下用哪种方法的分页存储过程效率上会好一些，能提供代码参考，就更感谢了(因为最近刚了解存储过程这一块，知识欠缺)，多谢了，书到用时方知少呀(QQ:77826402)

主表Buy:


CREATE TABLE [dbo].[buy] (

	[ID] [int] IDENTITY (1, 1) NOT NULL ,

	[Title] [varchar] (128) COLLATE Chinese_PRC_CI_AS NOT NULL ,

	[Content] [text] COLLATE Chinese_PRC_CI_AS NULL ,

	[SmallClassID] [smallint] NULL ,

	[Picture] [char] (32) COLLATE Chinese_PRC_CI_AS NULL ,

	[Type] [char] (16) COLLATE Chinese_PRC_CI_AS NULL ,

	[Mode] [char] (16) COLLATE Chinese_PRC_CI_AS NULL ,

	[Money] [char] (16) COLLATE Chinese_PRC_CI_AS NULL ,

	[RegDate] [datetime] NULL ,

	[validity] [smallint] NULL ,

	[InfoType] [tinyint] NULL ,

	[UserName] [char] (16) COLLATE Chinese_PRC_CI_AS NULL ,

	[UserType] [tinyint] NULL ,

	[QQ] [char] (16) COLLATE Chinese_PRC_CI_AS NULL ,

	[Tel] [char] (32) COLLATE Chinese_PRC_CI_AS NULL ,

	[Email] [char] (32) COLLATE Chinese_PRC_CI_AS NULL ,

	[LinkMan] [char] (16) COLLATE Chinese_PRC_CI_AS NULL ,

	[Shi] [smallint] NULL ,

	[Qu] [smallint] NULL ,

	[IP] [char] (16) COLLATE Chinese_PRC_CI_AS NULL ,

	[IsCheck] [bit] NULL 

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO



ALTER TABLE [dbo].[buy] WITH NOCHECK ADD 

	CONSTRAINT [PK_buy] PRIMARY KEY  CLUSTERED 

	(

		[ID]

	)  ON [PRIMARY] 

GO



 CREATE  INDEX [IX_SmallClassID] ON [dbo].[buy]([SmallClassID]) ON [PRIMARY]

GO



 CREATE  INDEX [IX_shi] ON [dbo].[buy]([Shi]) ON [PRIMARY]

GO



 CREATE  INDEX [IX_qu] ON [dbo].[buy]([Qu]) ON [PRIMARY]

GO


目前用的存储过程如下：


CREATE PROCEDURE dbo.getBuyList

@iPageCount int OUTPUT, --总页数

@iPage int, --当前页号

@iPageSize int, --每页记录数



@strTemp nvarchar(200) --搜索条件



as

set nocount on

begin

if @strTemp is null return



--创建临时表 

create table #TempBuy (ID int IDENTITY, --自增字段

Buy_ID int,

Buy_InfoType tinyint,

Buy_Type nvarchar(16),

Buy_Title nvarchar(128),

Buy_Money nvarchar(16),

Buy_RegDate datetime,

Buy_Content text,

Buy_Picture nvarchar(32),

Buy_SmallClassID smallint,

Buy_LinkMan nvarchar(16),

Buy_Tel nvarchar(32),

Buy_shi nvarchar(16),

Buy_qu nvarchar(16),

Buy_SmallClassName nvarchar(16))



Declare @strSQL nvarchar(1000)



Set @strSQL = N'insert into #TempBuy(Buy_ID,Buy_InfoType,Buy_Type,Buy_Title,Buy_Money,Buy_RegDate,Buy_Content,Buy_Picture,Buy_SmallClassID,Buy_LinkMan,Buy_Tel,Buy_shi,Buy_qu,Buy_SmallClassName) SELECT Buy.ID,Buy.InfoType,Buy.Type,Buy.Title,Buy.Money,Buy.RegDate,Buy.Content,Buy.Picture,Buy.SmallClassID,Buy.LinkMan,Buy.Tel,shi.shi,qu.qu,SmallClass1.SmallClassName FROM (((Buy INNER JOIN shi ON Buy.shi=shi.ID)) INNER JOIN qu ON Buy.qu=qu.ID) INNER JOIN SmallClass1 ON  Buy.SmallClassID=SmallClass1.ID  WHERE ' + @strTemp + ' order by case infoType when 0 then 0 else 1 end desc,RegDate desc,Buy.ID Desc;'



--向临时表中写入数据

execute sp_executesql @strSQL





--取得记录总数 

declare @iRecordCount int

set @iRecordCount = @@rowcount



--确定总页数

IF @iRecordCount%@iPageSize=0

SET @iPageCount=CEILING(@iRecordCount/@iPageSize)

ELSE

SET @iPageCount=CEILING(@iRecordCount/@iPageSize)+1



--若请求的页号大于总页数，则显示最后一页

IF @iPage > @iPageCount

SELECT @iPage = @iPageCount



--确定当前页的始末记录

DECLARE @iStart int --start record

DECLARE @iEnd int --end record

SELECT @iStart = (@iPage - 1) * @iPageSize

SELECT @iEnd = @iStart + @iPageSize + 1



--取当前页记录 

select * from #TempBuy where [ID]>@iStart and [ID]<@iEnd



--删除临时表

DROP TABLE #TempBuy



--返回记录总数

return @iRecordCount

end

GO


进行排序的字段如下：

order by case infoType when 0 then 0 else 1 end desc,RegDate desc,Buy.ID Desc;


附加说明：存储过程里面用到Buy这个表与shi,qu,SmallClass1这三个表进行了关联,这三个表的数据量是固定的，都非常小，不超过100条记录．主要是Buy这个表的数据在不断增加．
...全文
182 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
lai18721432393 2011-11-30
  • 打赏
  • 举报
 回复
看看怎么样。
wgybb 2008-03-20
  • 打赏
  • 举报
 回复
dawugui 潇洒老乌龟(爱新觉罗.毓华)　
wzy_love_sly 世界因为有你而精彩~ぐ~（紫煈）

两位发的存储过程，哪个效率更高一些？
viva369 2008-03-20
  • 打赏
  • 举报
 回复
如果数据量不大就采用2005的排名函数做排序吧
--
效率问题可以为连接字段添加索引，如果要支持百万多表数据的任意字段排序，就要采用特殊的处理方式了
wgybb 2008-03-20
  • 打赏
  • 举报
 回复
怎么只显示个回复数，看不到回复的内容呢．
wzy_love_sly 2008-03-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

dawugui 2008-03-19
  • 打赏
  • 举报
 回复 
-- 临时表缓存实现的通用分页存储过程

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*@PageSize



--第一页直接显示

IF @PageCurrent=1

	EXEC(N'SELECT TOP '+@TopN

		+N' '+@FieldShow

		+N' FROM '+@tbname

		+N' '+@Where

		+N' '+@FieldOrder)

ELSE

BEGIN

	--生成主键(惟一键)处理条件

	DECLARE @Where1 nvarchar(4000),@s nvarchar(1000)

	SELECT @Where1=N'',@s=@FieldKey

	WHILE CHARINDEX(N',',@s)>0

		SELECT @s=STUFF(@s,1,CHARINDEX(N',',@s),N''),

			@Where1=@Where1

				+N' AND a.'+LEFT(@s,CHARINDEX(N',',@s)-1)

				+N'='+LEFT(@s,CHARINDEX(N',',@s)-1)

	SELECT @Where1=STUFF(@Where1+N' AND a.'+@s+N'='+@s,1,5,N''),

		@TopN=@TopN1-@PageSize



	--执行查询

	EXEC(N'SET ROWCOUNT '+@TopN1

		+N' SELECT '+@FieldKey

		+N' INTO # FROM '+@tbname

		+N' '+@Where

		+N' '+@FieldOrder

		+N' SET ROWCOUNT '+@TopN

		+N' DELETE FROM #'

		+N' SELECT '+@FieldShow

		+N' FROM '+@tbname

		+N' a WHERE EXISTS(SELECT * FROM # WHERE '+@Where1

		+N') '+@FieldOrder)

END
-狙击手- 2008-03-19
  • 打赏
  • 举报
 回复
太长，想干什么
wzy_love_sly 2008-03-19
  • 打赏
  • 举报
 回复
用老大写的，因该速度不错
dawugui 2008-03-19
  • 打赏
  • 举报
 回复
喝多了,帮顶.

呼叫石头.
数据库帖子收集
这样的数据列表在存储过程中应该怎么样选择得到？ 请问造成SQL2000服务不能启动的原因有哪些？ sql server中有無類似于if 的函數,即像這樣select if(cancel=1,'cancel','') from sales 这样怎么返回呀！！！！...
WEB开发文档2 总结
http://blog.donews.com/lvjiyong/archive/2006/06/29/931071.aspx怎样将后台生成的在内存中的图象显示到客户端Microsoft IE WebControls下载...显示数据下载中文名文件时保存文件名乱码问题关于用ASP.net绘图的问题...
MS-SQL Server 基础类 - SQL语句
sql:对字段排序并取其序号 一个有关当前记录号的问题。（类似功能） 先排序，然后根据排序内联怎么做？比如…… 跨服务器的SQL语句如何书写？？ 分组统计查询难题 列举路径 关于分组，高手请来看看 如何比较两个表中...
C++笔试题目大全
1 c++ c++ c++ c++ 笔试题汇总 ...最容易想到的方法遍历一遍链表，利用一个辅助指针，存储遍历过程中当前指针指向的下一个 元 素，然后将当前节点元素的指针反转后，利用已经存储的指针往后面继续遍
编程资料 －C# 多线程
编程资料 － 多线程 C#多线程编程实例实战 作者： 刘弹 www.ASPCool.com 时间:2003-5-17 上午 10:24:05 阅读次数:10996 单个... 问题的提出 所谓单个写入程序/多个阅读程序的线程同步问
MS-SQL Server

34,593

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章