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

---

How to Evaluate Staffing Software

If you are in the market for new staffing software,... Read More

Guide to Software Marketing

Intro This concise article will tell you in plain English... Read More

Pros and Cons of Using FREE Software in Your Business

Itâ??s easy to understand why you might be drawn to... Read More

Should I Use Windows Update?

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

Microsoft CRM Data Conversion FAQ

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

S is for Spying, Surveillance -- and for Software as Well

Words we choose to describe things and phenomena often show... Read More

Run Your Own Search Engine

Our hosted solution allow you to run your own search... Read More

Microsoft Great Plains Customization: Project Organization ? International Business Example

Microsoft Business Solutions Great Plains fits to majority of horizontal... Read More

Microsoft Great Plains - Microsoft RMS Integration ? overview

Microsoft Great Plains and Microsoft Retail Management System (Microsoft RMS)... Read More

Oracle Development: JDeveloper 10G ? Java, J2EE, EJB, MVC, XML - Overview For Programmer

In 2004 Oracle, Inc. made its new step toward J2EE... Read More

ERP for Large Corporation ? New Selling Strategy

Our opinion is based on our Microsoft Business Solutions Great... Read More

Microsoft Business Solutions Partner ? How to Launch New IT Consulting Practice

In the new era of internet marketing the problem of... Read More

It?s Back!! WordPerfect?s Amazing Comeback

Have you noticed WordPerfect is gearing up for a comeback... Read More

Bridging the Gap between Paper and Data

The cornerstone of successful automated office systems is the ability... Read More

Software Piracy - Global Increase

Pirated software is on the increase and now accounts for... Read More

Make 2005 the Year You Save Time!

Today's business world is fast-paced. No matter what it is... Read More

What is Groupware?

Vince Lombardi once said that, "The achievements of an organization... Read More

Windows Screensavers Explained

In this article you will find some background information about... Read More

Popular Fleet Maintenance Programs

Some companies that are in need of fleet management may... Read More

How a Bug Challenges to a Software Professional

It is really interesting that a bug can create problem... Read More

Groupware: Answers the 5 Questions of Document Collaboration

Every organization which creates collaborative documents, whether they are budgets,... Read More

Microsoft Business Solutions VAR/Partner Selection ? Overview for IT Director/Manager/Controller

Microsoft Great Plains and Microsoft CRM become more and more... Read More

History of Java

The java programming language is becoming more and more popular... Read More

Understanding Document Management

The term "document management" and "paperless office" is the subject... Read More

OSI Layers Model

IntroductionDuring the early years of our modern computer era, very... Read More

An Easy Way to Develop JAVA Enterprise Applications

Research bears that less than 70 percent of development projects... Read More

Microsoft Great Plains: Interest Calculation Example ? Stored Procedure for Crystal Report

This is intermediate level SQL scripting article for DB Administrator,... Read More

Reporting for Microsoft Great Plains/Dynamics/eEnterprise: RW ? ReportWriter ? Tips for Developer

Microsoft Business Solutions Great Plains is written in Great Plains... Read More

Create A Flash Presentation For Free With Open Office

The intentions of this short tutorial are not to teach... Read More

Microsoft Great Plains SOP: Sales Order Processing

Microsoft Business Solutions Great Plains is marketed for mid-size companies... Read More

Microsoft Small Business Manager eCommerce ? Overview

Microsoft Business Solutions Small Business Manager is scaled down Great... Read More

Adware and Spyware Blockers

The most important things you can do for your computer... Read More

Microsoft Great Plains Upgrade ? Version 8.0 Overview for IT Director/Controller

If you have Microsoft Great Plains as main accounting and... Read More