`

sqlserver2005 分页优化

 
阅读更多

有同学强调我对sql server的分页不了解，在mssql 2000之前只能通过top进行分页。我没有说top分页不对，我是说在hibernate的sql server方言里面，针对sql server 的top分页是不对的。至少在我使用的版本里是不对的。而且有很多同学也遇到和我类似的问题。请大家打开hibernate的showsql，看看生成的sql 语句再来判断正确还是错误，谢谢！
提到Hibernate，好多人都用“垃圾”，“效率太低”这样的字眼评价。其实任何一个框架的产生都是有原因的，这些为企业级开发产生的框架最初只是为了简化开发，或许会有一些影响性能的地方。但是通过配置和巧妙的办法，完全可以规避这些问题，发挥最大的效果。至少目前我的项目，日均pv在500w左右目前还没发现什么问题。
一、hibernate分页 hibernate对MsSql的伪分页
分页是web项目中比不可少的一个功能，数据量大的时候不能全部展示必然要用到分页技术。相信大家对hibernate中的分页都不陌生：

Java代码 复制代码 收藏代码
  1. public Query setMaxResults(int maxResults);   
  2. public Query setFirstResult(int firstResult);   
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代码 复制代码 收藏代码
  1. public String getLimitString(String sql, boolean hasOffset) {   
  2.         return new StringBuffer( sql.length() + 20 )   
  3.                 .append( sql )   
  4.                 .append( hasOffset ? " limit ?, ?" : " limit ?" )   
  5.                 .toString();   
  6.     }  
public String getLimitString(String sql, boolean hasOffset) {
		return new StringBuffer( sql.length() + 20 )
				.append( sql )
				.append( hasOffset ? " limit ?, ?" : " limit ?" )
				.toString();
	}


采用了大家熟悉的mysql的limit进行分页。
Oracle数据库的方言Oracle9iDialect：

Java代码 复制代码 收藏代码
  1. StringBuffer pagingSelect = new StringBuffer( sql.length()+100 );   
  2.         if (hasOffset) {   
  3.             pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( ");   
  4.         }   
  5.         else {   
  6.             pagingSelect.append("select * from ( ");   
  7.         }   
  8.         pagingSelect.append(sql);   
  9.         if (hasOffset) {   
  10.             pagingSelect.append(" ) row_ where rownum <= ?) where rownum_ > ?");   
  11.         }   
  12.         else {   
  13.             pagingSelect.append(" ) where rownum <= ?");   
  14.         }  
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代码 复制代码 收藏代码
  1. public String getLimitString(String querySelect, int offset, int limit) {   
  2.         if ( offset > 0 ) {   
  3.             throw new UnsupportedOperationException( "query result offset is not supported" );   
  4.         }   
  5.         return new StringBuffer( querySelect.length() + 8 )   
  6.                 .append( querySelect )   
  7.                 .insert( getAfterSelectInsertPoint( querySelect ), " top " + limit )   
  8.                 .toString();   
  9.     }  
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代码 复制代码 收藏代码
  1. public class SQLServer2005Dialect extends SQLServerDialect {   
  2.   
  3.     /**  
  4.      *   
  5.      * 是否需要绑定limit参数?  
  6.      *   
  7.      * 在SQL Server中使用top时不能使用参数表示top条数,而使用ROW_NUMBER()则需要提供limit参数  
  8.      */  
  9.   
  10.     private ThreadLocal<Boolean> supportsVariableLimit = new ThreadLocal<Boolean>();   
  11.     public SQLServer2005Dialect() {   
  12.         registerFunction("bitand", new BitAndFunction());   
  13.         registerFunction("bitxor", new BitXorFunction());   
  14.         registerFunction("bitor", new BitOrFunction());   
  15.         setSupportsVariableLimit(false);   
  16.     }   
  17.   
  18.     /**  
  19.      *   
  20.      * <p>  
  21.      * 设置是否先绑定limit参数。  
  22.      * </p>  
  23.      *   
  24.      * @param first  
  25.      */  
  26.   
  27.     private void setSupportsVariableLimit(boolean first) {   
  28.         this.supportsVariableLimit.set(Boolean.valueOf(first));   
  29.     }   
  30.   
  31.     /**  
  32.      *   
  33.      * <p>  
  34.      * 获取sql中select子句位置。  
  35.      * </p>  
  36.      *   
  37.      * @param sql  
  38.      *   
  39.      * @return int  
  40.      */  
  41.     protected static int getSqlAfterSelectInsertPoint(String sql) {   
  42.         int selectIndex = sql.toLowerCase().indexOf("select");   
  43.   
  44.         int selectDistinctIndex = sql.toLowerCase().indexOf("select distinct");   
  45.   
  46.         return selectIndex + ((selectDistinctIndex == selectIndex) ? 15 : 6);   
  47.     }   
  48.   
  49.     public boolean supportsLimitOffset() {   
  50.         return true;   
  51.     }   
  52.   
  53.     /*  
  54.      * Hibernate在获得Limit String(已添加了limit子句)后，如果此方法返回true，  
  55.      *   
  56.      * 则会添加额外的参数值(ROW_NUMBER()范围)(策略可能是这样：有offset设置两个参数值，没有设置一个参数值)  
  57.      */  
  58.     public boolean supportsVariableLimit() {   
  59.         return ((Boolean) this.supportsVariableLimit.get()).booleanValue();   
  60.     }   
  61.   
  62.     public boolean useMaxForLimit() {   
  63.         return true;   
  64.     }   
  65.     /**  
  66.      * 首页top，以后用ROW_NUMBER  
  67.      */  
  68.     public String getLimitString(String query, int offset, int limit) {   
  69.         setSupportsVariableLimit(offset > 0);   
  70.   
  71.         if (offset == 0) {   
  72.             return new StringBuffer(query.length() + 8).append(query).insert(   
  73.                     getSqlAfterSelectInsertPoint(query), " top " + limit)   
  74.                     .toString();   
  75.         }   
  76.   
  77.         return getLimitString(query, offset > 0);   
  78.     }   
  79.        
  80.     public String getLimitString(String sql, boolean hasOffset) {   
  81.         int orderByIndex = sql.toLowerCase().lastIndexOf("order by");   
  82.   
  83.         if (orderByIndex <= 0) {   
  84.             throw new UnsupportedOperationException(   
  85.                     "must specify 'order by' statement to support limit operation with offset in sql server 2005");   
  86.         }   
  87.   
  88.         String sqlOrderBy = sql.substring(orderByIndex + 8);   
  89.   
  90.         String sqlRemoveOrderBy = sql.substring(0, orderByIndex);   
  91.   
  92.         int insertPoint = getSqlAfterSelectInsertPoint(sql);   
  93.         return new StringBuffer(sql.length() + 100)   
  94.                 .append("with tempPagination as(")   
  95.                 .append(sqlRemoveOrderBy)   
  96.                 .insert(   
  97.                         insertPoint + 23,   
  98.                         " ROW_NUMBER() OVER(ORDER BY " + sqlOrderBy   
  99.                                 + ") as RowNumber,")   
  100.                 .append(   
  101.                         ") select * from tempPagination where RowNumber>?  and RowNumber<=?")   
  102.                 .toString();   
  103.     }   
  104. }  
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();
	}
}


函数：

Java代码 复制代码 收藏代码
  1. public class BitAndFunction implements SQLFunction {   
  2.     public Type getReturnType(Type type, Mapping mapping) {   
  3.         return Hibernate.INTEGER;   
  4.     }   
  5.   
  6.     public boolean hasArguments() {   
  7.         return true;   
  8.     }   
  9.   
  10.     public boolean hasParenthesesIfNoArguments() {   
  11.         return true;   
  12.     }   
  13.   
  14.     public String render(List args, SessionFactoryImplementor factory)   
  15.             throws QueryException {   
  16.         if (args.size() != 2) {   
  17.             throw new IllegalArgumentException(   
  18.                     "BitAndFunction requires 2 arguments!");   
  19.         }   
  20.         return args.get(0).toString() + " & " + args.get(1).toString();   
  21.     }   
  22. }  
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代码 复制代码 收藏代码
  1. public class BitOrFunction implements SQLFunction {   
  2.     public Type getReturnType(Type type, Mapping mapping) {   
  3.         return Hibernate.INTEGER;   
  4.     }   
  5.     public boolean hasArguments() {   
  6.         return true;   
  7.     }   
  8.     public boolean hasParenthesesIfNoArguments() {   
  9.         return true;   
  10.     }   
  11.     public String render(List args, SessionFactoryImplementor factory)   
  12.             throws QueryException {   
  13.         if (args.size() != 2) {   
  14.             throw new IllegalArgumentException(   
  15.                     "BitOrFunction requires 2 arguments!");   
  16.         }   
  17.         return args.get(0).toString() + " | " + args.get(1).toString();   
  18.     }   
  19. }  
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代码 复制代码 收藏代码
  1. public Type getReturnType(Type type, Mapping mapping) {   
  2.         return Hibernate.INTEGER;   
  3.     }   
  4.     public boolean hasArguments() {   
  5.         return true;   
  6.     }   
  7.     public boolean hasParenthesesIfNoArguments() {   
  8.         return true;   
  9.     }   
  10.     public String render(List args, SessionFactoryImplementor factory)   
  11.             throws QueryException {   
  12.         if (args.size() != 2) {   
  13.             throw new IllegalArgumentException(   
  14.                     "BitXorFunction requires 2 arguments!");   
  15.         }   
  16.         return args.get(0).toString() + " ^ " + args.get(1).toString();   
  17.     }  

转载：http://www.iteye.com/topic/721903

分享到：
| spring3.0.6 aop 配置
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    SQL Server2005分页查询

    在SQL Server 2005中，分页查询是一个常见的需求，特别是在处理大量数据时，为了提高用户体验，通常需要一次性加载一定数量的数据，而不是一次性显示所有数据。以下将详细讲解SQL Server 2005中分页查询的常用方法。...

    SQLServer2005一千万条以上记录分页数据库优化经验总结

    资源名称：SQL Server 2005 一千万条以上记录分页数据库优化经验总结 资源截图： 资源太大，传百度网盘了，链接在附件中，有需要的同学自取。

    ASP.NET基于SQLServer的分页

    3. **SQL Server分页查询** - `TOP`子句：在SQL Server中，可以使用`TOP`子句配合`ORDER BY`来实现分页。例如，获取第一页的数据，可以写成`SELECT TOP pageSize * FROM table ORDER BY sortField DESC`。 - `...

    vc+sqlserver2005分页模块实现

    本教程将深入探讨如何在VC++（Microsoft Visual C++）环境中，结合SQL Server 2005数据库，实现高效的分页查询功能。这有助于提高用户体验，减少网络负载，同时确保数据的有序、稳定显示。 首先，理解分页的概念。...

    Hibernate+sqlserver2000分页

    综上所述，`Hibernate+sqlserver2000分页`是一个结合了ORM框架和数据库分页策略的技术主题。在实际应用中，开发者需要根据业务需求选择合适的分页方法，并注意性能优化，如避免全表扫描，使用索引来加速查询等。理解...

    java 连接sqlserver数据库查询，并分页显示

    String url = "jdbc:sqlserver://localhost:1433;databaseName=myDatabase"; String username = "myUsername"; String password = "myPassword"; try { Class.forName(...

    SQLServer分页.rar

    本资源"SQLServer分页.rar"包含了一个名为"SQL分页.txt"的文件，里面详细讲解了SQL Server中的分页实现方法。 首先，我们来理解SQL Server分页的基本概念。分页通常涉及到两个关键参数：`OFFSET`和`FETCH NEXT`。`...

    SqlServer数据库分页

    在SQL Server数据库中，分页是一种常见的数据检索技术，它允许我们从大量数据中按需获取一部分结果，而不是一次性加载所有记录。这在处理大型数据集时尤其有用，可以提高性能，减少网络传输，以及提供更好的用户体验...

    sqlserver 实现分页的前台代码 以及后台的sqlserver语句

    在后端，我们需要处理分页请求，生成对应的SQL Server分页查询语句，并返回结果。这里假设使用Java的Servlet或Spring MVC来处理请求。 1. SQL Server分页语句： SQL Server提供了多种方式实现分页，比如`ROW_NUMBER...

    java语言的分页查询功能(mysql和sql server)

    本教程将详细讲解如何在Java中实现不分框架的分页查询，同时涵盖对MySQL和SQL Server数据库的支持。 一、基础知识 1. 分页概念：分页是将大量数据按一定数量分成若干部分，每次只加载一部分到内存中显示，用户可以...

    sql server分页技术（SQL Server 与Access数据库相关分页技术）

    以下是几种常用的SQL Server分页技术： 1. **TOP 和 NOT IN** 分页： 这种方法通过`TOP`关键字选取指定数量的记录，然后使用`NOT IN`子句排除已选取的记录。例如，查询第二页的记录，可以先选取前`@RecordStart`个...

    SQL Server 2005基础教程

    - "SQLServer2005教程.pdf"：可能是一份全面的教程，涵盖数据库设计、查询、管理等方面。 - "SQL Server 2005基础入门教程.pdf"：同上，可能侧重于基础操作和使用技巧。 - "SQL2005学习教程较为详细.ppt"：可能是PPT...

    Sql Server2005数据库

    "SQLServer2005样例数据库.rar"可能包含SQL Server 2005的标准示例数据库，如AdventureWorks，它可以帮助学习者了解实际数据库结构和业务场景。"SQLServer2005第14章源代码.rar"可能包含了与某一教材或课程相关的...

    DataGridView分页(Sql Server 2005)

    本教程将详细讲解如何在C#环境中，结合Sql Server 2005数据库实现`DataGridView`的分页功能。 一、基本概念与原理 1. `DataGridView`: 这是Windows Forms中的一个控件，可以显示二维表格数据，支持数据编辑、排序...

    SQL server 分页查询

    ### SQL Server分页查询概述 分页查询是指从大量数据中按需取出一部分数据的过程，通常用于网页展示或大型数据报表中，以提高加载速度和减少服务器负担。在SQL Server中，实现分页查询有多种方法，包括使用`ROW_...

    千万级数据分页查询存储过程SQLServer

    在SQL Server中，面对千万级别的大数据量，进行有效的分页查询是数据库性能优化的重要环节。存储过程在这种场景下显得尤为重要，因为它们可以提高查询效率，减少网络传输，并且便于管理和重用代码。本文将深入探讨...

    SQL Server 2005内存调优

    在数据库管理系统（DBMS）的运维过程中，SQL Server 2005 的性能优化是确保系统稳定运行的关键之一。其中，内存管理尤其重要，因为它直接影响到数据查询的速度和效率。本文将详细介绍SQL Server 2005 内存调优的相关...

    《Microsoft SQL Server 2005技术内幕：T-SQL查询》示例代码

    《Microsoft SQL Server 2005技术内幕：T-SQL查询》是一本深入解析SQL Server 2005中T-SQL查询语言的专业书籍。T-SQL（Transact-SQL）是微软对标准SQL的扩展，是SQL Server的核心组件，用于数据库管理、数据查询和...

    sql server 分页大全

    在SQL Server数据库管理中，分页查询是一种常用的技术，它允许用户按页面浏览大量数据，而不是一次性加载所有结果。在大型数据库中，这可以显著提高性能，减少内存消耗，并提供更好的用户体验。本篇文章将深入探讨...

Magicbox
Global site tag (gtag.js) - Google Analytics