- 浏览: 24768 次
- 性别:
- 来自: 广州
文章分类
最新评论
有同学强调我对sql server的分页不了解,在mssql 2000之前只能通过top进行分页。我没有说top分页不对,我是说在hibernate的sql server方言里面,针对sql server 的top分页是不对的。至少在我使用的版本里是不对的。而且有很多同学也遇到和我类似的问题。请大家打开hibernate的showsql,看看生成的sql 语句再来判断正确还是错误,谢谢!
提到Hibernate,好多人都用“垃圾”,“效率太低”这样的字眼评价。其实任何一个框架的产生都是有原因的,这些为企业级开发产生的框架最初只是为了简化开发,或许会有一些影响性能的地方。但是通过配置和巧妙的办法,完全可以规避这些问题,发挥最大的效果。至少目前我的项目,日均pv在500w左右目前还没发现什么问题。
一、hibernate分页 hibernate对MsSql的伪分页
分页是web项目中比不可少的一个功能,数据量大的时候不能全部展示必然要用到分页技术。相信大家对hibernate中的分页都不陌生:
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为例:
- public String getLimitString(String sql, boolean hasOffset) {
- return new StringBuffer( sql.length() + 20 )
- .append( sql )
- .append( hasOffset ? " limit ?, ?" : " limit ?" )
- .toString();
- }
public String getLimitString(String sql, boolean hasOffset) { return new StringBuffer( sql.length() + 20 ) .append( sql ) .append( hasOffset ? " limit ?, ?" : " limit ?" ) .toString(); }
采用了大家熟悉的mysql的limit进行分页。
Oracle数据库的方言Oracle9iDialect:
- 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 <= ?");
- }
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:
- 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();
- }
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的分页技术,那我们自己来实现。
方言类:
- public class SQLServer2005Dialect extends SQLServerDialect {
- /**
- *
- * 是否需要绑定limit参数?
- *
- * 在SQL Server中使用top时不能使用参数表示top条数,而使用ROW_NUMBER()则需要提供limit参数
- */
- private ThreadLocal<Boolean> supportsVariableLimit = new ThreadLocal<Boolean>();
- public SQLServer2005Dialect() {
- registerFunction("bitand", new BitAndFunction());
- registerFunction("bitxor", new BitXorFunction());
- registerFunction("bitor", new BitOrFunction());
- setSupportsVariableLimit(false);
- }
- /**
- *
- * <p>
- * 设置是否先绑定limit参数。
- * </p>
- *
- * @param first
- */
- private void setSupportsVariableLimit(boolean first) {
- this.supportsVariableLimit.set(Boolean.valueOf(first));
- }
- /**
- *
- * <p>
- * 获取sql中select子句位置。
- * </p>
- *
- * @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();
- }
- }
public class SQLServer2005Dialect extends SQLServerDialect { /** * * 是否需要绑定limit参数? * * 在SQL Server中使用top时不能使用参数表示top条数,而使用ROW_NUMBER()则需要提供limit参数 */ private ThreadLocal<Boolean> supportsVariableLimit = new ThreadLocal<Boolean>(); public SQLServer2005Dialect() { registerFunction("bitand", new BitAndFunction()); registerFunction("bitxor", new BitXorFunction()); registerFunction("bitor", new BitOrFunction()); setSupportsVariableLimit(false); } /** * * <p> * 设置是否先绑定limit参数。 * </p> * * @param first */ private void setSupportsVariableLimit(boolean first) { this.supportsVariableLimit.set(Boolean.valueOf(first)); } /** * * <p> * 获取sql中select子句位置。 * </p> * * @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(); } }
函数:
- 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();
- }
- }
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(); } }
- 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();
- }
- }
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(); } }
- 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();
- }
转载:http://www.iteye.com/topic/721903
相关推荐
资源名称:SQL Server 2005 一千万条以上记录分页数据库优化经验总结 资源截图: 资源太大,传百度网盘了,链接在附件中,有需要的同学自取。
sqlserver2005的select语句top子句可以跟参数,这样就很好的解决了分页的问题,通用的写法就是: select 【排序列】,【显示列1...N】 from TableName where 【排序列】 in select top (@PageNo*@PageSize) ...
SQLServer海量数据库的查询优化及分页算法方案
SQLSERVER高效率分页存储过程,数据量在2千万下查询分页只需要1-2秒,优化良好....
《SQL Server海量数据库的查询优化及分页算法方案》,忘了从哪里搞来的。
jsp分页、PLSQL中用Group by和Having来实现分组统计、SQL_server_海量数据库查询优化及分页算法、SQL_SERVER数据库中实现快速的数据提取和数据分页、SQLServer 2005下的分页SQL、sql分页查询、sql数据库模糊查询分页...
SQLServer2008Dialect 优化了原来的分页查询数据方法以及在生成SQL时表后增加了with(nolock)
sql server 2005的分页存储过程分3个版本,一个是没有优化过的,一...1.SqlServer 2005: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE proc [dbo].[up_Page2005] @TableName varchar(50), --表名 @Fi
SQLServer2008Dialect 优化了原来的分页查询数据方法以及在生成SQL时表后增加了with(nolock)
sqlserver2008不支持关键字limit ,所以它的分页sql查询语句将不能用MySQL的方式进行,幸好sqlserver2008提供了top,rownumber等关键字,这样就能通过这几个关键字实现分页。 下面是本人在网上查阅到的几种查询脚本...
本文我们主要对SQL Server多表查询的优化方案进行了总结,并给出了实际的例子进行性能与效率的对比,需要的朋友可以参考下
SqlSever2005.一千万条以上记录分页数据库优化经验总结【索引优化.代码优化】一周搞定
行式引擎按页取数只适用于Oracle,mysql,hsql和sqlserver2008及以上数据库,其他数据库,如access,sqlserver2005,sqlite等必须编写分页SQL。今天我们以Access数据库为例介绍需要写分页SQL的数据库怎样利用行式的...
海量 数据库 的查询 优化 及 分页 算法 方案
海量数据分页,海量数据查询,海量数据排序
包含一些常见的SqlServer问题解决方法,包括:sa忘记密码怎么办?SQL server2008过期激活方法?SqlSever千万条以上记录分页数据库优化经验总结,删除所有存储过程的方法及一个简单的游标示例.
代码如下: –代码一DECLARE @cc INT SELECT NewsId,ROW_NUMBER() OVER(ORDER BY SortNum DESC) AS RowIndex INTO #tb FROM news WITH(NOLOCK) WHERE NewsTypeId=@NewsTypeId AND IsShow=1 SET @cc = @@ROWCOUNT ...
《Microsoft SQL Server 2008技术内幕:T-SQL查询》全面深入地介绍了Microsoft SQL Server 2008中高级T-SQL查询、性能优化等方面的内容,以及SQL Server 2008新增加的一些特性。主要内容包括SQL的基础理论、查询优化...
《Microsoft SQL Server 2008技术内幕:T-SQL查询》全面深入地介绍了Microsoft SQL Server 2008中高级T-SQL查询、性能优化等方面的内容,以及SQL Server 2008新增加的一些特性。主要内容包括SQL的基础理论、查询优化...