34,593
社区成员
发帖
与我相关
我的任务
分享
--建立索引
create index idx_date on tb(date desc)
go
select top 100 * from tab;
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Psp_SetPagination]
(
@vc_Table varchar(50),--表名
@vc_SqlWhere varchar(1000)='',--条件
@vc_SqlOrder varchar(1000)='',--排序规则
@vc_SqlFile varchar(1000)='*',--选取字段
@vc_Key varchar(50)='',--关键字
@vc_FieldType varchar(50)='',--字段类型
@i_PageSize int=1,--分页大小
@i_PageIndex int=1,--页面索引
@bt_Count bit --在统计吗?
)
As
set nocount on
Declare @vc_Sql varchar(5000)
if @bt_Count=1
BEGIN
set @vc_Sql='select count(*) from '+@vc_Table+' '+@vc_SqlWhere
END
ELSE
BEGIN
set @vc_Sql='Declare @indexTable Table(Id decimal identity(1,1),Temp_Key '+@vc_FieldType+ ')'+char(13)
set @vc_Sql=@vc_Sql+
'Declare @PageLowerBound int'+char(13)
set @vc_Sql=@vc_Sql+
'Declare @PageUpperBound int'+char(13)
set @vc_Sql=@vc_Sql+
'set @PageLowerBound='+cast((@i_PageIndex-1)*@i_PageSize as varchar(8))+char(13)
set @vc_Sql=@vc_Sql+
'set @PageUpperBound=@PageLowerBound+'+cast(@i_PageSize as varchar(8))+char(13)
set @vc_Sql=@vc_Sql+
'set rowcount @PageUpperBound'+char(13)
set @vc_Sql=@vc_Sql+
'Insert into @indexTable(Temp_Key)'+
'Select '+@vc_Key+' from '+@vc_Table+' '+@vc_SqlWhere+' '+@vc_SqlOrder+char(13)
if @vc_SqlWhere<>''
begin
set @vc_SqlWhere=@vc_SqlWhere+' and '+@vc_Key+'=t.Temp_Key and t.Id>@PageLowerBound and t.Id<=@PageUpperBound'
end
else
begin
set @vc_SqlWhere='Where '+@vc_Key+'=t.Temp_Key and t.Id>@PageLowerBound and t.Id<=@PageUpperBound'
end
set @vc_Sql=@vc_Sql+
'Select '+@vc_SqlFile+' from '+@vc_Table+',@indexTable t '+@vc_SqlWhere+' Order by t.Id'
END
EXEC(@vc_Sql)
--方案一,建立索引
create index idx_date on tb(date desc)
--方案二,建立一个同结构的数据表,只存放最近几天的数据
create table tb2
(
)
当然,在插入数据时,需要同时维护2个表,但是却可以获得最好的查询效率
加个逆序索引,如果原来已存在就先删除
create index idx_date on tb(date desc)
create index idx_date on tb(date desc)
go
select top 100 * from tab;