导航：首页 > 数据库 >

sqlserver2005的分页优化

发表于：2024-09-22 作者：千家信息网编辑

千家信息网最后更新 2024年09月22日，这篇文章主要介绍"sqlserver2005的分页优化"，在日常操作中，相信很多人在sqlserver2005的分页优化问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答"s

千家信息网最后更新 2024年09月22日sqlserver2005的分页优化

这篇文章主要介绍"sqlserver2005的分页优化"，在日常操作中，相信很多人在sqlserver2005的分页优化问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答"sqlserver2005的分页优化"的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

一、hibernate分页 hibernate对MsSql的伪分页
分页是web项目中比不可少的一个功能，数据量大的时候不能全部展示必然要用到分页技术。相信大家对hibernate中的分页都不陌生：
Java代码
public Query setMaxResults(int maxResults);
public Query setFirstResult(int firstResult);
Java代码
public Query setMaxResults(int maxResults);
public Query setFirstResult(int firstResult);

只要调用了这两个方法并设置好参数，hibernate自动分页完全屏蔽了底层分页技术，这也是众多开发者喜欢hibernate的原因之一。
项目开发中遇到一个奇怪的问题。数据库采用的是Sql Server 2005，也设置了上面两个参数，可是每次发送到数据库端的SQL语句都是select top ....语句。即便是查询第10w条，也只有一个select top 语句，不免引起对hibernate实现sql server分页的怀疑。hibernate针对不同数据库实现的分页方法封装在对应数据库的方言里，通过getLimitString方法转化成对应数据库的分页算法。
以常见的Mysql数据库的方言MySQLDialect为例：
Java代码
public String getLimitString(String sql, boolean hasOffset) {
return new StringBuffer( sql.length() + 20 )
.append( sql )
.append( hasOffset ? " limit ?, ?" : " limit ?" )
.toString();
}
Java代码
public String getLimitString(String sql, boolean hasOffset) {
return new StringBuffer( sql.length() + 20 )
.append( sql )
.append( hasOffset ? " limit ?, ?" : " limit ?" )
.toString();
}

采用了大家熟悉的的limit进行分页。
数据库的方言Oracle9iDialect：
Java代码
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 <= ?) where rownum_ > ?");
}
else {
pagingSelect.append(" ) where rownum <= ?");
}
Java代码
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 <= ?) where rownum_ > ?");
}
else {
pagingSelect.append(" ) where rownum <= ?");
}

利用Oracle的rownum 结合三层嵌套查询完成分页。这个三层是Oracle最经典高效的分页算法。
可是针对Sql Server的方言SQLServerDialect：
Java代码
public String getLimitString(String querySelect, int offset, int limit) {
if ( offset > 0 ) {
throw new UnsupportedOperationException( "query result offset is not supported" );
}
return new StringBuffer( querySelect.length() + 8 )
.append( querySelect )
.insert( getAfterSelectInsertPoint( querySelect ), " top " + limit )
.toString();
}
Java代码
public String getLimitString(String querySelect, int offset, int limit) {
if ( offset > 0 ) {
throw new UnsupportedOperationException( "query result offset is not supported" );
}
return new StringBuffer( querySelect.length() + 8 )
.append( querySelect )
.insert( getAfterSelectInsertPoint( querySelect ), " top " + limit )
.toString();
}

揉揉眼睛、再揉揉，没错，只出现了一个top语句。这就意味着如果查询第10w页的数据，需要把前10w页数据全部提取出来。hibernate针对sql server的分页是伪分页，所以随着数据量日益增加用户抱怨系统速度慢，程序员抱怨hibernate性能低，dba抱怨开发人员sql功底太浅。
不知道hibernate开发组，出于什么目前或情况没有真正提供sql server的分页技术，那我们自己来实现。
方言类：
Java代码
public class SQLServer2005Dialect extends SQLServerDialect {

/**
*
* 是否需要绑定limit参数?
*
* 在SQL Server中使用top时不能使用参数表示top条数,而使用ROW_NUMBER()则需要提供limit参数
*/

private ThreadLocal supportsVariableLimit = new ThreadLocal();
public SQLServer2005Dialect() {
registerFunction("bitand", new BitAndFunction());
registerFunction("bitxor", new BitXorFunction());
registerFunction("bitor", new BitOrFunction());
setSupportsVariableLimit(false);
}

/**
*
*

* 设置是否先绑定limit参数。
*

*
* @param first
*/

private void setSupportsVariableLimit(boolean first) {
this.supportsVariableLimit.set(Boolean.valueOf(first));
}

/**
*
*

* 获取sql中select子句位置。
*

*
* @param sql
*
* @return int
*/
protected static int getSqlAfterSelectInsertPoint(String sql) {
int selectIndex = sql.toLowerCase().indexOf("select");

int selectDistinctIndex = sql.toLowerCase().indexOf("select distinct");

return selectIndex + ((selectDistinctIndex == selectIndex) ? 15 : 6);
}

public boolean supportsLimitOffset() {
return true;
}

/*
* Hibernate在获得Limit String(已添加了limit子句)后，如果此方法返回true，
*
* 则会添加额外的参数值(ROW_NUMBER()范围)(策略可能是这样：有offset设置两个参数值，没有设置一个参数值)
*/
public boolean supportsVariableLimit() {
return ((Boolean) this.supportsVariableLimit.get()).booleanValue();
}

public boolean useMaxForLimit() {
return true;
}
/**
* 首页top，以后用ROW_NUMBER
*/
public String getLimitString(String query, int offset, int limit) {
setSupportsVariableLimit(offset > 0);

if (offset == 0) {
return new StringBuffer(query.length() + 8).append(query).insert(
getSqlAfterSelectInsertPoint(query), " top " + limit)
.toString();
}

return getLimitString(query, offset > 0);
}

public String getLimitString(String sql, boolean hasOffset) {
int orderByIndex = sql.toLowerCase().lastIndexOf("order by");

if (orderByIndex <= 0) {
throw new UnsupportedOperationException(
"must specify 'order by' statement to support limit operation with offset in sql server 2005");
}

String sqlOrderBy = sql.substring(orderByIndex + 8);

String sqlRemoveOrderBy = sql.substring(0, orderByIndex);

int insertPoint = getSqlAfterSelectInsertPoint(sql);
return new StringBuffer(sql.length() + 100)
.append("with tempPagination as(")
.append(sqlRemoveOrderBy)
.insert(
insertPoint + 23,
" ROW_NUMBER() OVER(ORDER BY " + sqlOrderBy
+ ") as RowNumber,")
.append(
") select * from tempPagination where RowNumber>? and RowNumber<=?")
.toString();
}
}
Java代码
public class SQLServer2005Dialect extends SQLServerDialect {

/**
*
* 是否需要绑定limit参数?
*
* 在SQL Server中使用top时不能使用参数表示top条数,而使用ROW_NUMBER()则需要提供limit参数
*/

private ThreadLocal supportsVariableLimit = new ThreadLocal();
public SQLServer2005Dialect() {
registerFunction("bitand", new BitAndFunction());
registerFunction("bitxor", new BitXorFunction());
registerFunction("bitor", new BitOrFunction());
setSupportsVariableLimit(false);
}

/**
*
*

* 设置是否先绑定limit参数。
*

*
* @param first
*/

private void setSupportsVariableLimit(boolean first) {
this.supportsVariableLimit.set(Boolean.valueOf(first));
}

/**
*
*

* 获取sql中select子句位置。
*

函数：
Java代码
public class BitAndFunction implements SQLFunction {
public Type getReturnType(Type type, Mapping mapping) {
return Hibernate.INTEGER;
}

public boolean hasArguments() {
return true;
}

public boolean hasParenthesesIfNoArguments() {
return true;
}

public String render(List args, SessionFactoryImplementor factory)
throws QueryException {
if (args.size() != 2) {
throw new IllegalArgumentException(
"BitAndFunction requires 2 arguments!");
}
return args.get(0).toString() + " & " + args.get(1).toString();
}
}
Java代码
public class BitAndFunction implements SQLFunction {
public Type getReturnType(Type type, Mapping mapping) {
return Hibernate.INTEGER;
}

public boolean hasArguments() {
return true;
}

public boolean hasParenthesesIfNoArguments() {
return true;
}

public String render(List args, SessionFactoryImplementor factory)
throws QueryException {
if (args.size() != 2) {
throw new IllegalArgumentException(
"BitAndFunction requires 2 arguments!");
}
return args.get(0).toString() + " & " + args.get(1).toString();
}
}

Java代码
public class BitOrFunction implements SQLFunction {
public Type getReturnType(Type type, Mapping mapping) {
return Hibernate.INTEGER;
}
public boolean hasArguments() {
return true;
}
public boolean hasParenthesesIfNoArguments() {
return true;
}
public String render(List args, SessionFactoryImplementor factory)
throws QueryException {
if (args.size() != 2) {
throw new IllegalArgumentException(
"BitOrFunction requires 2 arguments!");
}
return args.get(0).toString() + " | " + args.get(1).toString();
}
}
Java代码
public class BitOrFunction implements SQLFunction {
public Type getReturnType(Type type, Mapping mapping) {
return Hibernate.INTEGER;
}
public boolean hasArguments() {
return true;
}
public boolean hasParenthesesIfNoArguments() {
return true;
}
public String render(List args, SessionFactoryImplementor factory)
throws QueryException {
if (args.size() != 2) {
throw new IllegalArgumentException(
"BitOrFunction requires 2 arguments!");
}
return args.get(0).toString() + " | " + args.get(1).toString();
}
}

Java代码
public Type getReturnType(Type type, Mapping mapping) {
return Hibernate.INTEGER;
}
public boolean hasArguments() {
return true;
}
public boolean hasParenthesesIfNoArguments() {
return true;
}
public String render(List args, SessionFactoryImplementor factory)
throws QueryException {
if (args.size() != 2) {
throw new IllegalArgumentException(
"BitXorFunction requires 2 arguments!");
}
return args.get(0).toString() + " ^ " + args.get(1).toString();
}