Performance Tuning of a Daffodil 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 how to improve the performance of a Daffodil DB / One$DB JDBC application using Statement, PreparedStatemnt, CallableStatement and ResultSet interfaces. Choosing the right statement interfaces and right methods according to your SQL query plays a vital role in improving the performance of a JDBC Driver.

JDBC Overview

JDBC API provides standard set of interfaces to work 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 from the execution of a SQL query using Statement.

Following are the basic steps to write a JDBC program.

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

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

3. Establish connection to database using Connection interface

4. Create a Statement

5. Execute the Statement

6. Retrieve results by using ResultSet interface

7. Close Statement and Connection

Choosing right Statement interface:

There are three types of Statement interfaces in JDBC to represent/execute a SQL query-Statement, PreparedStatement and CallableStatement. Statement is used for executing static SQL statement with no input and output parameters; PreparedStatement is used to execute dynamic SQL statement with input parameters whereas CallableStatement is used to execute dynamic SQL with both input and output parameters. One important thing to note about PreparedStatement and CallableStatement is that they can also be used for static SQL statements. However, CallableStatement is mainly meant for stored procedures.

PreparedStatement gives better performance when compared to Statement because it 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 already parsed and compiled statement. This significantly improves the performance because whenever a statement has to be executed repeatedly, it doesn't need to be parsed and compiled time and again. So the overload incurred by parsing and compiling the same statement can be reduced.

When there is a requirement for single request to process multiple complex statements, CallableStatement gives better performance as compared to PreparedStatement and Statement.

To read the full article please visit http://www.daffodildb.com/daffodildb-performance-tuning.html

This article has been contributed by (Mr.) Parveen Aggarwal, Technical Consultant to DSL India (http://www.daffodildb.com). With more than 6 years of industry experience in Java and allied technologies, he has an in-depth understanding of J2EE, J2ME and database management systems. Parveen is currently working on the concept of data-archiving in embedded databases. He can be contacted at parveenaggarwal@hotmail.com

---

Great Plains Dexterity ? Microsoft Great Plains Customization Overview

Microsoft Business Solutions Great Plains, former Great Plains Software Dynamics... Read More

Software Review: Xsitepro Total Site Management Software

It's not very often I get excited about a software... Read More

Helping Newbies Understand Professional Software

The Windows registry is a huge database that ensures normal... Read More

Recovering Microsoft Great Plains Customization ? Tips for IT Director

Remember nice and prosperous Clinton era? When you implemented innovative... Read More

How To Develop Software For Your Business

Software development is a risky business.Many software developers are barely... Read More

Free Microsoft Word Online Training Tutorial Resources

Microsoft Word is one of the most popular office applications... Read More

IT Strategy for Large Corporation: ERP/MRP/CRM, Unix/Linux/Windows, Microsoft/Java

Combining Microsoft Business Solutions Great Plains ERP with non-Microsoft Business... Read More

Internet Security Threats: Who Can Read Your Email?

Before being able to choose a secure Internet communication system,... Read More

Editing Your Photos Using Microsoft Picture It Publishing Platinum 2002 - A Great Dinosaur

I started using PIP (Picture It Publishing) Platinum 2002 right... Read More

Linux Dual Boot-How To Format

Just the thought of a duel-boot scares many people away,... Read More

Open Source Concepts: Dual Licensing Explained

We were recently faced with a decision: either to let... Read More

Navision Sales Module & Reporting: Jet Reports, C/ODBC, XBRL, Business Analytics ? highlights

Microsoft bought Navision, Denmark based software development company, along with... Read More

Managing Stress in the Computer Industry - Five Steps to a Stress-free Life

It would be easy to think, like most people apparently... Read More

Keeping Track

One of the things we can be as certain of... Read More

Examining the Substance of Studio MX

To all web designers out there, this article is for... Read More

Mozilla Vs. Firefox

For those who are unclear on the differences between the... Read More

Configure Windows Indexing Service for Performance

The Windows Indexing Service provides you with the ability to... Read More

Internet Relay Chat - A Basic Introduction

What is IRC?IRC is Internet Relay Chat. It is a... Read More

Microsoft Great Plains Security Setup - Overview For Consultant

Microsoft Business Solutions Great Plains is very good fit for... Read More

Most Dangerous Types of Spyware are on the Rise: How to Choose the Weapon

Bad News - the Threat is Bigger than it SeemedHow... Read More

Behave, Word, Behave!

If you copy something from a Web site or elsewhere...... Read More

Software Piracy

SOFTWARE PIRACY We regularly hear reports... Read More

The Truth: Netzero 3G

We've all seen the ads on TV for Netzero 3G.... Read More

Free Software for Newbies and Web Developers

Here is some free software tools to help you build... Read More

Microsoft Great Plains POP: Purchase Order Processing ? Overview For Consultants

Great Plains Purchase Order Processing (POP) module makes up one-third... Read More

Microsoft CRM Implementation for Large Corporation ? overview

Microsoft Business Solutions CRM is now approaching the phase of... Read More

How Do I Get Rid Of That Darn Spyware?

Ad-Aware and Spybot are probably the two most well known... Read More

Algebra Help Software

Need help making sense of algebra? Have algebra lectures in... Read More

Ukraine IT Myths Dispersed

While Ukraine is becoming a new popular IT outsourcing destination,... Read More

Microsoft Great Plains: Data Conversion & Migration Scenarios ? Overview for Consultant

Microsoft Business Solutions Great Plains serves multiple industries in the... Read More

Microsoft Great Plains customization ? Freight Forwarding/Transportation industry example

Microsoft Business Solutions Great Plains version 8.5, 8.0, 7.5, Great... Read More

Quick Summary of Basic and Common Linux Commands

There are many commands that are used in linux on... Read More

Linux vs Windows Operating Systems

With so many Microsoft Windows related viruses, errors, and other... Read More