时间戳

首页 > 解决方案 > SQL Server 2008 R2 中 500,000 条记录的分页速度较慢

sql-server - SQL Server 2008 R2 中 500,000 条记录的分页速度较慢

问题描述

我在 SQL Server 2008 R2 中使用此存储过程进行分页：

ALTER PROCEDURE [SelectPaging]
@SelectedColumn nvarchar(max),
@ViewName nvarchar(200),
@PrimaryKeyName nvarchar(100),
@RowCountOnPage int,
@PageNumberDesired int,
@Condition nvarchar(max),
@OrderBy nvarchar(200),
@OrderByAsc bit,
@UserID int,
@ErrorNumber int out,
@TotalRowsCount int out
as
begin
SET NOCOUNT ON;

Begin Try



    exec [general].TotalRowsWithFiltering @PrimaryKeyName,@viewName ,@Condition ,@UserID,@TotalRowsCount out,@ErrorNumber out


    declare @RowNumberFrom int,@RowNumberTo int
    set @RowNumberFrom=((@PageNumberDesired-1)*@RowCountOnPage)
    set @RowNumberTo=(@PageNumberDesired*@RowCountOnPage)+1

    if @Condition='' or @Condition is null
    set @Condition=' 1=1 '
    else set @Condition=@Condition

    if @OrderBy='' or @OrderBy is null
    set @OrderBy=@PrimaryKeyName
    declare @OrderByAsc_Desc nvarchar(6)
    if (@OrderByAsc='false' or @OrderByAsc is null)
        set @OrderByAsc_Desc=' Desc'
    else if @OrderByAsc='true'
        set @OrderByAsc_Desc=' Asc'


    declare @SQLQuery nvarchar(max)


set @SQLQuery='SELECT
  *
FROM (
  SELECT
    *,
    ROW_NUMBER() OVER (order by '+@OrderBy+' '+@OrderByAsc_Desc+') AS RowNumber
  FROM (
    SELECT '+@SelectedColumn+'
    FROM '+@viewName+' where  ' + @Condition +'
  ) AS d
) AS c
where RowNumber >'+cast(@RowNumberFrom as nvarchar(10))+' and RowNumber <  '+cast(@RowNumberTo as nvarchar(10))


    EXECUTE sp_executesql @SQLQuery

    set @ErrorNumber=@@ERROR

END TRY
BEGIN CATCH

END CATCH


end

我在具有 700.000 条记录的视图上运行查询。当我在视图上选择查询时，运行时间为零。但是当我运行这个 sp 进行分页时，每页获取 10 条记录需要 50-60 秒

如何优化？

标签： sql-serverpagination

解决方案

您可以使用此代码进行分页....OFFSET 支持 sql server 2012+ 版本。所以首先升级你的服务器版本..然后在你的表中创建一个索引以便快速搜索

   declare @Page bigint=1,@pageSize bigint = 20

    SELECT * FROM AC_TRN_STOCKHEADER 
    Order by HEADER_ID desc 
    OFFSET  (@Page-1) ROWS 
    FETCH NEXT @pageSize ROWS ONLY

-----==================================
-----Code to Create an index
CREATE NONCLUSTERED INDEX IDX_HEADER_ID ON YOUR_TABLE_NAME (KEY_FIELD_OF_TABLE)

ON THE ABOVE CODE "AC_TRN_STOCKHEADER" is my Table Name and "Header_ID" is my Key_FIELD
Hence,
CREATE NONCLUSTERED INDEX IDX_HEADER_ID ON AC_TRN_STOCKHEADER (Header_ID)

希望它能解决你的问题..

推荐阅读