NHibernate分页的优化，针对SQLServer（未深入测试）_.NET_编程开发_程序员俱乐部

是想优化大数据量时的查询，查询后几页的数据能快1/4左右，但前几页的数据查询会变慢，慢多少记得不太清了。

应该还有更好的办法优化，暂时只想到这种优化方式。

主要是优化查询语句：

修改前的生成的SQl语句（NHibernate用的是ROW_NUMBER()分页，该分页数据量大时，越到后边越慢）：

1 --8.094秒
2 SELECT TOP (10) Id9_, Version9_, Name9_ 
3 FROM (select customer0_.Id as Id9_, customer0_.Version as Version9_, customer0_.Name as Name9_, 
4 ROW_NUMBER() OVER(ORDER BY name,version) as __hibernate_sort_row from ZBZB_CUS customer0_) as query 
5 WHERE query.__hibernate_sort_row > 11168306 
6 ORDER BY query.__hibernate_sort_row

修改后的生成的SQL语句（）：

 1 --修改后执行时间为6.218秒
 2 declare  @totalCount int
 3 declare @limit int
 4 select @totalCount=count(*) from (select distinct * from zbzb_cus)a
 5 set @limit = case when (10+@totalCount-11168316) <= 0 then 0 when (10+@totalCount-11168316) > 10 then 10 else 10+@totalCount-11168316 end
 6 
 7 select * from (select top 
 8 (@limit) * 
 9     from (select top 11168326 * 
10         from (select distinct * from zbzb_cus) a order by  CURRENT_TIMESTAMP) a order by name desc,version desc) b order by name,version option(RECOMPILE)

修改后的NHibernate代码（修改PageByLimitAndOffset生成修改后的SQL语句）：

  1 private SqlString PageByLimitAndOffset(SqlString offset, SqlString limit)
  2         {
  3             int fromIndex = GetFromIndex();
  4             SqlString select = _sourceQuery.Substring(0, fromIndex);
  5 
  6             List<SqlString> columnsOrAliases;
  7             Dictionary<SqlString, SqlString> aliasToColumn;
  8             Dictionary<SqlString, SqlString> columnToAlias;
  9 
 10             Dialect.ExtractColumnOrAliasNames(select, out columnsOrAliases, out aliasToColumn, out columnToAlias);
 11 
 12             int orderIndex = _sourceQuery.LastIndexOfCaseInsensitive(" order by ");
 13             SqlString fromAndWhere;
 14             SqlString[] sortExpressions;
 15 
 16             //don't use the order index if it is contained within a larger statement(assuming
 17             //a statement with non matching parenthesis is part of a larger block)
 18             if (orderIndex > 0 && HasMatchingParens(_sourceQuery.Substring(orderIndex).ToString()))
 19             {
 20                 fromAndWhere = _sourceQuery.Substring(fromIndex, orderIndex - fromIndex).Trim();
 21                 SqlString orderBy = _sourceQuery.Substring(orderIndex).Trim().Substring(9);
 22                 sortExpressions = orderBy.SplitWithRegex(@"(?<!\([^\)]*),{1}");
 23             }
 24             else
 25             {
 26                 fromAndWhere = _sourceQuery.Substring(fromIndex).Trim();
 27                 // Use dummy sort to avoid errors
 28                 sortExpressions = new[] { new SqlString("CURRENT_TIMESTAMP") };
 29             }
 30 
 31             var result = new SqlStringBuilder();
 32 
 33             if (limit == null)
 34             {
 35                 // ORDER BY can only be used in subqueries if TOP is also specified.
 36                 limit = new SqlString(int.MaxValue);
 37             }
 38 
 39             if (IsDistinct())
 40             {
 41                 result.Add(@"declare @totalCount int
 42                 declare @limit int
 43                 declare @pageLimit int
 44                 declare @offset int " + "\r\n");
 45                 result.Add("set @pageLimit = ").Add(limit).Add("\r\n");
 46                 result.Add("set @offset = ").Add(offset).Add("\r\n");
 47                 result.Add("select @totalCount=count(*) from (");
 48                 result.Add(select);
 49                 result.Add(" ");
 50                 result.Add(fromAndWhere);
 51                 result.Add(") _tempCount \r\n");
 52                 result.Add(@"set @limit = case
 53                             when @totalCount-@pageLimit-@offset<= 0 then 0
 54                             when @totalCount-@pageLimit-@offset> @pageLimit then @pageLimit
 55                             else @totalCount-@pageLimit-@offset end " + "\r\n");
 56                 result.Add(@"select ");
 57                 result
 58                     .Add(StringHelper.Join(", ", columnsOrAliases))
 59                     .Add(@" from (select top 
 60                     (@limit) * 
 61                     from (select top ")
 62                     .Add("(@pageLimit+@offset) * ")
 63                     //.Add(StringHelper.Join(", ", select.Replace("select", "").Replace("SELECT", "")
 64                     //.Replace("distinct", "").Replace("DISTINCT", "")))
 65                     .Add(" from(")
 66                     .Add(select)
 67                     .Add(" ")
 68                     .Add(fromAndWhere)
 69                     .Add(") _tempDistinct ");
 70                 result.Add("order by ");
 71                 AppendSortExpressionsForDistinct(columnToAlias, sortExpressions, result);
 72                 result.Add(") _tempOrder ");
 73                 result.Add(" order by ");
 74                 AppendSortExpressionsForDistinctReverse(columnToAlias, sortExpressions, result);
 75                 result.Add(") _tempOrderReverse ");
 76                 result.Add(" order by ");
 77                 AppendSortExpressionsForDistinct(columnToAlias, sortExpressions, result);
 78                 result.Add(" option(RECOMPILE)");
 79             }
 80             else
 81             {
 82                 result.Add(@"declare @totalCount int
 83                 declare @limit int
 84                 declare @pageLimit int
 85                 declare @offset int "+"\r\n");
 86                 result.Add("set @pageLimit = ").Add(limit).Add("\r\n");
 87                 result.Add("set @offset = ").Add(offset).Add("\r\n");
 88                 result.Add("select @totalCount=count(*) from (");
 89                 result.Add(select);
 90                 result.Add(" ");
 91                 result.Add(fromAndWhere);
 92                 result.Add(") _tempCount \r\n");
 93                 result.Add(@"set @limit = case
 94                             when @totalCount-@pageLimit-@offset<= 0 then 0
 95                             when @totalCount-@pageLimit-@offset> @pageLimit then @pageLimit
 96                             else @totalCount-@pageLimit-@offset end "+"\r\n");
 97                 result.Add(@"select ");
 98                 result
 99                     .Add(StringHelper.Join(", ", columnsOrAliases))
100                     .Add(@" from (select top 
101                     (@limit) * 
102                     from (select top ")
103                     .Add("(@pageLimit+@offset) ")
104                     .Add(StringHelper.Join(", ", select.Replace("select","").Replace("SELECT","")));
105                 result.Add(" ");
106                 result.Add(fromAndWhere);
107                 result.Add(" order by ");
108                 AppendSortExpressionsForDistinct(columnToAlias, sortExpressions, result);
109                 result.Add(") a ");
110                 result.Add(" order by ");
111                 AppendSortExpressionsForDistinctReverse(columnToAlias, sortExpressions, result);
112                 result.Add(") b ");
113                 result.Add(" order by ");
114                 AppendSortExpressionsForDistinct(columnToAlias, sortExpressions, result);
115                 result.Add(" option(RECOMPILE)");
116             }
117 
118             return result.ToSqlString();
119         }