Improving Hibernate's Performance

Connection Pooling:-

        Opening a connection to a database is generally much more expensive than executing an SQL statement. A connection pool is used to minimize the number of connections opened between application and database. Much like a library, your application code needs to be strict about returning connections to the pool when complete, for if it does not do so, your application will run out of connections.This problem can be avoided by always using a finally block to close your connection, as shown throughout this book.

Starving a Pool:-

         Hibernate supports a variety of connection pooling mechanisms. If you are using an application server, you may wish to use the built-in pool (typically a connection is obtaining using JNDI). If you can't or don't wish to use your application server's built-in connection pool, Hibernate supports several other connection pools

c3p0	http://sourceforge.net/projects/c3p0	Distributed with Hibernate
Apache DBCP	http://jakarta.apache.org/commons/dbcp/	Apache Pool
Proxool	http://proxool.sourceforge.net/	JDBC Pooling Wrapper

     

 If you wish to use c3p0, the version distributed with Hibernate 2.1.2 (0.8.3) is out of date (and GPL is a problem if you wish to distribute a non-GPL application). If you wish to distribute an application that makes use of c3p0, make sure to download the latest (LGPL) release, c3p0-0.8.4-test1 or later.

Statement Cache:-

      Certain connection pools, drivers, databases, and other portions of the system may provide an additional cache system, known as a statement cache. This cache stores a partially compiled version of a statement in order to increase performance. By reusing the parsed or precompiled statement, the application is able to trade an increase in memory usage for a boost in performance.

Hibernate c3p0 Configuration:-

hibernate.connection.driver_class=com.mysql.jdbc.Driver

hibernate.connection.url=jdbc:mysql://localhost/hibernate

hibernate.connection.username=root

hibernate.connection.password=

hibernate.dialect=net.sf.hibernate.dialect.MySQLDialect

hibernate.show_sql=false

hibernate.c3p0.max_size=1

hibernate.c3p0.min_size=0

hibernate.c3p0.timeout=5000

hibernate.c3p0.max_statements=100

hibernate.c3p0.idle_test_period=300

hibernate.c3p0.acquire_increment=2

c3p0 Configuration Options

Property Meaning	Property	Example
Maximum number of database connections to open	hibernate.c3p0.max_size	15
Initial number of database connections	hibernate.c3p0.min_size	3
Maximum idle time for a connection (in seconds)	hibernate.c3p0.timeout	5000
Maximum size of c3p0 statement cache (0 to turn off)	hibernate.c3p0.max_ statements	0
Number of connections in a clump acquired when pool is exhausted	hibernate.c3p0.acquire_ increment	3
Idle time before a c3p0 pooled connection is validated (in seconds)	hibernate.c3p0.idle_test period _	300
Validate the connection on checkout. Recommend setting thehibernate.c3p0.idle_test_periodproperty instead. Defaults to false	hibernate.c3p0.validate	true |false

Apache DBCP Configuration Options

Property Meaning	Property	Example
Maximum number of checked-out database connections	hibernate.dbcp.maxActive	8
Maximum number of idle database connections for connection pool	hibernate.dbcp.maxIdle	8
Maximum idle time for connections in connection pool (expressed in ms).	hibernate.dbcp.max Wait	-1
Set to -1 to turn off
Action to take in case of an exhausted DBCP connection pool. Set to 0 to fail, 1 to block until a connection is made available, or 2 to grow)	hibernate.dbcp.whenExhaustedAction	1
Validate connection when borrowing connection from pool (defaults to true)	hibernate.dbcp.test OnBorrow	true |false
Validate connection when returning connection to pool (optional, true, or false)	hibernate.dbcp.test OnReturn	true |false
Query to execute for connection validation (optional, requires either hibernate.dbcp.testOn Borrow orhibernate.dbcp.testOnReturn)	hibernate.dbcp.validationQuery	Valid SQL SELECT statement(e.g.,SELECT 1+1)
Maximum number of checked-out statements	hibernate.dbcp.ps.maxActive	8
Maximum number of idle statements	hibernate.dbcp.ps.maxIdle	8
Maximum idle time for statements (in ms)	hibernate.dbcp.ps.maxWait	1000 * 60 * 30
Action to take in case of an exhausted statement pool. Set to0 to fail, 1 to block until a statement is made available, or 2to grow)	hibernate.dbcp.ps.whenExhaustedAction	1

 

 

Proxool Configuration Options:-

Uses a standard Java properties file to configure Proxool. For example:

 jdbc-0.proxool.alias=property-test

 jdbc-0.proxool.driver-url=jdbc:hsqldb:.

 jdbc-0.proxool.driver-class=org.hsqldb.jdbcDriver

 jdbc-0.user=foo

 jdbc-0.password=bar

 jdbc-0.proxool.house-keeping-sleep-time=40000

 jdbc-0.proxool.house-keeping-test-sql=select CURRENT_DATE

 jdbc-0.proxool.maximum-connection-count=10

 jdbc-0.proxool.minimum-connection-count=3

 jdbc-0.proxool.maximum-connection-lifetime=18000000

 jdbc-0.proxool.simultaneous-build-throttle=5

 jdbc-0.proxool.recently-started-threshold=40000

 jdbc-0.proxool.overload-without-refusal-lifetime=50000

 jdbc-0.proxool.maximum-active-time=60000

 jdbc-0.proxool.verbose=true

 jdbc-0.proxool.trace=true

 jdbc-0.proxool.fatal-sql-exception=Fatal error

 jdbc-0.proxool.prototype-count=2

 jdbc-1.proxool.alias=property-test-2

 jdbc-1.proxool.driver-url=jdbc:hsqldb:.

 jdbc-1.proxool.driver-class=org.hsqldb.jdbcDriver

 jdbc-1.user=scott

 jdbc-1.password=tiger

 jdbc-1.proxool.house-keeping-sleep-time=40000

 jdbc-1.proxool.house-keeping-test-sql=select CURRENT_DATE

 jdbc-1.proxool.maximum-connection-count=10

 jdbc-1.proxool.minimum-connection-count=3

 jdbc-1.proxool.maximum-connection-lifetime=18000000

 jdbc-1.proxool.simultaneous-build-throttle=5

 jdbc-1.proxool.recently-started-threshold=40000

 jdbc-1.proxool.overload-without-refusal-lifetime=50000

 jdbc-1.proxool.maximum-active-time=60000

 jdbc-1.proxool.verbose=true

 jdbc-1.proxool.trace=true

 jdbc-1.proxool.fatal-sql-exception=Fatal error

 jdbc-1.proxool.prototype-count=2

The first word (up to the first dot) must start with "jdbc", but it can be anything you like. Use unique names to identify each pool. Any property not starting with "jdbc" will be ignored. The properties prefixed with "proxool." will be used by Proxool while the properties that are not prefixed will be passed on to the delegate JDBC driver.

Property Meaning	Property	Example
Configure Proxool provider using an XML file	hibernate.proxool.xml	/path/to/file.xml
Configure the Proxool provider using a properties file.properties	hibernate.proxool.properties	/path/to/proxool
Configure the Proxool provider from an existing pool	hibernate.proxool.existing_pool	true | false
Proxool pool alias to use (required forhibernate.proxool.existing_pool, hibernate.proxool .properties, hibernate .proxool.xml)	hibernate.proxool.pool_alias	As set by Proxoolconfiguration

And then simply call the property configurator in your startup code:

PropertyConfigurator.configure("src/java-test/org/logicalcobwebs/proxool/configuration/test.properties");

Again, picking up connections is really easy ("property-test" is the alias we used above)

connection = DriverManager.getConnection("proxool.property-test");

Connection pool library implementation is given here.