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

Question

小弟用的邹老大的存储过程，现在有数据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 

Answer 1

create index ind1 on SupplyHistory([date] DESC, [time])

Answer 2

提示错误：服务器: 消息 1911，级别 16，状态 1，行 1
目标表中不存在列名 'date'。
谢谢大家在线等

Answer 3

CREATE INDEX supply ON supplyHistory (supplyaddtime) 是这么建的吗？ 查询的结果还是很慢，要8秒 谢谢大家。。很着急在线等。

Answer 4

CONVERT(varchar(10), supplyAddTime, 120) AS [date],
晕，原来是计算出来的，那在表里加一个计算列（存储数据），然后再加索引，
查询和排序都直接使用这个计算列

Answer 5

我也尝试着把 supplyAddTime 分为两个字段 日期 ： supplyAddSmallDate  [smalldatetime] 字符 :  supplyAddSmallTime [nvarchar]
如：2008-9-26   15:32:00然后对他们两个建索引，结果还是很慢。。

Answer 6

--try
go
alter table SupplyHistory add [date] as convert(varchar(10), dt, 120) PERSISTED
go
create index ind1 on SupplyHistory([date] DESC, [time]) 

Answer 7

查询的时候：
CONVERT(varchar(10), supplyAddTime, 120) AS [date], 
---->
[date], 

Answer 8

谢谢楼上的大侠们。在查询分析器里执行错误。。这句话是增加新列吗？我已经增加了新列，已经把 supplyAddTime 分为两个字段 比如： 2008-9-26 16:02:15日期 ： supplyAddSmallDate  [smalldatetime] 值为: 2008-9-26 字符 :  supplyAddSmallTime [nvarchar] 值为: 16:02:15也分别做了索引了。exec p_show 'SELECT top 100 percent *  from  SupplyHistory order by supplyAddSmallDate  desc,supplyAddSmallTime ',100,2 8秒还是很慢
接着等。。

Answer 9

分成两个字段，但不要分别建索引，建一个就够了：
create index ind1 on SupplyHistory(supplyAddSmallDate desc, supplyAddSmallTime)BTW:
白天上班，没仔细看，原来date,time是同一个字段拆开的，，，

Answer 10

谢谢楼上的各位大侠，用
create index ind1 on SupplyHistory(supplyAddSmallDate desc, supplyAddSmallTime)之后，查询第一页很快，第二页还是在8秒左右。接着提问

Answer 11

以我的经验,"如果使用查询语句,而且查询语句使用了order by,则查询语句必须包含top 语句"这段注释没道理,top语句不应该放进去. 我建议你去掉top 100再试试