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

---

HSphere Control Panel Tips and Tricks - Power At Your Fingertips: Part 1

The first topic we are going to discuss... Read More

Protect Your Most Vital Business Asset with Security Software

Homeland security, airport security, Internet security â?" these days weâ??re... Read More

Beware of Spyware

One day, you suddenly realize that your computer started to... Read More

ERP System of the Future: Database, Business Logic and Interface

We will base our prognosis on our Microsoft Business Solutions... Read More

New Web-Based HR Tool is Max From NAS, Hannibal, and InfoLink

Three highly respected names in Human Resources have joined forces... Read More

Microsoft CRM: Implementation, Customization, Support ? Consultant Overview

Microsoft Business Solutions CRM is present several years on the... Read More

Introducing GRML

Creating a new markup language.Introduction.General Reuse Markup Langauge, or GRML,... Read More

Microsoft Great Plains Implementation ? Overview for IT Director/Controller

What is installation in the language of technology? Installation... Read More

Introduction To ISDN, Part II

In the previous ISDN article, we looked at how and... Read More

Tripwire for Linux File Integrity

What is Tripwire?Tripwire is a form intrusion detection system (IDS)... Read More

Demand More From Your Lead Tracking Software

An integral part of any quality CRM system is lead... Read More

How to Make Own CMS

Every day millions of new web documents emerge on the... Read More

Microsoft Business Solutions - Navision Customization: C/SIDE, C/ODBC, C/FRONT, XBRL

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

Microsoft Small Business Manager Customization Options - Overview

Microsoft Business Solutions Small Business Manager is Great Plains Dexterity... Read More

Adware and Spyware

What is 'adware'?Adware is basically software or scripts that are... Read More

Microsoft CRM Data Conversion FAQ

Microsoft Business Solutions CRM data conversion deserves FAQ type of... Read More

Make or Break Factors - When Considering Estimating Software

Make-or-Break Factors in Success and ProfitabilityFor quick printers, estimating can... Read More

RSS: Get Notified When Your Favorite Websites Are Updated

RSS (Really Simple Syndication) is a way for a site... Read More

Reduce Pop-ups and Annoying Ads

There is many things more frustrating than surfing a website... Read More

The Truth: Netzero 3G

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

About Microsoft Sysprep (System Preparation) Utility (Windows 2000)

The Microsoft system preparation utility for Windows 2000 is a... Read More

The Importance of Timely Timesheets

Whether you are a small consultancy firm, a medium sized... Read More

Microsoft Navision Customization Upgrade ? Tips For Programmer/IT Specialist

Currently Microsoft Business Solutions is on the way of creating... Read More

Causes of ERP Failures

ERP is the acronym of Enterprise Resource Planning. Multi-module ERP... Read More

My Experience - Making a Vision into Reality

Disclaimer: All the thoughts expressed are my views only! Your... Read More

Spyware - The Internet Devil Of Our Times!

Spyware and Adware infest over 90 percent of computers in... Read More

What Stands Behind The Software Description?

With thousands of web pages added to the Net every... Read More

Should I Use Windows Update?

Should one use Windows Update?This topic has good and valid... Read More

Microsoft Great Plains Implementation: Verticals - Wholesale Order Entry center ? overview

Microsoft Business Solutions Great Plains has substantial market share and... Read More

The Tools and Features of Adobe Photoshop

Adobe Photoshop is one of the world's leading graphics editing... Read More

Unofficial Windows 98 SE Service Pack 2.0 RC2

Microsoft has never released a service pack for Windows98 SE,... Read More

Microsoft Great Plains Installation ? Overview for IT Director/Controller

Microsoft Great Plains is main mid-market application from Microsoft Business... Read More

How to Make Attractive and Effective PowerPoint Presentations

Microsoft PowerPoint has dramatically changed the way in which academic... Read More