SQLSERVER 存储过程实现分页查询 C#后台获取查询结果集

来源：互联网发布：动态sql的执行原理编辑：程序博客网时间：2024/06/03 20:05

一、为什么要用分页查询

在列表查询时由于数据量非常多，一次性查出来非常慢，也不能一次显示给客户端，特别是在使用ExtJS的GridPanel时候，显示数据量达到200条时对性能影响难以容忍，所以需要考虑将数据分批次查询出来，每页显示一定量的数据，这就是数据要分页，即需要分页查询(paging query)。

二、怎样用分页查询

分页查询思路有两个，分别是在前台和后台实现分页查询。前台了肯定不如后台高效理想。所以在此，只对后台实现做总结和归纳。在后台实现分页查询也有不同的实现方法（方法肯定是多种多样的），一个是在后台获取整个数据表，然后进行条件筛选和查询，再就是直接在数据库中实现分页查询，使用存储过程将查询的结果集返回（也可以不使用存储过程，直接用SQL语句，存储过程也是平时编写的sql查询语句，只不过是经过预先编译存放在后台，一次编译多次使用，提升性能）；当然后者即使用存储过程是最理想的选择。ok，来整理一下思路：1.数据库系统中编写存储过程，2.后台调用存储过程（语言平台)。知道做什么不知道怎么做并不可怕，只要它是肯学习都可以逐个击破。

三、存储过程编写

存储过程编写，以实例讲解

USE [Exam]GO/****** Object:  StoredProcedure [dbo].[spPager]    Script Date: 2014/12/28 23:42:02 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[spPager]@TableVARCHAR(50), --表名@pageIndex  INT,--当前页码@PageSizeINT,--每页记录数@FieldVARCHAR(1000)='*',--筛选列即输出字段@SortVARCHAR(300)=NULL,   --排序字段，不带ORDER BY@FilterVARCHAR(200)=NULL,   -- where过滤条件，不带where@MaxPageSMALLINT OUTPUT, -- 总页数@TotalCountINT OUTPUT,--总记录数@DescriptVARCHAR(100) OUTPUT--查询结果描述ASBEGINDECLARE @PrimaryKey VARCHAR(50) -- 主键名  SET @PrimaryKey = 'id' --主键名称IF @Sort IS NULL OR @Sort = ''SET @Sort = @PrimaryKey--计算总记录数DECLARE @TotalCountSql NVARCHAR(1000)SET @TotalCountSql = N'SELECT @TotalCount=COUNT(*)'+N' FROM '+@Table+' WHERE '+@Filter -- 构造获取满足条件的总记录数sql语句EXEC sys.sp_executesql @TotalCountSql,N'@TotalCount INT OUTPUT',@TotalCount OUTPUT   --执行    SET @MaxPage =CEILING(CAST(@TotalCount AS FLOAT)/CAST(@PageSize AS FLOAT))  --赋值分页最大页数IF @pageIndex < 1SET @pageIndex = 1IF @pageIndex>=@MaxPageSET @pageIndex = @MaxPage--执行查询语句DECLARE @querySql VARCHAR(1000)IF @pageIndex<1SET @pageIndex = 1IF @pageIndex > @MaxPageSET @pageIndex = @MaxPageIF @pageIndex = 1BEGINSET @querySql = 'SELECT TOP '+STR(@PageSize)+'  '+@Field+'  FROM '+@Table+ '  WHERE  '+@Filter+' ORDER BY '+@SortENDELSEBEGINSET @querySql = 'SELECT TOP '+STR(@PageSize)+'  '+@Field+'  FROM '+@Table+ '  WHERE '+@Filter+' AND '+@PrimaryKey+'  NOT IN (SELECT TOP  '+ STR((@pageIndex-1)* @PageSize)+'  '+ @PrimaryKey +' FROM  '+ @Table+' WHERE '+@Filter+'  ORDER BY '+@Sort +') ORDER BY '+@SortEND  EXEC(@querySql)--执行sql语句得到结果集，在后台下载获取SET @Descript= 'succeed'END

四、后台获取结果集

再次仅以C#代码为例

using (DataTable tb = new DataTable())            {                using (SqlConnection conn = new SqlConnection(DA.STR_CONNECTION(database)))                {                    using (SqlCommand cmd = new SqlCommand("spPager", conn))                    {                        cmd.CommandType = CommandType.StoredProcedure;                        cmd.Parameters.AddWithValue("@pageIndex", page_index);                        cmd.Parameters.AddWithValue("@PageSize", size);                        cmd.Parameters.AddWithValue("@Table", table);                        cmd.Parameters.AddWithValue("@Field", fields);                        cmd.Parameters.AddWithValue("@Sort", order);                        cmd.Parameters.AddWithValue("@Filter", condition);                        cmd.Parameters.Add("@MaxPage", SqlDbType.SmallInt).Direction = ParameterDirection.Output;                        cmd.Parameters.Add("@TotalCount", SqlDbType.Int).Direction = ParameterDirection.Output;                        cmd.Parameters.Add("@Descript", SqlDbType.VarChar, 100).Direction = ParameterDirection.Output;                        conn.Open();                        tb.Load(cmd.ExecuteReader());--获取结果集                        result = cmd.Parameters["@Descript"].Value.ToString();                        if (result.IndexOf("Error") >= 0)                            throw (new Exception(result));                        count = Int32.Parse(cmd.Parameters["@TotalCount"].Value.ToString());                        pages = Int32.Parse(cmd.Parameters["@MaxPage"].Value.ToString());                        return tb;                    }                }            }

五、常用分页查询方法

    我们经常会碰到要取n到m条记录，就是有分页思想，下面罗列一下一般的方法。我本地的一张表 tbl_FlightsDetail，有300多W记录，主键 FlightsDetailID(Guid)，要求按照FlightsDetailID排序 取 3000001 到3000010 之间的10条记录，也是百万级。

方法1 定位法 (利用ID大于多少)

select top 10 * from tbl_FlightsDetail where FlightsDetailID>(       select max(FlightsDetailID) from (               select top 3000000 FlightsDetailID from tbl_FlightsDetail order by FlightsDetailID       ) as t) order by FlightsDetailID

执行计划：

先查出 top 300000，再聚合取这个集合中最大的Id1，再过滤 id大于id1的集合（上图中使用到索引），再取top 10 条。

方法2 (利用Not In)

语句形式：

select top 10* from tbl_FlightsDetail where FlightsDetailID not in (       select top 3000000 FlightsDetailID from tbl_FlightsDetail order by FlightsDetailID) order by FlightsDetailID

执行计划：

和方法一类似，只是过滤where条件不一样，这里用到的是not in，上图中没有用到索引，耗时8秒。如果 FlightsDetailID不是索引的话，方法1和该方法将差不多。

方法3 (利用颠颠倒倒top)

语句形式：

select top 10* from (       select top 3000010* from tbl_FlightsDetail order by FlightsDetailID) as t  order by t.FlightsDetailID desc

执行计划：

先取前面3000010条记录，再倒序，这时再取前面10条即是300001 到300010条记录，没有用到索引，耗时11秒

方法4 (ROW_NUMBER()函数)

语句形式：

select * from (       select *,ROW_NUMBER() OVER (ORDER BY FlightsDetailID) as rank from tbl_FlightsDetail)  as t where t.rank between 3000001 and 3000010

执行计划：

Sql 2005版本或以上支持，也没用到索引，耗时2秒，速度还不错。

方法5 (利用IN)

此方法是由 金色海洋（jyk）阳光男孩 回复的，飞常感谢，语句形式：

select top 10 * from tbl_FlightsDetail  where FlightsDetailID in(        select top 10 FlightsDetailID from(                select top 3000010 FlightsDetailID from tbl_FlightsDetail order by FlightsDetailID       ) as t order by t.FlightsDetailID desc ) order by FlightsDetailID

执行计划：

多次执行之后一般维持在4秒左右，用到索引，非常不错，计划图还很长，只截取部分，可能是绕的多一点。

3.千万级分页存储过程

大家百度一下这个标题立马会出现很多相关信息，都大同小异，我自己拷贝的一个，应项目的需要，修改了一个排序的bug以及添加了返回总记录数，如下：

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO--分页存储过程  CREATE PROCEDURE [dbo].[sp_Paging] ( @Tables nvarchar(1000),                --表名/视图名@PrimaryKey nvarchar(100),             --主键@Sort nvarchar(200) = NULL,            --排序字段(不带order by)@pageindex int = 1,                    --当前页码@PageSize int = 10,                    --每页记录数@Fields nvarchar(1000) = N'*',         --输出字段@Filter nvarchar(1000) = NULL,         --where过滤条件(不带where)@Group nvarchar(1000) = NULL,          --Group语句(不带Group By)@TotalCount int OUTPUT                 --总记录数) AS   DECLARE @SortTable nvarchar(100) DECLARE @SortName nvarchar(100) DECLARE @strSortColumn nvarchar(200) DECLARE @operator char(2) DECLARE @type nvarchar(100) DECLARE @prec int --设定排序语句IF @Sort IS NULL OR @Sort = ''        SET @Sort = @PrimaryKey      IF CHARINDEX('DESC',@Sort)>0   BEGIN             SET @strSortColumn = REPLACE(@Sort, 'DESC', '')             SET @operator = '<='     END ELSE     BEGIN                    SET @strSortColumn = REPLACE(@Sort, 'ASC', '')                    SET @operator = '>='     END IF CHARINDEX('.', @strSortColumn) > 0     BEGIN             SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))    SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))     END ELSE     BEGIN             SET @SortTable = @Tables             SET @SortName = @strSortColumn  END --设置排序字段类型和精度 SELECT @type=t.name, @prec=c.prec FROM sysobjects o        JOIN syscolumns c on o.id=c.id        JOIN systypes t on c.xusertype=t.xusertype WHERE o.name = @SortTable AND c.name = @SortName        IF CHARINDEX('char', @type) > 0       SET @type = @type + '(' + CAST(@prec AS varchar) + ')'   DECLARE @strPageSize nvarchar(50) DECLARE @strStartRow nvarchar(50) DECLARE @strFilter nvarchar(1000) DECLARE @strSimpleFilter nvarchar(1000) DECLARE @strGroup nvarchar(1000)   IF @pageindex <1        SET @pageindex = 1  SET @strPageSize = CAST(@PageSize AS nvarchar(50)) --设置开始分页记录数 SET @strStartRow = CAST(((@pageindex - 1)*@PageSize + 1) AS nvarchar(50))  --筛选以及分组语句IF @Filter IS NOT NULL AND @Filter != ''     BEGIN             SET @strFilter = ' WHERE ' + @Filter + ' '     SET @strSimpleFilter = ' AND ' + @Filter + ' ' END ELSE     BEGIN             SET @strSimpleFilter = ''             SET @strFilter = ''     END IF @Group IS NOT NULL AND @Group != ''     SET @strGroup = ' GROUP BY ' --计算总记录数DECLARE @TotalCountSql nvarchar(1000)SET @TotalCountSql=N'SELECT @TotalCount=COUNT(*)' +N' FROM ' + @Tables + @strFilterEXEC sp_executesql @TotalCountSql,N'@TotalCount int OUTPUT',@TotalCount OUTPUT--执行查询语句    EXEC('DECLARE @SortColumn ' + @type + 'SET ROWCOUNT ' + @strStartRow + 'SELECT @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + 'SET ROWCOUNT ' + @strPageSize + 'SELECT ' + @Fields + ' FROM ' + @Tables + ' WHERE ' + @strSortColumn + @operator + ' @SortColumn ' + @strSimpleFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '')

现在我们来测试一下：

DECLARE    @return_value int,        @TotalCount intEXEC    @return_value = [dbo].[sp_Paging]        @Tables = N'tbl_FlightsDetail',        @PrimaryKey = N'FlightsDetailID',        @Sort = N'FlightsDetailID',        @pageindex = 299999,        @PageSize = 10,        @Fields = '*',        @Filter = NULL,        @Group = NULL,        @TotalCount = @TotalCount OUTPUTSELECT    @TotalCount as N'@TotalCount'SELECT    'Return Value' = @return_value

执行计划：

看时间的确是快，执行计划显示4个查询

查询1，是利用系统表获取排序字段、类型和精度，这个很快，全是索引。

查询2，返回总记录数，第一次会慢点，后面就很快了。

查询3 和查询4（用到索引）才是我们要分页取的数据，查询3 是排序，取一个最大的值赋给变量，查询4是大于这个变量的值取数据，直接看sql语句，把上面的exec动态语句改成如下：

DECLARE @SortColumn varchar(40)--即 top 3000001，取出最大的 id覆盖@SortColumn SET ROWCOUNT  3000001SELECT @SortColumn= FlightsDetailID  FROM tbl_FlightsDetail ORDER BY FlightsDetailID  --即 top 10SET ROWCOUNT 10SELECT *  FROM  tbl_FlightsDetail  WHERE FlightsDetailID   >= @SortColumn  ORDER BY  FlightsDetailID

你会发现，原来它跟我们标题2 常用的数据分页方法中的方法1 定位类似，原来奥秘在这。

0 0