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 CRM Customization ? Programming Closed Email Activity

Microsoft CRM is CRM answer from Microsoft and attempt to... Read More

SQL scripts for Project Accounting: Microsoft Great Plains series ? overview for developer

Microsoft Business Solutions Great Plains has Project Accounting module where... Read More

Two Reasons to Use Timesheet Software

This short paper will expand on two key reasons to... Read More

SSH (SCP) Send Files from Windows to Your Linux Box

Not all of us have the luxury of working both... Read More

Microsoft CRM for Large Corporation ? Security

Microsoft Business Solutions CRM proved to be reliable solution in... Read More

Understanding Document Management

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

Microsoft Great Plains Subcontracting ? Overview for Microsoft Business Solutions Partner

Microsoft Business Solutions Great Plains is very popular ERP/MRP applications... Read More

Selecting Corporate ERP: Microsoft Business Solutions Great Plains ? Estimation Parameters

As we could imagine, if you are reading this article... Read More

Groove Network. Are you in it?

If you are in a business that passes documents around... Read More

Great Plains Customization ? Programming Auto-apply in Accounts Receivable

Microsoft Great Plains is one of three Microsoft Business Solutions... Read More

10 Ways to Learn a Software

Following tips help you to learn a software in lesser... Read More

Great Plains Accounting Migration to Microsoft Great Plains - Overview for IT Specialist

This is a short article, written in question/answer/FAQ style to... Read More

RSS: Get Notified When Your Favorite Websites Are Updated

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

ERP Software Financing: the Future? ? Overview for Company Owner

In our case ? we serve Microsoft Business Solutions ERP... Read More

Software Piracy - Global Increase

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

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

Microsoft Business Solutions Great Plains has many years of successful... 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

Performance Tuning of a Daffodil DB / One$DB -JDBC Application

This article illustrates the best practices to improve the performance... Read More

Microsoft Great Plains Oil & Gas ? Implementation & Customization Highlights

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

Microsoft Small Business Manager Customization Options - Overview

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

Device Driver Basics

Most people understand that the "hardware" part of their computer... Read More

Great Plains Dexterity: Customizations & Source Code Programming

Great Plains Software Dynamics, Dynamics C/S+, eEnterprise were written on... Read More

Review on QuarkXpress 6.0

After almost two decades of existence, Quark has become the... Read More

Spyware Statistics -- Whats New in May 2005?

Although statistics often is blamed for various deadly sins --... Read More

The Software 2005 Conference - A Review

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

Dig Out That Worm

Internet worms. Is your PC infected?If your computer has become... Read More

Corporate ERP: Standard vs. Rich functionality ? Microsoft Great Plains

Traditionally we were considering functionally rich systems, such as SAP,... Read More

Interactive Mapping Brings Information to Life

What is Interactive Mapping?Interactive mapping is a visual display medium... Read More

Microsoft CRM Lotus Notes Domino Connector FAQ

Microsoft Business Solutions CRM and IBM Lotus Notes Domino, being... Read More

Microsoft eCommerce Web-development: Great Plains eConnect .Net ? Highlights for Programmer

In our small article we'll consider Microsoft Business Solutions Great... Read More

Software Automation Helps Increase your Bottom Line

When you own a small business, time is money. And... Read More

How to Choose the Right Accounting Software for Your Business

With any good luck and a good amount of hard... 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