sqlserver 存储过程分页，排序效率问题

wangyingdong 2008-09-26 02:37:18
小弟用的邹老大的存储过程，现在有数据20多万条数据，数据还在增加中。。。

在查询分析器里，执行语句:

exec p_show 'supplyhistory ',100,1 没加 order by 执行的速度超快，

但是加了order by 以后就很慢了，代码如下：

exec p_show 'SELECT top 100 percent SupplyHistory.*,CONVERT(varchar(10), supplyAddTime, 120) AS [date],CONVERT(varchar(10), supplyAddTime, 108) AS [time] from SupplyHistory order by [date] DESC,[time]',100,2


请问有什么办法可以提高查询的速度？

谢谢大家。。。。

/*--实现分页的通用存储过程

显示指定表、视图、查询结果的第X页
对于表中主键或标识列的情况,直接从原表取数查询，其它情况使用临时表的方法
如果视图或查询结果中有主键,不推荐此方法
如果使用查询语句,而且查询语句使用了order by,则查询语句必须包含top 语句

--邹建 2003.09(引用请保留此信息)--*/

/*--调用示例
exec p_show '地区资料'

exec p_show 'select top 100 percent * from 地区资料 order by 地区名称',5,3,'地区编号,地区名称,助记码'
--*/
CREATE Proc p_show
@QueryStr nvarchar(4000), --表名、视图名、查询语句
@PageSize int=10, --每页的大小(行数)
@PageCurrent int=1, --要显示的页
@FdShow nvarchar (4000)='', --要显示的字段列表,如果查询结果不需要标识字段,需要指定此值,且不包含标识字段
@FdOrder nvarchar (1000)='' --排序字段列表
as
set nocount on
declare @FdName nvarchar(250) --表中的主键或表、临时表中的标识列名
,@Id1 varchar(20),@Id2 varchar(20) --开始和结束的记录号
,@Obj_ID int --对象ID
--表中有复合主键的处理
declare @strfd nvarchar(2000) --复合主键列表
,@strjoin nvarchar(4000) --连接字段
,@strwhere nvarchar(2000) --查询条件


select @Obj_ID=object_id(@QueryStr)
,@FdShow=case isnull(@FdShow,'') when '' then ' *' else ' '+@FdShow end
,@FdOrder=case isnull(@FdOrder,'') when '' then '' else ' order by '+@FdOrder end
,@QueryStr=case when @Obj_ID is not null then ' '+@QueryStr else ' ('+@QueryStr+') a' end

--如果显示第一页，可以直接用top来完成
if @PageCurrent=1
begin
select @Id1=cast(@PageSize as varchar(20))
exec('select top '+@Id1+@FdShow+' from '+@QueryStr+@FdOrder)
return
end

--如果是表,则检查表中是否有标识更或主键
if @Obj_ID is not null and objectproperty(@Obj_ID,'IsTable')=1
begin
select @Id1=cast(@PageSize as varchar(20))
,@Id2=cast((@PageCurrent-1)*@PageSize as varchar(20))

select @FdName=name from syscolumns where id=@Obj_ID and status=0x80
if @@rowcount=0 --如果表中无标识列,则检查表中是否有主键
begin
if not exists(select 1 from sysobjects where parent_obj=@Obj_ID and xtype='PK')
goto lbusetemp --如果表中无主键,则用临时表处理

select @FdName=name from syscolumns where id=@Obj_ID and colid in(
select colid from sysindexkeys where @Obj_ID=id and indid in(
select indid from sysindexes where @Obj_ID=id and name in(
select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
)))
if @@rowcount>1 --检查表中的主键是否为复合主键
begin
select @strfd='',@strjoin='',@strwhere=''
select @strfd=@strfd+',['+name+']'
,@strjoin=@strjoin+' and a.['+name+']=b.['+name+']'
,@strwhere=@strwhere+' and b.['+name+'] is null'
from syscolumns where id=@Obj_ID and colid in(
select colid from sysindexkeys where @Obj_ID=id and indid in(
select indid from sysindexes where @Obj_ID=id and name in(
select name from sysobjects where xtype='PK' and parent_obj=@Obj_ID
)))
select @strfd=substring(@strfd,2,2000)
,@strjoin=substring(@strjoin,5,4000)
,@strwhere=substring(@strwhere,5,4000)
goto lbusepk
end
end
end
else
goto lbusetemp

/*--使用标识列或主键为单一字段的处理方法--*/
lbuseidentity:
exec('select top '+@Id1+@FdShow+' from '+@QueryStr
+' where '+@FdName+' not in(select top '
+@Id2+' '+@FdName+' from '+@QueryStr+@FdOrder
+')'+@FdOrder
)
print 'select top '+@Id1+@FdShow+' from '+@QueryStr
+' where '+@FdName+' not in(select top '
+@Id2+' '+@FdName+' from '+@QueryStr+@FdOrder
+')'+@FdOrder
return

/*--表中有复合主键的处理方法--*/
lbusepk:
exec('select '+@FdShow+' from(select top '+@Id1+' a.* from
(select top 100 percent * from '+@QueryStr+@FdOrder+') a
left join (select top '+@Id2+' '+@strfd+'
from '+@QueryStr+@FdOrder+') b on '+@strjoin+'
where '+@strwhere+') a'
)
print ('select '+@FdShow+' from(select top '+@Id1+' a.* from
(select top 100 percent * from '+@QueryStr+@FdOrder+') a
left join (select top '+@Id2+' '+@strfd+'
from '+@QueryStr+@FdOrder+') b on '+@strjoin+'
where '+@strwhere+') a'
)
return

/*--用临时表处理的方法--*/
lbusetemp:
select @FdName='[ID_'+cast(newid() as varchar(40))+']'
,@Id1=cast(@PageSize*(@PageCurrent-1) as varchar(20))
,@Id2=cast(@PageSize*@PageCurrent-1 as varchar(20))

exec('select '+@FdName+'=identity(int,0,1),'+@FdShow+'
into #tb from'+@QueryStr+@FdOrder+'
select '+@FdShow+' from #tb where '+@FdName+' between '
+@Id1+' and '+@Id2
)

print ('select '+@FdName+'=identity(int,0,1),'+@FdShow+'
into #tb from'+@QueryStr+@FdOrder+'
select '+@FdShow+' from #tb where '+@FdName+' between '
+@Id1+' and '+@Id2
)

GO

...全文
330 32 打赏 收藏 转发到动态 举报
写回复
用AI写文章
32 条回复
切换为时间正序
请发表友善的回复…
发表回复
Atai-Lu 2008-10-10
  • 打赏
  • 举报
 回复
是单表么？
单表的话用这个试试


if not object_id('Cdt_changePage') is null drop procedure Cdt_changePage

go

create procedure [dbo].[Cdt_changePage]

@pagesize int,              --每页显示的数据数

@pageindex int,             --当前页码

@tbName varchar(20),        --表名

@columns varchar(600),      --需要查询的字段

@keyIndex varchar(20),      --自增字段名(自动编号)

@where varchar(200),        --查询条件,不用写where

@order varchar(200)         --排序,不用写order by



as 

declare @strSQL varchar(3000)

--准备分页

declare @datacount int --总记录数

declare @pagecount int --总页数



if @order=''

	begin

		set @order=' '+@keyIndex+' desc'

	end

--获取总记录数

	if not object_id('#cdt_tem') is null drop table #cdt_tem

	create table dbo.#cdt_tem(datacount int)

	exec('insert #cdt_tem select count(*) as datacount from '+@tbName+' where 1=1 '+@where)

	set @datacount=(select top 1 datacount from #cdt_tem)

	if not object_id('#cdt_tem') is null drop table #cdt_tem

--计算总页数

if(@datacount%@pagesize)=0

	begin

		set @pagecount=(@datacount/@pagesize)

	end

else

	begin

		set @pagecount=(@datacount/@pagesize)+1

	end



--开始分页

if @pageindex<=1

	begin

	  set @strSQL = 'select top '+str(@pagesize)+' '+str(@datacount)+' as datacount, '+str(@pagecount)+' as pagecount, '+@columns+'

		from '+@tbName+' where 1=1 '+@where+' order by '+@order

	end

else

	begin

	  set @strSQL = 'select top '+str(@pagesize)+' '+str(@datacount)+' as datacount, '+str(@pagecount)+' as pagecount, '+@columns+'

		from '+@tbName+' where '+@keyIndex+'

			not in(select top '+str((@pageindex-1)*@pagesize)+' '+@keyIndex+'

			from '+@tbName+' where 1=1 '+@where+' order by '+@order+')

		'+@where+' order by '+@order

	end



	exec (@strSQL)

go

--调用示例：对表dict分页,每页10条，显示第一页，Id为标识字段

	exec Cdt_changePage 10,1,'dict','*','Id','','Id asc'
delphi_new 2008-10-10
  • 打赏
  • 举报
 回复 


------------------------------------

--用途：分页存储过程(对有主键的表效率极高)  

--说明：

------------------------------------



CREATE PROCEDURE UP_GetRecordByPage

    @tblName      varchar(255),       -- 表名

    @fldName      varchar(255),       -- 主键字段名

    @PageSize     int = 10,           -- 页尺寸

    @PageIndex    int = 1,            -- 页码

    @IsReCount    bit = 0,            -- 返回记录总数, 非 0 值则返回

    @OrderType    bit = 0,            -- 设置排序类型, 非 0 值则降序

    @strWhere     varchar(1000) = ''  -- 查询条件 (注意: 不要加 where)

AS



declare @strSQL   varchar(6000)       -- 主语句

declare @strTmp   varchar(100)        -- 临时变量

declare @strOrder varchar(400)        -- 排序类型



if @OrderType != 0

begin

    set @strTmp = '<(select min'

    set @strOrder = ' order by [' + @fldName +'] desc'

end

else

begin

    set @strTmp = '>(select max'

    set @strOrder = ' order by [' + @fldName +'] asc'

end



set @strSQL = 'select top ' + str(@PageSize) + ' * from ['

    + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['

    + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['

    + @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'

    + @strOrder



if @strWhere != ''

    set @strSQL = 'select top ' + str(@PageSize) + ' * from ['

        + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['

        + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['

        + @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '

        + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder



if @PageIndex = 1

begin

    set @strTmp =''

    if @strWhere != ''

        set @strTmp = ' where ' + @strWhere



    set @strSQL = 'select top ' + str(@PageSize) + ' * from ['

        + @tblName + ']' + @strTmp + ' ' + @strOrder

end



if @IsReCount != 0

    set @strSQL = 'select count(*) as Total from [' + @tblName + ']'+' where ' + @strWhere



exec (@strSQL)
wangyingdong 2008-10-09
  • 打赏
  • 举报
 回复
。。。。大家帮忙看看啊。谢谢。。
linshi988 2008-09-27
  • 打赏
  • 举报
 回复
强人，学习
gingerkang 2008-09-26
  • 打赏
  • 举报
 回复
到sqlserver版去问吧,对这方面的问题,那里有更多更厉害的强人.
wangyingdong 2008-09-26
  • 打赏
  • 举报
 回复
[Quote=引用 24 楼 doordie 的回复:]
索引建在supplyAddTime字段上,修改查询语句中的order by:

exec p_show 'SELECT top 100 percent SupplyHistory.*,CONVERT(varchar(10), supplyAddTime, 120) AS [date],CONVERT(varchar(10), supplyAddTime, 108) AS [time] from SupplyHistory order by supplyAddTime DESC',100,2

不得不说,你原来写的order by很怪异,按天数倒序,按小时升序.如果不是因为奇怪的需求的话,直接给你改成倒序了
[/Quote]

排序是客户需要的，呵呵。。

两个字段的联合索引 。。怎么建啊？
doordie 2008-09-26
  • 打赏
  • 举报
 回复
csdn的缓存有点毛病
doordie 2008-09-26
  • 打赏
  • 举报
 回复
咦,我的回帖呢?

补一下

日期 ： supplyAddSmallDate [smalldatetime]

字符 : supplyAddSmallTime [nvarchar]

分成两个字段后,按照你的order by,应该建立两个字段的联合索引
doordie 2008-09-26
  • 打赏
  • 举报
 回复
索引建在supplyAddTime字段上,修改查询语句中的order by:

exec p_show 'SELECT top 100 percent SupplyHistory.*,CONVERT(varchar(10), supplyAddTime, 120) AS [date],CONVERT(varchar(10), supplyAddTime, 108) AS [time] from SupplyHistory order by supplyAddTime DESC',100,2

不得不说,你原来写的order by很怪异,按天数倒序,按小时升序.如果不是因为奇怪的需求的话,直接给你改成倒序了
wangyingdong 2008-09-26
  • 打赏
  • 举报
 回复
帮帮忙啊。。各位大侠。。。
wangyingdong 2008-09-26
  • 打赏
  • 举报
 回复
分开后：

exec p_show 'SELECT top 100 percent * from supplyHistory order by [supplyAddSmallDate] DESC,[supplyAddSmallTime]',100,2

执行时间：6秒或7秒。。。

还是很慢。。。
wangyingdong 2008-09-26
  • 打赏
  • 举报
 回复
我也尝试着把 supplyAddTime 分为两个字段

日期 ： supplyAddSmallDate [smalldatetime]

字符 : supplyAddSmallTime [nvarchar]


如：2008-9-26 15:32:00

然后对他们两个建索引，结果还是很慢。。。。。
sy_binbin 2008-09-26
  • 打赏
  • 举报
 回复
那就是CONVERT这个函数的事 了
wangyingdong 2008-09-26
  • 打赏
  • 举报
 回复
没建索引执行：
exec p_show 'SELECT top 100 percent SupplyHistory.*,CONVERT(varchar(10), supplyAddTime, 120) AS [date],CONVERT(varchar(10), supplyAddTime, 108) AS [time] from SupplyHistory order by [date] DESC,[time]',100,2

需要8秒。

建了

CREATE INDEX supply ON supplyHistory (supplyaddtime)

在执行

exec p_show 'SELECT top 100 percent SupplyHistory.*,CONVERT(varchar(10), supplyAddTime, 120) AS [date],CONVERT(varchar(10), supplyAddTime, 108) AS [time] from SupplyHistory order by [date] DESC,[time]',100,2


执行也是7秒或8秒。。。


sy_binbin 2008-09-26
  • 打赏
  • 举报
 回复
网上说SQL语句里用了函数，那么这个字段一般是不走索引的

是不是CONVERT这个函数的问题呢
gingerkang 2008-09-26
  • 打赏
  • 举报
 回复
查询分析器ctrl+l查看执行计划,数据量大,无索引,肯定慢,你的排序的用的是计算得到的列,根本用不到索引,可尝试索引视图
sy_binbin 2008-09-26
  • 打赏
  • 举报
 回复
我 感觉还是索引出问题了

没建supplyaddtime这个索引的 时候排序需要多长时间？？？

penglewen 2008-09-26
  • 打赏
  • 举报
 回复
排序肯定会影响执行时间的，
你以那个字段排序，就在那个字段上面建索引。。索引的作用就是在你读之前已经帮你在数据库中排好序了。
jhwcd 2008-09-26
  • 打赏
  • 举报
 回复
最好不要用×，那样肯定会很慢，需要多少字段就显示多少字段。
wangyingdong 2008-09-26
  • 打赏
  • 举报
 回复
SupplyHistory这张表里字段全是nvarchar,int,datetime 基本上都是需要的，所以就直接用 *

如果不要order by 的话那速度是超快，是不是order by 出了问题了？

加载更多回复（12）
sql server 通用分页存储过程
1.查询返回的表、列名以及排序列没有写死，可以根据需要将这些放到存储过程的输入参数中； 2.适用于所有需要分页的单表或多表联合查询。
sqlserver 存储过程分页(按多条件排序)
sqlserver 存储过程分页，并支持条件排序，需要的朋友可以参考下。
SQLServer2008分页存储过程和java调用
PagingOperation数据库SQLServer2008分页存储过程(和java调用存储过程源代码)： 调用存储过程进行分页查询速度快，尤其是数据量超过50万条的表非常重要，此代码是我们项目中使用的，实际使用情况稳定，也希望大家提出更加合理的修改建议。排序字段(必须!支持多字段) -- 条件语句(不用加where)-- 指定当前为第几页 每页多少条记录 --分组语句(不用加Group by) --返回总记录条数
基于Sql Server通用分页存储过程的解决方法
sql server2000分页方式很多，效率吗  当到达10万级以上就有些吃力了，这里暂时不例出来了 sql server2005以上就有了row_number 也是一大进步，详情如下参考 代码如下:Sql Server2005通用分页存储过程 CREATE PROCEDURE [dbo].[Common_GetPagedList](@TableName nvarchar(100), –表名@ColumnNames nvarchar(1000) = ‘*’, –字段名集合(全部字段为*，其它逗号分隔)@OrderClause nvarchar(1000), –排序从句(不包含order b
Server2005高效分页存储过程（支持多字段排序）
Server2005/2008专用高效分页存储过程（支持多字段排序）.
ASP

28,391

社区成员

357,059

社区内容

发帖
与我相关
我的任务
社区描述
ASP即Active Server Pages，是Microsoft公司开发的服务器端脚本环境。
社区管理员
  • ASP
  • 无·法
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

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

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

+ 用AI写文章