人生苦短，必须性感。看看桃花，再聊聊技术

嘿嘿，我又开始写博客了。

其实也就是随便写写咯。2013刚刚开始，有运气好的时候，也有运气坏的时候。之所以说运气好是因为过年和朋友打麻将，基本上就看到我一个人胡咯，各种好牌，自摸的都不好意思了。其实我技术真的不行的，但似乎像我这种愣头青一般都是无心插柳成荫喔。然后运气坏的时候也是霉的不行。上上周和群里的朋友骑车翻越龙泉到樱桃沟看樱花的时候，不慎冲出了下坡变道，请假在家疗养了一周嘛。记得当时整个人就瘫在那儿了，大口大口的喘气，感觉到脸上在不断地往外面淌血，车车也不晓得摔得哪儿去了。还好在后面的车友跟了上来，几个妹子细心地蘸着开水擦我脸上的血，甚至还有人看我呼吸不过来的样子说要给我做人工呼吸。也许是这句吓到我了，我两扭两扭竟然站起来了！甩甩手啊脚的，发现没有什么问题，兜兜里面的手机也还是好的，身上沾了好多草灰，脸上虽然有几个口子但也不是很疼。然后，有骑友说叫车直接拉我回家，我一看我的车还是好的，就坚持着继续上路咯。后来在龙泉大伙还一起吃了个饭，回家都晚上11点了。一照镜子，其实也没有想像中的那么严重嘛。

现在想起来，其实摔车的那次运气也不算太坏的。摔出去的那个坡也不是很陡。同行的都是朝气蓬勃，热心互助的车友。最严重的伤口也在太阳穴和眼睛之间。也没有断手断脚之类的。车车也没有摔坏。看来是我摔的有技巧哟。话说龙泉的桃花开了，这周再骑过去转转。

再说说工作上的事吧。听说这周星期天要加班，但我感觉没什么事情啊。然后中午问同事加班做什么，同事说加班拿加班工资噻，我晕哦。今天就是改了网管系统的一些BUG。而且发现有一些数据量比较大的页面点分页的时候响应那个慢啊！奇怪的是客户都没有反映么。于是我就到代码库里面去找啊找。找到了下面的分页方法：

        /// <summary>
        /// 运行分页查询
        /// </summary>
        /// <param name="tableName">查询数据表或视图名</param>
        /// <param name="indexName">索引字段名</param>
        /// <param name="indexDbType">索引字段类型</param>
        /// <param name="columns">返回字段列表</param>
        /// <param name="selection">查询条件</param>
        /// <param name="orderBy">排序方式</param>
        /// <param name="pageIndex">页面开始索引</param>
        /// <param name="pageSize">分页大小</param>
        /// <param name="buildObj">IDataReader 处理委托</param>
        /// <returns></returns>
        public RecordSet<T> ExecutePaging<T>(string tableName, string indexName, SqlDbType indexDbType, string[] columns, string selection, string orderBy,
            int pageIndex, int pageSize, DataReaderParserHandler<T> buildObj)
        {
            if (pageIndex < 0)
            {
                pageIndex = 0;
            }

            if (pageSize == 0)
            {
                return new RecordSet<T>();
            }

            StringBuilder commandText = new StringBuilder();
            commandText.AppendFormat(@"
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int

SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1

CREATE TABLE #PageIndexForUsers 
(
    IndexID int IDENTITY (1, 1) NOT NULL,
    {0} {1}
)", indexName, indexDbType == SqlDbType.Int ? "int" : "bigint");

            commandText.AppendFormat(@"

INSERT INTO 
    #PageIndexForUsers ({1})
SELECT
    [{1}]
FROM
    {0}", tableName, indexName);

            if (!string.IsNullOrEmpty(selection))
            {
                commandText.AppendFormat(@"
WHERE
    {0}", selection);
            }

            if (!string.IsNullOrEmpty(orderBy))
            {
                commandText.AppendFormat(@"
ORDER BY
    {0}", orderBy);
            }

            commandText.AppendFormat(@"

SELECT
    {0}
FROM
    [{1}]
WHERE 
    [{2}] IN
        (
            SELECT
                [{2}]
            FROM
                #PageIndexForUsers 
            WHERE
                [IndexID] > @PageLowerBound AND [IndexID] < @PageUpperBound
        )
", columns.Length == 0 ? "*" : string.Join(",", columns), tableName, indexName);
            commandText.AppendFormat(@"

SELECT count(*) FROM #PageIndexForUsers

DROP TABLE #PageIndexForUsers ");

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = commandText.ToString();
            cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
            cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;

            RecordSet<T> sets = new RecordSet<T>();
            sets.PageIndex = pageIndex;
            sets.PageSize = pageSize;

            using (IDataReader dr = this.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    sets.Add(buildObj(dr));
                }

                if (dr.NextResult()
                    && dr.Read())
                {
                    sets.TotalRecords = (int)dr[0];
                }
                dr.Close();
            }

            return sets;
        }

上面的代码就是利用临时表进行分页咯。事实证明，这个在大数量分页的时候似乎效率并不是很高啊。
话说SqlServer不是在很久之前就引进了“表变量”嘛，分页的话效率肯定比临时表高不少。而且，也不知道为什么当初没有写一个通用的分页存储过程。呐，大牛的心思你真的不懂。

其实很多朋友，包含之前我在做一些东西需要用到分页的时候，都是利用下面的SQL哦：

SELECT TOP PageSize *
FROM TableName
WHERE ID NOT IN
        (SELECT TOP PageSize*(PageIndex-1) id  FROM TableName ORDER BY id)
ORDER BY ID

原理一看便知。Top语句的效率很高，通常情况下ID也是一个表的聚簇索引。但 not in 的效率就不敢恭维咯，即使换作 not exists 语句，也不会有太大的改观吧。

记得很久以前看到一篇.NET的面试题集，有一道写Sql的，题干大概是这样的：

“在SQLServer数据库，取出一个表A中第21到第30条记录，其中，ID为自动增长的主键, 但可能不是连续的”。

有朋友就给出了两种解法：

解1：select top 10 * from A where id not in (select top 20 id from A) 
解2: select top 10 * from A where id > (select max(id) from (select top 20 id from A )as T)

 

解1不必多说，解2利用max函数提取出id的最大值，以此为参照物进行分页，同时去掉了 not in 语句。思路当然比第一种高明咯。

解2的分页原型如下：

select top PageSize * from TableName
where id>
      (select max (id) from
        (select top PageSize*(PageIndex-1) id from TableName order by id) as T
       )    
  order by id

改造成存储过程基本就是下面这个样子：

CREATE PROCEDURE P_DataPagination
                @tblName      VARCHAR(255),        -- 查询的表名
                @returnFields VARCHAR(1000) = '*', -- 需要返回的数据列
                @sortField      VARCHAR(255) = '', -- 排序的字段名
                @sortType    BIT = 0,              -- 设置排序类型, 非 0 值则降序
                @pageSize     INT = 10,            -- 页尺寸
                @pageIndex    INT = 1,             -- 页码
                @totalCount      BIT = 0,          -- 返回记录总数, 非 0 值则返回
                @strWhere     VARCHAR(1500) = ''   -- 查询条件 (注意: 不要加 where)                                           
AS
  DECLARE  @strSQL VARCHAR(5000)  -- 主语句
  DECLARE  @strTmp VARCHAR(110)   -- 临时变量
  DECLARE  @strOrder VARCHAR(400) -- 排序语句
  IF @totalCount != 0             --如果@totalCount传递过来的不是0，就执行总数统计。
    BEGIN
      SET @strSQL = 'select count(*) as TotalCount from [' + @tblName + ']'
      IF @strWhere != ''
      begin
        SET @strSQL = @strSQL + ' where ' + @strWhere
      end
    END
    
  ELSE
    BEGIN
      IF @sortType != 0
        BEGIN
          SET @strTmp = '<(select min'
          SET @strOrder = ' order by [' + @sortField + '] desc'
        END
       ELSE
        BEGIN
          SET @strTmp = '>(select max'
          SET @strOrder = ' order by [' + @sortField + '] asc'
        END
      IF @pageIndex = 1
        BEGIN
          Set @strSql = 'select top ' + Str(@pageSize) + ' ' + @returnFields + '  from [' + @tblName + '] '
          IF @strWhere != ''
          begin
            SET @strSQL = @strSql + ' where ' + @strWhere 
          end
          SET @strSql = @strSql + ' ' + @strOrder        
        END
       ELSE
        BEGIN
          SET @strSQL = 'select top ' + Str(@pageSize) + ' ' + @returnFields + '  from [' + @tblName + '] where [' + @sortField + ']' + @strTmp + '([' + @sortField + ']) from (select top ' + Str((@pageIndex - 1) * @pageSize) + ' [' + @sortField + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)' + @strOrder
          IF @strWhere != ''
          begin
            SET @strSQL = 'select top ' + Str(@pageSize) + ' ' + @returnFields + '  from [' + @tblName + '] where [' + @sortField + ']' + @strTmp + '([' + @sortField + ']) from (select top ' + Str((@pageIndex - 1) * @pageSize) + ' [' + @sortField + '] from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
          end
        END
    END
  EXEC(@strSQL)
GO

经过我的测试，上面这个分页存储过程性能确实比较稳定哦，而且在分页数据量明显增大的情况下，也有非常高效的表现。

下面是我的测试代码：

declare @startTime datetime ;
set @startTime=getdate();

exec P_DataPagination 
    @totalCount = 0,
    @tblName = 'targetsPort',
    @returnFields = 'id,portDesc,createTime,IfIndex,ifSpeed,IfName',
    @sortField = 'id',
    @pageSize = 2000,
    @pageIndex = 2,
    @sortType=0,
    
    @strWhere = ' portDesc like ''1%'' '
    
select [耗费时间(ms)]=datediff(ms,@startTime,getdate()) 

基本上也该收尾咯。虽然说咱家的防御值比较高，但路过的各位大神吐槽不要太猛烈哈。└(^o^)┘