Overall performance Tuning of a Daffodil DB / One$DB -JDBC Application

This article illustrates the top practices to enhance the overall performance of Daffodil DB / One$DB JDBC Driver. This article focuses on tips on how to boost the functionality of a Daffodil DB / One$DB JDBC application employing Statement, PreparedStatemnt, CallableStatement and ResultSet interfaces. Selecting the right statement interfaces and appropriate procedures based on your SQL query plays a important part in enhancing the performance of a JDBC Driver.

Image result for alooma pic

JDBC Overview

JDBC API delivers standard set of interfaces to perform with databases like Daffodil DB / One$DB, Oracle, Derby etc.

Connection interface encapsulates database connection functionality, Statement interface encapsulates SQL statement representation and execution functionality whereas ResultSet interface encapsulates retrieving data which comes in the execution of a SQL query utilizing Statement.

Following will be the fundamental measures to write a JDBC program.

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

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

3. Establish connection to database working with Connection interface

4. Create a Statement

5. Execute the Statement

6. Retrieve results by using ResultSet interface

7. Close Statement and Connection

Picking out correct Statement interface:

You can find three types of Statement interfaces in JDBC to represent/execute a SQL query-Statement, PreparedStatement and CallableStatement. Statement is employed for executing static SQL statement with no input and output parameters; PreparedStatement is applied to execute dynamic SQL statement with input parameters whereas CallableStatement is applied to execute dynamic SQL with each input and output parameters. One particular essential factor to note about PreparedStatement and CallableStatement is the fact that they’re able to also be applied for static SQL statements. Having said that, CallableStatement is mostly meant for stored procedures.

PreparedStatement gives much better overall performance when compared to Statement because it truly is pre-parsed and pre-compiled. This means that compilation and parsing of such statement is done only once by the database. Afterwards the database reuses the currently parsed and compiled statement. This drastically improves the efficiency due to the fact anytime a statement must be executed repeatedly, it doesn’t need to be parsed and compiled time and again. So the overload incurred by parsing and compiling exactly the same statement is often decreased.

When there is a requirement for single request to procedure several complex statements, CallableStatement gives much better overall performance as in comparison with PreparedStatement and Statement. click this link: Redshift JDBC Driver.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s