Site Announcements

Register and Login to Sanjaal Board to get access to exclusive contents including guitar tabs, high quality arts, glamor and photography pictures. New Users will be manually validated against spam database and potential advertisers. So this might cause your account activation to delay from a couple of days to few weeks. If you have Nepali/Indian/Pakistani/Bangladeshi names in your username, the user activation will be quicker. SPAMMERS WILL BE DELETED WITH NO MERCY

How To Improve JAVA JDBC Performance?

How To Improve JAVA JDBC Performance?

Postby rumblefish » Fri Jul 17, 2009 3:00 pm





Source: Sharat.wordpress.com

* Use prepared statements. Use parametrized SQL.
* Tune the SQL to minimize the data returned (e.g. not ‘SELECT *’).
* Minimize transaction conflicts (e.g. locked rows).
* Use connection pooling.
* Try to combine queries and batch updates.
* Use stored procedures.
* Cache data to avoid repeated queries.
* Close resources (Connections, Statements, ResultSets) when finished with.
* Select the fastest JDBC driver.
* If you are not using stored procedures or triggers, turn off autocommit. All transaction levels operate faster with autocommit turned off, and doing this means you must code commits.
* Use connection pooling, either explicitly with your own implementation, or implicitly via a product that supports connection pooling.
* Use batch updates (sending multiple rows to the database in one call).
* Use the type-correct get() method, rather than getObject().
* Avoid n-way database joins: every join has a multiplicative effect on the amount of work the database has to do. The performance degradation may not be noticeable until large datasets are involved.
* Avoid bringing back thousands of rows of data: this can use a disproportionate amount of resources.
* Run an application server and any database servers on separate server machines.
* One of the most time-consuming procedures of a database application is establishing a connection to the database. Use connection pooling to minimize this overhead
* Use the same connection to execute multiple statements
* Avoid distributed transactions (transactions that span mutliple connections).
* Use scrollable ResultSet (JDBC 2.0).
* Oracle 9i includes queryable snapshots of the main database which can offload the query to run against the clients local snapshot.
* Performance should be considered at the start of a project
* Given a simple SQL statement and a stored procedure call that accomplishes the same task, the simple SQL statement will always execute faster because the stored procedure executes the same SQL statement but also has the overhead of the procedure call itself. On the other hand complex tasks requiring several SQL statements can be faster using stored procedures as fewer network trips and data transfers will be needed.
* Performance can be better if changes to the database are batched: turn off autocommit; add multiple SQL statements using the Statement.addBatch() method; execute Statement.executeBatch().
* The ConnectionPoolDataSource (from JDBC3.0) and PooledConnection interfaces provide built-in support for connection pools.
* Use Connection.setReadOnly(true) to optimize read-only database interactions.
* Use Connection.nativeSQL() to see how the SQL query will execute in the database to help ensure that the SQL is optimized.
* Use Connection.nativeSQL() to see how the SQL query will execute in the database to help ensure that the SQL is optimized.
* Commit the data after the transaction completes rather than after each method call
* Use the fastest driver available to the database: normally type 4 (preferably) or type 3.
* Use the Page-by-Page Iterator pattern to repeatedly pass small amounts of data rather than huge chunks.
* Reusing connections allows a prepared statement to be reused.
* Database connection pools can take one of two strategies: a limited size pool, where attempts to make connections beyond the pool size must wait for a connection to become idle; or a flexible sized pool with a preferred size which removes idle connections as soon as the preferred size is exceeded (i.e. temporarily able to exceed the preferred size). The fixed size pool is generally considered to be the better choice.
* Avoid the following common mistakes: Failure to close JDBC result sets, statements, and connections; Failure to remove unused stateful session beans; Failure to invalidate HttpSession.
* CachedRowSet provides cached result sets that do not require continuous connection to the database, allowing connections to be reused more efficiently.
* Reuse database connections using a connection pool.
* Obtain and release pooled conections within each method that requires the resource if the connection is very short (termed “Quick Catch-and-Release Strategy” in the article). However do not release the connection only to use it again almost immediately, instead hold the connection until it will not be immediately needed.
* Response time increasing too much when database is over populated probably indicates lack of or inappropriate indexing on the database.
* Use SQL clause with EXPLAIN or similar (e.g. “Explain select * from table where tablefield = somevalue”) to ensure that the database is doing an indexed search rather than a linear searches of large datasets.



Sanjaal.com is owned and maintained by Sanjaal Corps, Nepal. The company offers Webhosting and Domain Registration Services, IT Solutions and Business Analysis. Sanjaal.com website features H1B Visa Information, Entertainment Portal, Link Directory Service, Free Articles, Free Open Source Tutorials on Java and J2EE Platform, Digital Photography, High Resolution Picture Gallery and Free Reliable Image Hosting Services. Future plan includes Open Source Software Development Portal, Technical Solutions and Customizable Movie and Music Arena. We would be introducing data backup, data recovery, data hosting and voip solutions. Stay free from phishing - our website does not ask for your credit card and banking information. Happy Surfing!
rumblefish
Sanjaal Extremist
 
Posts: 1074
Joined: Sun Apr 12, 2009 10:21 pm


Similar topics


Return to Java Programming Discussions

Who is online

Users browsing this forum: No registered users and 1 guest

cron