NHibernate 分页优化，针对SQLServer（未深入测试）
是想优化大数据量时的查询，查询后几页的数据能快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 customer customer0_) as query
5 WHERE query.__hibernate_sort_row > 11168306
6ORDERBY query.__hibernate_sort_row
修改后的生成的SQL语句（）：
1 --修改后执行时间为6.218秒 2 declare @totalCount int 3 declare @limit int 4 select @totalCount=count(*) from (select distinct * from customer)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 customer) 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 }
