MsSql2005Dialect does not use parameters for paging parameters

Description

I have spent 3 days to looking for while nhibernate does paging on MSSQL2005/MSSQL2008 too slow, I found that the problem is in MySql2005Dialect, it does not use parameters for paging parameters which will force database engine to re-compile the same SQL many times.

1. SqlStringBuilder result = new SqlStringBuilder()
2. .Add("SELECT TOP ")
3. .Add(last.ToString())
4. .Add(" ")
5. .Add(StringHelper.Join(", ", columnsOrAliases))
6. .Add(" FROM (")
7. .Add(select)
8. .Add(", ROW_NUMBER() OVER(ORDER BY ");

9. AppendSortExpressions(aliasToColumn, sortExpressions, result);

10. result.Add(") as __hibernate_sort_row ")
11. .Add(from)
12. .Add(") as query WHERE query.__hibernate_sort_row > ")
13. .Add(offset.ToString())
14. .Add(" ORDER BY query.__hibernate_sort_row");

The line 3rd should be '.Add(Parameter.Placeholder)', and the line 13th should be '.Add(Parameter.Placeholder)'. Of course, we need to do more processing to let nhibernate to support 'bind offset first, and limit last'

Environment

None

Attachments

Linked issues

is related to

NH-1799

Change SQL Server dialect to support variable limits

NH-1653

SubqueryExpression don't support Dialect with VariableLimit

Activity

Show:

Fabio Maulo July 25, 2010 at 5:01 AM

Julian, can you check and NH-1799.
Thanks.

Julian Maughan July 15, 2010 at 4:22 PM

Committed to trunk (rev. 5007)

Hung Tran Dinh June 9, 2010 at 9:11 AM

The patch works perfectly, performance is much better right now.

Thank you for your working!

Julian Maughan June 6, 2010 at 10:18 AM

So, you mean it could be quicker

I made your proposed change to the SQL Server 2005 dialect. Also, two related unit-tests had to be updated, but nothing serious.

It would be great if you could test my patch to see if SQL Server will now cache the query execution plans, and give you an improvement in performance. Please note that when I was doing some background reading on this issue there were a few complaints about the performance of using ROWNUMBER OVER, so your proposed change may not actually fix the problem altogether, if at all.

(I did find a small issue with the SqlStringFormatter. It assigns incorrect parameter names if a parameter list contains a mixture of parameters: some with a ParameterPosition, and some without (e.g created with Parameter.Placeholder). The issue can be reproduced by replacing the 'limitParameter' and 'offsetParameter' variables with 'Parameter.Placeholder' references in MsSql2005Dialect.GetLimitString, and running the Pagination unit-tests. Just documenting the issue here for general reference.)

Hung Tran Dinh June 4, 2010 at 7:13 AM

"Too slow" means slower than using parameters.

Because of not use parameters, database engine does not cache execution plan of paging SQL generated by NHibernate. It always spend time to re-analyze/re-parse/re-compile for them (sometimes it consumes more than 300ms per SQL)