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

---

Selecting Microsoft Great Plains Partner/VAR/Reseller: ERP Implementation & Customization ? Overview

In the case when you represent mid-size or mid-size-to-large business,... 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

Microsoft C# vs. VB.Net

Hi, Guys,I believe a lot of programmers are trying to... Read More

How to Build a Basic CSS Layout

Designing without tables by using CSS layouts is fast becoming... Read More

netstat - Linux Command to Display Networking Information

In linux, one of great commands for finding out information... Read More

Corporate Accounting System: Microsoft Great Plains ? Overview

Corporate ERP/MRP selection might be tough one, especially considering very... Read More

Perfectly New Database Query Tool - Foxy SQL Pro Released

Are you a database professional? Do you work with a... Read More

A Case Study on Selecting Contract Management Software

Professional services firm cuts costs and improves productivity with integrated... Read More

International Support - Microsoft Great Plains VAR/Partner Selection ? Overview for V.P. IT

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

Software Piracy

SOFTWARE PIRACY We regularly hear reports... Read More

Microsoft Great Plains: Dexterity vs. eConnect ? FAQ

Microsoft Business Solutions Great Plains, former Great Plains Software Dynamics/eEnterprise... Read More

C++ Function Templates

C++ Function templates are those functions which can handle different... Read More

Basic Tips and tricks for Windows XP

Running Applications in Compatibility Mode With Windows XP, you can... Read More

Preventive Maintenance Software Companies

Several software companies design programs for preventive maintenance. Most of... Read More

Free Preventive Maintenance Software

While several preventive maintenance software manufacturers offer free trials for... Read More

History of Java

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

Unwanted Files

A LOT OF UNWANTED FILES.When you uninstall an item of... Read More

Microsoft CRM ? Consulting in the Post-Recession Time

New post-recession era has new features, which didn't exist in... Read More

Ukraine IT Myths Dispersed

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

Does your Company have Documentum?

Are you lost in the mess of documents that get... Read More

Will Adobe Manage to Replace Industry Work Horse Quark Express by Giving Adobe InDesign for Free?

Heard about the Quark "killer"?Adobe InDesign CS2. Will it really... Read More

Hear It in Digits

Music downloads are off the charts! We're listening to digital... 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

The Bluebird Project

The objective for Zandi Digital is to make available clever... Read More

The Dreaded Paper Label - Should it be Used?

While paper labeling CDs and DVDs may appear to be... Read More

Microsoft Great Plains Jewelry ? Implementation & Customization Overview

Each Industry and market niche has business specific and unique... Read More

Great Plains Customization ? Programming Auto-apply in Accounts Receivable

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

The True Meaning of Freeware

The vast majority of us will have, at some point,... Read More

Beware of The Pirated Software E-Mail Scams!

You have gotten those E_Mails buy software at deep discounts.... Read More

Ukraine: a Prospective Player on the World Software Outsourcing Market

The destruction of the Soviet Union about 15 years ago,... 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

Fleet Maintenance Software Reviews

Innovative Maintenance Systems (IMS) is one company that offers solutions... Read More

Cisco CCNA Certification: Becoming A Truly Valuable CCNA.

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