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

---

Information Products: A Business Owners Best Friend

We live in a post-industrial age where information is the... Read More

Microsoft RMS ? Great Plains Integration ? Overview For IT Specialist

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

Microsoft Great Plains - Payroll & HR Inexpensive Solution? Not Any More

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

Microsoft Great Plains Reporting ? Overview for Developer

Looks like Microsoft Great Plains becomes more and more... Read More

MicroWorld Releases New Version of MailScan Ver. 4.5 - Antivirus and Content Security Software

MicroWorld Technologies, Inc. the leading solutions provider in the area... Read More

String in Java

Handling character strings in Java is supported through two final... Read More

Ukraine IT Myths Dispersed

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

OEComplete ? A Personal Information Manager

OEComplete is a utility for managing the personal information of... Read More

Making The Decision To Automate Your Software Testing

Not every software testing project can or should be automated.... Read More

Business Plan Software Review: Ultimate Business Planner?

One of the main reasons business owners and entrepreneurs use... Read More

Microsoft Business Solutions Customization Options - Overview for Programmer

Several years ago Microsoft purchased Great Plains Software, then Navision... Read More

Business Planning Software

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

Great Plains Dexterity Programming ? Overview For Developer

Looks like Microsoft Great Plains becomes more and more popular,... Read More

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

Traditionally we were considering functionally rich systems, such as SAP,... 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

Microsoft CRM Programming Secrets ? Tips For Developers

This article is for advanced Microsoft CRM SDK C# developers.... Read More

Microsoft CRM Integration with IBM Lotus Notes Domino ? Machinery Dealership Example

IBM Lotus Notes with Domino email server is traditional document... 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

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

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

Database Guru James F. Koopmann Reviews DBxtra Reporting and Query Tool

DBxtra is a powerful query and reporting tool that hides... Read More

A Simple Guide To Wikis

A wiki is an editable text-based website. But you don't... Read More

Healthcare Preventive Maintenance Software

Healthcare facilities such as clinics, hospitals, and biomedical laboratories can... Read More

Microsoft Great Plains Multicurrency ? Overview For Implementation Consultant

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

Computer Phones ? Facts and Fallacies

The stakes are high when considering security, privacy, and savings,... Read More

Free Software - Powerful Alternatives to Budget-Busting Software

When you buy a computer, it most likely comes with... Read More

Helping Newbies Understand Professional Software

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

Microsoft CRM and No-Frills Cadillacs

In today's business world it's all but impossible to escape... Read More

Great Plains DOS Support ? Notes for Consultant

Great Plains Accounting, accounting package for mid-size and small companies... Read More

Tools for Customizing Great Plains

Microsoft Business Solutions ? Great Plains has captured the US... Read More

Manufacturing Outsourcing: Microsoft Great Plains Implementation, Customization & Reporting

Manufacturing in the USA is far away down from mid... Read More

Troubleshoot Windows with Task Manager

Task Manager is a Windows system utility that displays thetasks... Read More

Introducing GRML

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

ERP Consulting: Microsoft Great Plains Partner Future Directions

In the Clinton era the status quo was simple: you... Read More