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.
