分页查询sql SQL大数据量分页存储过程效率测试

SQL大数据量分页存储过程效率测试  

　　 alter PROCEDURE proc_paged_ part_selectMax

　　 (

　　 @tblName     nvarchar( )         要显示的表或多个表的连接

　　 @fldName     nvarchar( ) = *     要显示的字段列表

　　 @pageSize    int =         每页显示的记录个数

　　 @page        int =         要显示那一页的记录

　　 @fldSort    nvarchar( ) = null     排序字段列表或条件

　　 @Sort        bit =         排序方法 为升序 为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记) 程序传参如 SortA Asc SortB Desc SortC )

　　 @strCondition    nvarchar( ) = null     查询条件 不需where

　　 @ID        nvarchar( )         主表的主键

　　 @Dist                 bit =            是否添加查询字段的 DISTINCT 默认 不添加/ 添加

　　 @pageCount    int = output             查询结果分页后的总页数

　　 @Counts    int = output                查询到的记录数

　　 )

　　 AS

　　 SET NOCOUNT ON

　　 Declare @sqlTmp nvarchar( )        存放动态生成的SQL语句

　　 Declare @strTmp nvarchar( )        存放取得查询结果总数的查询语句

　　 Declare @strID     nvarchar( )        存放取得查询开头或结尾ID的查询语句

　　 Declare @strSortType nvarchar( )    数据排序规则A

　　 Declare @strFSortType nvarchar( )    数据排序规则B

　　 Declare @SqlSelect nvarchar( )         对含有DISTINCT的查询进行SQL构造

　　 Declare @SqlCounts nvarchar( )          对含有DISTINCT的总数查询进行SQL构造

　　 declare @timediff datetime  耗时测试时间差

　　 select @timediff=getdate()

　　 if @Dist  =

　　 begin

　　     set @SqlSelect = select

　　     set @SqlCounts = Count(*)

　　 end

　　 else

　　 begin

　　     set @SqlSelect = select distinct

　　     set @SqlCounts = Count(DISTINCT +@ID+ )

　　 end

　　 if @Sort=

　　 begin

　　     set @strFSortType= ASC

　　     set @strSortType= DESC

　　 end

　　 else

　　 begin

　　     set @strFSortType= DESC

　　     set @strSortType= ASC

　　 end

　　 生成查询语句

　　 此处@strTmp为取得查询结果数量的语句

　　 if @strCondition is null or @strCondition=      没有设置显示条件

　　 begin

　　     set @sqlTmp =  @fldName + From + @tblName

　　     set @strTmp = @SqlSelect+ @Counts= +@SqlCounts+ FROM +@tblName

　　     set @strID = From + @tblName

　　 end

　　 else

　　 begin

　　     set @sqlTmp = + @fldName + From + @tblName + where ( > ) + @strCondition

　　     set @strTmp = @SqlSelect+ @Counts= +@SqlCounts+ FROM +@tblName + where ( > ) + @strCondition

　　     set @strID = From + @tblName + where ( > ) + @strCondition

　　 end

　　 取得查询结果总数量

　　 exec sp_executesql @strTmp N @Counts int out @Counts out

　　 declare @tmpCounts int

　　 if @Counts =

　　     set @tmpCounts =

　　 else

　　     set @tmpCounts = @Counts

　　     取得分页总数

　　     set @pageCount=(@tmpCounts+@pageSize )/@pageSize

　　     /**当前页大于总页数 取最后一页**/

　　     if @page>@pageCount

　　         set @page=@pageCount

　　     declare @pageIndex int 总数/页大小

　　     declare @lastcount int 总数%页大小

　　     set @pageIndex = @tmpCounts/@pageSize

　　     set @lastcount = @tmpCounts%@pageSize

　　     if @lastcount >

　　         set @pageIndex = @pageIndex +

　　     else

　　         set @lastcount = @pagesize

　　     //***显示分页

　　     if @strCondition is null or @strCondition=      没有设置显示条件

　　     begin

　　         if @pageIndex< or @page<=@pageIndex / + @pageIndex %    前半部分数据处理

　　             begin

　　                 if @page=

　　                     set @strTmp=@SqlSelect+ top + CAST(@pageSize as VARCHAR( ))+ + @fldName+ from +@tblName

　　                         + order by + @fldSort + + @strFSortType

　　                 else

　　                 begin

　　                     if @Sort=

　　                     begin

　　                     set @strTmp=@SqlSelect+ top + CAST(@pageSize as VARCHAR( ))+ + @fldName+ from +@tblName

　　                         + where +@ID+ <(select min( + @ID + ) from ( + @SqlSelect+ top + CAST(@pageSize*(@page ) as Varchar( )) + + @ID + from +@tblName

　　                         + order by + @fldSort + + @strFSortType+ ) AS TBMinID)

　　                         + order by + @fldSort + + @strFSortType

　　                     end

　　                     else

　　                     begin

　　                     set @strTmp=@SqlSelect+ top + CAST(@pageSize as VARCHAR( ))+ + @fldName+ from +@tblName

　　                         + where +@ID+ >(select max( + @ID + ) from ( + @SqlSelect+ top + CAST(@pageSize*(@page ) as Varchar( )) + + @ID + from +@tblName

　　                         + order by + @fldSort + + @strFSortType+ ) AS TBMinID)

　　                         + order by + @fldSort + + @strFSortType

　　                     end

　　                 end

　　             end

　　         else

　　             begin

　　             set @page = @pageIndex @page+ 后半部分数据处理

　　                 if @page <= 最后一页数据显示

　　                     set @strTmp=@SqlSelect+ * from ( +@SqlSelect+ top + CAST(@lastcount as VARCHAR( ))+ + @fldName+ from +@tblName

　　                         + order by + @fldSort + + @strSortType+ ) AS TempTB + order by + @fldSort + + @strFSortType

　　                 else

　　                     if @Sort=

　　                     begin

　　                     set @strTmp=@SqlSelect+ * from ( +@SqlSelect+ top + CAST(@pageSize as VARCHAR( ))+ + @fldName+ from +@tblName

　　                         + where +@ID+ >(select max( + @ID + ) from( + @SqlSelect+ top + CAST(@pageSize*(@page )+@lastcount as Varchar( )) + + @ID + from +@tblName

　　                         + order by + @fldSort + + @strSortType+ ) AS TBMaxID)

　　                         + order by + @fldSort + + @strSortType+ ) AS TempTB + order by + @fldSort + + @strFSortType

　　                     end

　　                     else

　　                     begin

　　                     set @strTmp=@SqlSelect+ * from ( +@SqlSelect+ top + CAST(@pageSize as VARCHAR( ))+ + @fldName+ from +@tblName

　　                         + where +@ID+ <(select min( + @ID + ) from( + @SqlSelect+ top + CAST(@pageSize*(@page )+@lastcount as Varchar( )) + + @ID + from +@tblName

　　                         + order by + @fldSort + + @strSortType+ ) AS TBMaxID)

　　                         + order by + @fldSort + + @strSortType+ ) AS TempTB + order by + @fldSort + + @strFSortType

　　                     end

　　             end

　　     end

　　     else 有查询条件

　　     begin

　　         if @pageIndex< or @page<=@pageIndex / + @pageIndex %    前半部分数据处理

　　         begin

　　                 if @page=

　　                     set @strTmp=@SqlSelect+ top + CAST(@pageSize as VARCHAR( ))+ + @fldName+ from +@tblName

　　                         + where = + @strCondition + order by + @fldSort + + @strFSortType

　　                 else if(@Sort= )

　　                 begin

　　                     set @strTmp=@SqlSelect+ top + CAST(@pageSize as VARCHAR( ))+ + @fldName+ from +@tblName

　　                         + where +@ID+ <(select min( + @ID + ) from ( + @SqlSelect+ top + CAST(@pageSize*(@page ) as Varchar( )) + + @ID + from +@tblName

　　                         + where ( = ) + @strCondition + order by + @fldSort + + @strFSortType+ ) AS TBMinID)

　　                         + + @strCondition + order by + @fldSort + + @strFSortType

　　                 end

　　                 else

　　                 begin

　　                     set @strTmp=@SqlSelect+ top + CAST(@pageSize as VARCHAR( ))+ + @fldName+ from +@tblName

　　                         + where +@ID+ >(select max( + @ID + ) from ( + @SqlSelect+ top + CAST(@pageSize*(@page ) as Varchar( )) + + @ID + from +@tblName

　　                         + where ( = ) + @strCondition + order by + @fldSort + + @strFSortType+ ) AS TBMinID)

　　                         + + @strCondition + order by + @fldSort + + @strFSortType

　　                 end

　　         end

　　         else

　　         begin

　　             set @page = @pageIndex @page+ 后半部分数据处理

　　             if @page <= 最后一页数据显示

　　                     set @strTmp=@SqlSelect+ * from ( +@SqlSelect+ top + CAST(@lastcount as VARCHAR( ))+ + @fldName+ from +@tblName

　　                         + where ( = ) + @strCondition + order by + @fldSort + + @strSortType+ ) AS TempTB + order by + @fldSort + + @strFSortType

　　             else if(@Sort= )

　　                     set @strTmp=@SqlSelect+ * from ( +@SqlSelect+ top + CAST(@pageSize as VARCHAR( ))+ + @fldName+ from +@tblName

　　                         + where +@ID+ >(select max( + @ID + ) from( + @SqlSelect+ top + CAST(@pageSize*(@page )+@lastcount as Varchar( )) + + @ID + from +@tblName

　　                         + where ( = ) + @strCondition + order by + @fldSort + + @strSortType+ ) AS TBMaxID)

　　                         + + @strCondition+ order by + @fldSort + + @strSortType+ ) AS TempTB + order by + @fldSort + + @strFSortType

　　             else

　　                     set @strTmp=@SqlSelect+ * from ( +@SqlSelect+ top + CAST(@pageSize as VARCHAR( ))+ + @fldName+ from +@tblName

　　                         + where +@ID+ <(select min( + @ID + ) from( + @SqlSelect+ top + CAST(@pageSize*(@page )+@lastcount as Varchar( )) + + @ID + from +@tblName

　　                         + where ( = ) + @strCondition + order by + @fldSort + + @strSortType+ ) AS TBMaxID)

　　                         + + @strCondition+ order by + @fldSort + + @strSortType+ ) AS TempTB + order by + @fldSort + + @strFSortType

　　         end

　　     end

　　 返回查询结果

　　 exec sp_executesql @strTmp

　　 select datediff(ms @timediff getdate()) as 耗时

　　 print @strTmp

　　 SET NOCOUNT OFF