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

---

Huddle Up; Groupware on Three

It could just be me, but my experiences with document... Read More

Microsoft Great Plains Inventory Control ? Overview For Consultant

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

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

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

Microsoft Great Plains: Government & Non-Profit Organization ? Workflow Implementation

Usually workflow & messaging is realized in CRM and then... Read More

Microsoft Great Plains version 8.5: Upgrade, Customization, VBA, Crystal Reports - Highlights

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

Groupware and Online Collaboration: Collaboration Series #4

This article is the fourth of a series of articles... Read More

Secure File Transfer Using SSH Plus Additional Audit & Automation - FSA Reporting

In order to meet regulatory and corporate compliance requirements reporting... Read More

String in Java

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

Programming Environments And The Software Production Process

Introduction: The creating of a computer program involves a number... Read More

Benefits of Shareware

Shareware has been fighting the stigma of being misunderstood for... 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

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

Inherent Dangers Of File Sharing Via The Internet.

Cyberspace has opened up a new frontier with exciting possibilities... Read More

The Hidden Power of Online Manual

Writing software manuals is boring, isn't it? We often think:... Read More

Demand More From Your Lead Tracking Software

An integral part of any quality CRM system is lead... Read More

Tools for Customizing Great Plains

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

Fundraising Software ? How Can That Help Me?

Fundraising software lets you connect with donors in a way... Read More

Four Easy Ways To Prevent Spyware

How would you like to prevent spyware and adware from... Read More

Microsoft CRM Data Import FAQ

Microsoft CRM has built-in conversion tool, however you should probably... 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 Great Plains Implementation: Verticals - Wholesale Order Entry center ? overview

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

Microsoft Great Plains Integrations - Tips for Developer

In this short FAQ style article we would like to... Read More

Microsoft CRM Integration & Customization: SharePoint Document Gateway

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

Tripwire for Linux File Integrity

What is Tripwire?Tripwire is a form intrusion detection system (IDS)... Read More

Photoshop Files and Formats

People often ask me: What image file formats will Photoshop... Read More

Microsoft CRM Development

Microsoft CRM is CRM answer from Microsoft Business Solutions.The whole... Read More

Microsoft Great Plains: Customization Upgrade & Recovery ? Visual Studio VB 6.0

Microsoft Great Plains, former Great Plains Software Dynamics, eEnterprise has... Read More

Microsoft Great Plains & CRM in Transportation & Logistics ? Overview

Microsoft Business Solutions Great Plains and MS CRM (client relation... Read More

Spyware Protection: A Must for Any Home Computer

Now that spyware is the single most dangerous threat to... Read More

How To Develop Software For Your Business

Software development is a risky business.Many software developers are barely... Read More

Great Plains Dexterity: Customizations & Source Code Programming

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

Resume Software ? Advantages Revealed

The various resume software offered, particularly on the internet, can... Read More

Software Piracy

SOFTWARE PIRACY We regularly hear reports... Read More