The column I was doing an ORDER BY on ('name') has an index, but still
it took indefinitely long to go to page 1200.
I'm sure that the other method can be fast as well with the proper DB
optimizations (I just don't know which ones) can be fast as well.
But this minor change in how the LIMIT query is build made speeding up
the searchpage a lot easier for me. And besides, I don't think
one would see any differences in the resultset when this change would
be applied to the codebase.It may help some and doesn't bother others. On 14 dec, 18:06, "[email protected]" <[email protected]> wrote: > Use SQL Profiler or NH Profiler... get the query, check the > compilation plan. You'll notice that the paging isn't very heavy on > the query itself (though it can fragment your tempdb but everything > does :P). It will be a missing index somewhere that is giving you > grief. > > I hit a 3mil record table with a fair amount of relational joins all > day long with paging but without issue. > > On Dec 14, 4:53 am, Bart <[email protected]> wrote: > > > > > > > > > We had a situation in which a quite large table (12500 persons with a > > lot of relations) had to be listed in a searchable grid. > > This grid shows 10 persons per page, so that meant more then 1200 > > pages. When going to page 300 or more one had to wait quite long > > before the results showed up. > > Browsing to page 1200 resulted in a query timeout. > > > When I looked up the MsSql2005Dialect it seemed that the > > GetLimitString function output didn't match the pagination performance > > tips that are around on the internet. > > So I made a few changes which can be found in this message which made > > grid page 1200 just as fast as page 1. > > Basically it comes down to replacing the "SELECT TOP (last)" with just > > "SELECT" and adding an extra WHERE clause at the bottom: > > > .Add(" and query.__hibernate_sort_row < ") > > .Add((offset + last + 1).ToString()) > > .Add(" ORDER BY query.__hibernate_sort_row"); > > > This is the altered GetLimitString function: > > > public override SqlString GetLimitString(SqlString querySqlString, int > > offset, int last) > > { > > //dont do this paging code if there is no offset, use the > > //sql 2000 dialect since it wont just uses a top statement > > if (offset == 0) > > { > > return base.GetLimitString(querySqlString, offset, > > last); > > } > > > // we have to do this in order to support parameters in > > order clause, the foramt > > // that sql 2005 uses for paging means that we move the > > parameters around, which means, > > // that positions are lost, so we record them before > > making any changes. > > // NH-1528 > > int parameterPositon = 0; > > foreach (var part in querySqlString.Parts) > > { > > Parameter param = part as Parameter; > > if (param == null) > > continue; > > param.OriginalPositionInQuery = parameterPositon; > > parameterPositon += 1; > > } > > > int fromIndex = GetFromIndex(querySqlString); > > SqlString select = querySqlString.Substring(0, fromIndex); > > List<SqlString> columnsOrAliases; > > Dictionary<SqlString, SqlString> aliasToColumn; > > ExtractColumnOrAliasNames(select, out columnsOrAliases, > > out aliasToColumn); > > > int orderIndex = > > querySqlString.LastIndexOfCaseInsensitive(" order by "); > > SqlString from; > > SqlString[] sortExpressions; > > > //don't use the order index if it is contained within a > > larger statement(assuming > > //a statement with non matching parenthesis is part of a > > larger block) > > if (orderIndex > 0 && > > HasMatchingParens(querySqlString.Substring(orderIndex).ToString())) > > { > > from = querySqlString.Substring(fromIndex, orderIndex > > - fromIndex).Trim(); > > SqlString orderBy = > > querySqlString.Substring(orderIndex).Trim(); > > sortExpressions = orderBy.Substring(9).Split(","); > > } > > else > > { > > from = querySqlString.Substring(fromIndex).Trim(); > > // Use dummy sort to avoid errors > > sortExpressions = new[] { new > > SqlString("CURRENT_TIMESTAMP"), }; > > } > > > SqlStringBuilder result = > > new SqlStringBuilder() > > .Add("SELECT ") > > .Add(StringHelper.Join(", ", columnsOrAliases)) > > .Add(" FROM (") > > .Add(select) > > .Add(", ROW_NUMBER() OVER(ORDER BY "); > > > AppendSortExpressions(aliasToColumn, sortExpressions, > > result); > > > result.Add(") as __hibernate_sort_row ") > > .Add(from) > > .Add(") as query WHERE query.__hibernate_sort_row> ") > > > .Add(offset.ToString()) > > .Add(" and query.__hibernate_sort_row < ") > > .Add((offset + last + 1).ToString()) > > .Add(" ORDER BY query.__hibernate_sort_row"); > > > return result.ToSqlString(); > > } -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
