Sunday, November 28, 2010

Improving the Performance of a JDBC Program

A java program that uses JDBC to connect to a database and retrieve the data from the database may take some time to perform these tasks. To know how much time has been taken by a JDBC program. we can take the help of currentTimeMillis() method of System class. This method gives the current  time in milliseconds since January 1st 1970.

By Reducing the execution time of a JDBC Program, we can improve its performance. The execution time of a JDBC program depends on the Following factors:

  1. The driver used to connect to the database will influence the execution time. Each driver will exhibit different performance and hence, selecting a good driver that gives optimum performance will improve the performance of a JDBC program.
  2. setFetchSize() method of statement interface is useful to tell the driver how many rows at a time to be fetched from the database. By increasing the number of rows to be retrieved at a time, it is possible to improve the performance of a JDBC program.
  3. Sometimes PreparedStatment inteface can be used in place of Statement interface for improving the performance.
Affect  of PerparedStatement:

Using the PreparedStatement in place of Statement Interface will improve the performance of a JDBC program.

When a SQL Statement is sent to database, the following tasks are performed:
  • The SQL Statement's Syntax should be verified to know whether it is correct or not. The SQL statement is divided into small pieces called "tokens". These tokens are also verified to know whether they are in SQL format or not.
  • Then another verification is done to know whether the table or view mentioned in the statement exists or not.
The above two statements are called "parsing" and takes some time. When the SQL Statement is executed, Statement interface does parsing every time the statement is executed.

On the Other hand if we use preparedStatement, it does parsing only once.
This saves time and hence PreparedStatement offers better Performance.

Vinay Guntaka

No comments:

Post a Comment

Java 1.7 New Features Over 1.6

Automatic Resource Management Description: A proposal to support scoping of resource usage in a block with automatic resource cleanup. T...