Newsletter sign-up
View all newsletters

Enterprise Java Newsletter
Stay up to date on the latest tutorials and Java community news posted on JavaWorld

Sponsored Links

Optimize with a SATA RAID Storage Solution
Range of capacities as low as $1250 per TB. Ideal if you currently rely on servers/disks/JBODs

JavaWorld.com > Java Enterprise Edition >

Server-side Java: Use JDBC for industrial-strength performance, Part 2

Follow these Java server-side data -mining patterns with multiple ResultSets for maximum performance

By Lennart Jorelid, JavaWorld.com, 02/21/00

  • Print
  • Feedback
.

Page 4 of 4

Following is the skeleton code for the CallableStatement multiple ResultSet scenario: 

import java.sql.*;

public class StoredProcedureMultipleResultsets extends MultipleResultsets
{
  public static void main(String[] args) throws Exception
  {
      Connection conn = setupConnection();

      // Create a SQL query calling a stored procedure returning multiple ResultSets
      String sql = "{call reverseSelectionProcedure()}";

      CallableStatement stmnt = conn.prepareCall(sql);

      processStatement(stmnt);

      // Close all database resources
      stmnt.close();
      conn.close();
  }
}


When run, the StoredProcedureMultipleResultsets produce the following result: 

Time: 1999-12-10 12:44:44.445
Got ResultSet[1]: 1 columns.
Got ResultSet[2]: 2 columns.


Some performance and JDBC driver hints

A good JDBC driver implementation is the key to obtaining good Java/database communication performance. Depending on how well the database and the JDBC driver uses caching and how useful that caching is for stored procedures, performance test results can become difficult to interpret. It is easier to use cache effectively for less difficult questions; I would therefore recommend against using stored procedures as simple as the reverseSelectionProcedure above. If your selection SQL statement string is as simple as the one executed by the reverseSelectionProcedure, you could often reach better performance with a PreparedStatement.

Be aware that some JDBC drivers need thorough coercing before returning multiple ResultSets to you. In some cases, the drivers silently ignore the second ResultSet of the statement. Therefore, you need to test a JDBC driver before using it in industrial-strength systems.

A simple example: When I tried out three different drivers for a database when extracting 10,000 lines that were roughly 80 characters in length apiece, I received running times ranging from 361 to 520 milliseconds in the multiple SQL queries scenario, and 500 milliseconds in the CallableStatement multiple ResultSet scenario when communicating with a database that had no cache. When running the queries a couple of times in sequence to allow the database/JDBC driver to build up its cache, the running times dropped to 10 to 401 milliseconds in the Multiple SQL queries scenario and 441 milliseconds in the CallableStatement multiple ResultSet scenario. The state and type of the drivers varied, and, in one case, my compiler complained about dead or unreachable code in the compiled driver classes.

Conclusion

Reducing network or interprocess calls to extract data from a database boosts speed in the application system. Using transactions or batch handling (provided in the JDBC 2.0 API) improves performance and provides isolation for the database user. As a complement, extracting multiple ResultSets using the multiple SQL queries scenario or the CallableStatement multiple ResultSet scenario could reduce database access time even more. Industrial-strength server-side applications should use this capability if possible.

The authors of this month's server-side Java computing articles will be holding a free online seminar on February 10 at 10:00 a.m. Pacific Standard Time. Register to join at http://seminars.jguru.com.

About the author

Lennart Jorelid is a server-side Java and ecommerce content expert working for jGuru Europe. With working experience that spans projects in the USA, Canada, UK, Switzerland, Sweden, and Germany, Lennart is a recognized expert, architect, and educator in the Java technology community. Based in G�teborg, Sweden, he is currently writing a book on server-side Java patterns. Lennart is a big fan of skiing, acting, and sci-fi. JavaWorld and jGuru have formed a partnership to help the community better understand server-side Java technology. Together, JavaWorld and jGuru are jointly producing articles, free educational Web events, and working together on the JavaWorld bookstore and Web-based training.

Read more about Enterprise Java in JavaWorld's Enterprise Java section.

  • Print
  • Feedback