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

---

Business Planning Software

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

Device Driver Basics

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

Microsoft CRM: Data Conversion ? Import from Act!

Best Software Act! is very popular CRM for small and... Read More

Best Spyware Removers

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

The Opera Alternative

Security flaws have long plagued Internet Explorer (IE), the market-dominating... Read More

Dont Choose Adobe When Working With PDF

While Adobe is the most known maker of PDF tools,... Read More

Huddle Up; Groupware on Three

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

Looking for an Alternative to Microsoft Office? You Should Be!

Now is the time to look at an alternative to... Read More

The Secret of the Layer Styles Dialogue

When you double-click a layer in the Layer Palette, you... Read More

Microsoft Great Plains: Dexterity vs. eConnect ? FAQ

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

What You Should Know About Installing Screensavers

Do you remember that frustrating feeling when you find an... Read More

A Case Study on Selecting Contract Management Software

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

Navision Customization: C/SIDE, C/ODBC, C/FRONT, XBRL ? Development Options

Microsoft bought Navision, Denmark based software development company, along with... Read More

Three Steps To Windows Safety Heaven

Now there are Three Steps To Heaven Just listen and... Read More

Keeping Track

One of the things we can be as certain of... Read More

How a Bug Challenges to a Software Professional

It is really interesting that a bug can create problem... Read More

C++ Tutorials: 3, Program Flow (If, Else, While, For)

Program Flow is what you think it is. How the... Read More

Microsoft Great Plains: Service Business Customization & Integration Example

Microsoft Business Solutions Great Plains might be considered as ERP... Read More

Software: What Suits Me?

Almost all new and major brand of PCs come with... Read More

Software Automation Helps Increase your Bottom Line

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

Microsoft CRM Conversation Gateway: VoIP - Implementation & Customization

Microsoft CRM is winning market share step-by-step from such the... Read More

Software Process Improvement -A Successful Journey

Background: For many organizations like ours, the interim target of... Read More

Algebra Help Software

Need help making sense of algebra? Have algebra lectures in... Read More

S is for Spying, Surveillance -- and for Software as Well

Words we choose to describe things and phenomena often show... 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

netstat - Linux Command to Display Networking Information

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

Document Templates Give You The Perfect Framework For Your Documents

When it comes to running an office, the SOHO entrepreneur... Read More

Microsoft Office Selecting Secrets

Stop the Runaway MouseWhat's the "runaway mouse?" You've seen it...you... Read More

Free Program Removes Spyware not Detected by Premium Security Scan

What is Spyware?Spyware monitors your surfing habits and sends the... Read More

A Time-Saving Programming Tactic that Doesn?t Work

Let's say that you have a software project that's under... Read More

Twelve Things You Should Know to Save on Computer Software

Do you want to get quality software at a reasonable... Read More

Reporting for Microsoft Great Plains/Dynamics/eEnterprise: RW ? ReportWriter ? Tips for Developer

Microsoft Business Solutions Great Plains is written in Great Plains... Read More

Business Plan Software Review: Ultimate Business Planner?

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