Paging Optimization of sqlserver2005
This article mainly introduces "the paging optimization of sqlserver2005". In the daily operation, I believe that many people have doubts about the paging optimization of sqlserver2005. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "paging optimization of sqlserver2005". Next, please follow the editor to study!
1. Hibernate paging hibernate pseudo-paging of MsSql
Paging is an indispensable function in web projects. Paging technology must be used when large amounts of data cannot be fully displayed. I believe everyone is familiar with the pagination in hibernate:
Java code
Public Query setMaxResults (int maxResults)
Public Query setFirstResult (int firstResult)
Java code
Public Query setMaxResults (int maxResults)
Public Query setFirstResult (int firstResult)
As long as these two methods are called and the parameters are set, hibernate automatic paging completely blocks the underlying paging technology, which is one of the reasons why many developers like hibernate.
There is a strange problem in project development. The database uses Sql Server 2005, and the above two parameters are set, 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 inevitably gives rise to doubts about hibernate's implementation of sql server paging. The paging method implemented by hibernate for different databases is encapsulated in the dialect of the corresponding database and transformed into the paging algorithm of the corresponding database through the getLimitString method.
Take the dialect MySQLDialect of the common Mysql database as an example:
Java code
Public String getLimitString (String sql, boolean hasOffset) {
Return new StringBuffer (sql.length () + 20)
.append (sql)
.append (hasOffset? "limit?,": "limit?")
.toString ()
}
Java code
Public String getLimitString (String sql, boolean hasOffset) {
Return new StringBuffer (sql.length () + 20)
.append (sql)
.append (hasOffset? "limit?,": "limit?")
.toString ()
}
The familiar limit is used for paging.
Dialect Oracle9iDialect of the database:
Java code
StringBuffer pagingSelect = new StringBuffer (sql.length () + 100)
If (hasOffset) {
PagingSelect.append ("select * from (select row_.*, rownum rownum_ from ("))
}
Else {
PagingSelect.append ("select * from ("))
}
PagingSelect.append (sql)
If (hasOffset) {
PagingSelect.append (") row_ where rownum?")
}
Else {
PagingSelect.append (") where rownum? and RowNumber? and RowNumber
