Troubleshoot hibernate for SQL Server paging slow

 One, hibernate paging hibernate to MSSQL pseudo-paging
Paging is a Web project in a less than a feature, the amount of data can not be fully displayed when it is necessary to use paging technology. I believe you are not familiar with hibernate in the page:
Java code

1. Public Query setmaxresults (int maxResults);
2. Public Query setfirstresult (int firstresult);

As long as the two methods are called and the parameters are set, Hibernate auto-paging completely masks the underlying database paging technology, which is one of the reasons why many developers like hibernate.
A strange problem was encountered in the development of the project. The database uses SQL Server 2005 and the two parameters above, but each SQL statement sent to the database side is select Top .... Statement. Even if you query section 10w, there is only one select top statement, which can cause doubts about hibernate implementing SQL Server paging. Hibernate's paging method for different databases is encapsulated in the corresponding database dialect, and transformed into the corresponding database paging algorithm by getlimitstring method.
Take the dialect mysqldialect of the common MySQL database as an example:
Java code

2. public string getlimitstring (String sql, Boolean hasoffset) {
4. return New StringBuffer (sql.length () + )
6. . append (SQL)
8. . Append (Hasoffset?  "Limit?,?": "limit?")
10. . toString ();
12. }

We use the familiar MySQL limit for paging.
Dialect oracle9idialect for Oracle databases:
Java code

2. StringBuffer pagingselect = new StringBuffer (sql.length () +100);
4. if (hasoffset) {
6. Pagingselect.append ("SELECT * from" (select Row_.*, RowNum rownum_ from (");
8. }
10. else {
12. Pagingselect.append ("select * FROM (");
14. }
16. Pagingselect.append (SQL);
18. if (hasoffset) {
20. Pagingselect.append (") Row_ where RowNum <=?) Where Rownum_ >? ");
22. }
24. else {
26. Pagingselect.append (") where RowNum <=?");
28. }

Complete pagination with Oracle's rownum combined with three-tier nested queries. This three-tier is Oracle's most classic and efficient paging algorithm.
However, the dialect sqlserverdialect for SQL Server:
Java code

2. public string getlimitstring (string queryselect, int offset, int limit) {
4. if (offset > 0) {
6. throw New Unsupportedoperationexception ( "Query result offset is not supported");
8. }
10. return New StringBuffer (queryselect.length () + 8)
12. . Append (Queryselect)
14. . Insert (Getafterselectinsertpoint (queryselect), "top" + limit)
16. . toString ();
18. }

Rub the eyes, then rub, yes, only a top statement appears. This means that if you query the data on page 10w, you need to extract all the data from the first 10w pages. Hibernate paging for SQL Server is pseudo-paging, so as the amount of data increases and users complain that the system is slow and the programmer complains that hibernate performance is low, the DBA complains that the developer SQL is too shallow.
Do not know the Hibernate development group, for what is not currently or the situation does not really provide SQL Server paging technology, then we implement it ourselves.
Dialect Category:
Java code

2. Public class Sqlserver2005dialect extends Sqlserverdialect {
6. /** 
8. *
10. * Do I need to bind the limit parameter?
12. *
14. * When using top in SQL Server, you cannot use parameters to represent top bars, while using row_number () you need to supply the limit parameter
16. */
20. private Threadlocal<boolean> supportsvariablelimit = new threadlocal<boolean> ();
22. Public Sqlserver2005dialect () {
24. Registerfunction ("Bitand", new Bitandfunction ());
26. Registerfunction ("Bitxor", new Bitxorfunction ());
28. Registerfunction ("Bitor", new Bitorfunction ());
30. Setsupportsvariablelimit (false);
32. }
36. /** 
38. *
40. * <p>
42. * Sets whether the limit parameter is bound first.
44. * </p>
46. *
48. * @param First
50. */
54. private void Setsupportsvariablelimit (boolean first) {
56. This.supportsVariableLimit.set (boolean.valueof (first));
58. }
62. /** 
64. *
66. * <p>
68. * Gets the location of the SELECT clause in SQL.
70. * </p>
72. *
74. * @param sql
76. *
78. * @return int
80. */
82. protected static int getsqlafterselectinsertpoint (String sql) {
84. int selectindex = Sql.tolowercase (). IndexOf ("select");
88. int selectdistinctindex = Sql.tolowercase (). IndexOf ("SELECT distinct");
92. return Selectindex + ((Selectdistinctindex = = Selectindex)?  : 6);
94. }
98. Public Boolean supportslimitoffset () {
100. return true;
102. }
106. /* 
108. * Hibernate after obtaining the limit String (the limit clause has been added), if this method returns True,
110. *
112. * Additional parameter values (Row_number () range) are added (the policy may be: There is offset set two parameter value, no parameter value set)
114. */
116. Public Boolean supportsvariablelimit () {
118. return ((Boolean) this.supportsVariableLimit.get ()). Booleanvalue ();
120. }
124. Public Boolean usemaxforlimit () {
126. return true;
128. }
130. /** 
132. * Home Top, later with Row_number
134. */
136. Public string getlimitstring (string query, int offset, int limit) {
138. Setsupportsvariablelimit (Offset > 0);
142. if (offset = = 0) {
144. return New StringBuffer (query.length () + 8). Append (query). Insert (
146. Getsqlafterselectinsertpoint (query), "top" + limit)
148. . toString ();
150. }
154. return getlimitstring (query, offset > 0);
156. }
160. Public string getlimitstring (String sql, Boolean hasoffset) {
162. int orderbyindex = Sql.tolowercase (). LastIndexOf ("Order by");
166. if (Orderbyindex <= 0) {
168. throw New Unsupportedoperationexception (
170. "must specify ' ORDER BY ' statement-to-support-limit operation with offset in SQL Server 2005");
172. }
176. String Sqlorderby = sql.substring (Orderbyindex + 8);
180. String Sqlremoveorderby = sql.substring (0, Orderbyindex);
184. int insertpoint = getsqlafterselectinsertpoint (sql);
186. return New StringBuffer (Sql.length () + + )
188. . Append ("with Temppagination as (")
190. . Append (Sqlremoveorderby)
192. . Insert (
194. Insertpoint + ,
196. "Row_number () over (ORDER by" + Sqlorderby
198. + ") as RowNumber,")
200. . Append (
202. ") SELECT * from temppagination where rownumber>? and rownumber<=? ")
204. . toString ();
206. }
208. }

Finally configure dialect to use your own Sqlserver2005dialect class when configuring Hibernate in Hibernate.cfg.xml

<property name= "dialect" > com.common.sqlserver2005dialect</property> or in an integrated configuration file with spring

<prop key= "Hibernate.dialect" >com.common.SQLServer2005Dialect</prop>

When paging, you can achieve very good efficiency.

The first page uses the top page, and later the first page of the Row_number page the above query requires that SQL must contain a sort clause.
This is only the problem that SQL Server encountered during the project process, and you will not experience this problem if you use Mysql,oracle.

Troubleshoot hibernate for SQL Server paging slow