Performance Tuning of a Narcissus DB / One $ DB-JDBC application

This article illustrates the best practices to improve the performance of Daffodil DB / One $ DB JDBC Driver. This article focuses on improving the performance of a Daffodil DB / One $ DB JDBC application using the Statement, PreparedStatemnt, CallableStatement, and ResultSet interfaces. Choosing the right statement interfaces and methods according to your SQL query plays a critical role in improving the performance of a JDBC driver.

JDBC overview

JDBC API provides standard set of interfaces to work with databases such as Daffodil DB / One $ DB, Oracle, Derby etc.

The connection interface includes the functionality of the database connection, the instruction interface includes the display and execution functionality of SQL statements, while the ResultSet interface includes the retrieval of data from the execution of an SQL query using instruction.

Following are the basic steps to write a JDBC program.

1. Import “java.sql” and “javax.sql” packages. (Import “javax.sql”, if advanced JDBC function like XA is to be used)

2. Load Daffodil DB JDBC driver (embedded or network JDBC driver)

3. Connect to the database through the connection interface

4. Make a statement

5. Make the statement

6. Get results using the ResultSet interface

7. Close statement and connection

Choosing the right Statement interface:

There are three types of instruction interfaces in JDBC for displaying / executing an SQL query statement, PreparedStatement and CallableStatement. Statement is used to execute static SQL statements without input and output parameters; PreparedStatement is used to execute dynamic SQL statements with input parameters, while CallableStatement is used to execute dynamic SQL with both input and output parameters. An important thing to note about PreparedStatement and CallableStatement is that they can also be used for static SQL statements. However, Callable Status is primarily for stored procedures.

Prepared Status gives better performance compared to Statement because it is pre-parsed and pre-assembled. This means that compiling and parsing such a statement is done through the database only once. The database then reuses the already parsed and compiled statement. This greatly improves performance, because when a statement has to be executed repeatedly it does not have to be parsed and compiled again and again. Thus, the overload by parsing and compiling the same instruction can be reduced.

When one request is required to process multiple complex statements, Callable Status provides better performance compared to Prepared Status and Statement.

To read the full article, go to [http://www.daffodildb.com/daffodildb-performance-tuning.html]



Source by Parveen Aggarwal