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

---

Microsoft Great Plains Multicurrency ? Overview For Implementation Consultant

When you first think about multicurrency ? you probably have... Read More

Spyware - The Internet Devil Of Our Times!

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

How to Evaluate Staffing Software

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

Great Plains Dynamics on Pervasive/Ctree support ? overview for consultant

All of us know that Microsoft bought former Great Plains... Read More

Quick Summary of Basic and Common Linux Commands

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

OLAP, An Alternative Technology Over Spreadsheets

Are Spreadsheets Robbing your Enterprise of Competitive Advantage?'90% of "average"... Read More

Can Group Collaboration Software Meet Business Needs?

According to a survey conducted by InfoTrends/CAP Ventures entitled "Content-Centric... Read More

Running a Program on a Remote Server Using SSH

How do you run a program on a remote server... Read More

Microsoft Great Plains in Construction & Building ? Implementation & Customization Highlights

Microsoft Great Plains could be tuned and setup to fit... Read More

Razzle Dazzle Them

Once upon a time not so long ago, there was... Read More

Navision Attain Database access via C/ODBC in ASP.NET Application

Navision Software was purchased by Microsoft and now it is... Read More

Microsoft Great Plains Beverage Production & Distribution ? Implementation & Customization Highlight

Microsoft Business Solutions Great Plains has many years of successful... Read More

Crystal Reports For Microsoft Navision - Overview For Programmer/IT Specialist

Let us give you - developer some hints in the... Read More

Integrating Microsoft Great Plains Accounting/ERP: RMS, CRM, eCommerce, Lotus Domino ? overview

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

Vlans

In order to implement VLANs in a network environment, you'll... Read More

The Software 2005 Conference - A Review

The Software 2005 conference is now a wrap. This conference,... Read More

Passwords Used In Microsoft Word Documents

You would like to protect your documents, wouldn't you? Reasons... Read More

Exactly What Does FTP Stand for and What Can it do for Me?

FTP stands for "file transfer protocol". FTP is basically a... Read More

Great Plains Custom Development: Dexterity, VBA, SQL, Crystal, eConnect ? Overview For Programmer

Microsoft Great Plains is main Microsoft Business Solutions accounting package... Read More

RFID: Strengthen the Position for SAP; United States

SAP Inc., a global leader in client/server enterprise application software... Read More

Windows Screensavers Explained

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

Microsoft Great Plains Oil & Gas ? Implementation & Customization Highlights

Microsoft Great Plains serves the wide spectrum of horizontal markets.... Read More

Best Spyware Removers

Finding the best spyware removers to detect and remove spyware... Read More

Business Planning Software

Once a business idea is selected, it is highly recommended... Read More

Microsoft CRM Data Conversion FAQ

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

Microsoft Moves to Small Business Accounting/Retail Market ? Stakes and Thoughts

In this small article we will be looking at the... 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 Nationwide Remote Support

ERP Consulting industry is on the way to serve clients... Read More

IBM Lotus Domino or Microsoft Exchange?

IBM Lotus Domino or Microsoft Exchange?The severe competition continues for... Read More

Screenplay and Script Writing Software

When it comes to screenplay software each screenwriter needs to... Read More

Microsoft CRM Integration & Customization: SharePoint Document Gateway

MS CRM is very close to document workflow automation, including... Read More

Cisco CCNA Certification: Becoming A Truly Valuable CCNA.

I've been active in the Cisco Certification track for four... Read More

Great Plains Customization Upgrade? Overview For CIO/IT Director

Around the same time Microsoft made its move with .Net... Read More