aspose file tools*
The moose likes JDBC and the fly likes Efficient database connections? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Efficient database connections?" Watch "Efficient database connections?" New topic
Author

Efficient database connections?

Louis Fatham
Greenhorn

Joined: Feb 23, 2005
Posts: 2
I'm new to programming in Java, so please forgive what might be a dumb question.

I wrote an application that makes many calls to a relational database. When I first wrote the program, I didn't take a lot of care to use code efficiently, and ended up repeating the same statements over and over throughout, making a single database connection to perform multiple select statements, and return multiple resultsets. And the thing ran like a champ.

But since I'm trying to go back and clean it up, I decided to move all the database calls to a separate class file (makes sense, I thought). Since the calls would use the same format, but use the resultsets differently, I decided to return the resultset back to the calling program, and scan it there. That works, however the processing speed fell massively. I have verified that I am closing the resultsets after every call (and subsequent processing of that resultset in the calling program). I think it's probably due to the fact that before, I was limiting the number of connection statements by having the db-logic inline with the main code, whereas now, every database call requires a new connection. Could that be the cause of the massive slowdown?
If that's so, can you suggest how I should organize the logic? Is it normal to isolate the database calls to a class of their own? Is it orthodox to return a resultset to the calling program, if the processing of the resultset differs for each statement performing the database lookup?
I'm just trying to write good code....

Thanks!
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
Originally posted by Louis Fatham:
But since I'm trying to go back and clean it up, I decided to move all the database calls to a separate class file (makes sense, I thought).

Nice thought.


Every database call requires a new connection. Could that be the cause of the massive slowdown?


May be, but not massive. You can implement connection pooling mechanism.


Is it normal to isolate the database calls to a class of their own?


It is considered as best practice.


If that's so, can you suggest how I should organize the logic? Is it normal to isolate the database calls to a class of their own? Is it orthodox to return a resultset to the calling program, if the processing of the resultset differs for each statement performing the database lookup?
I'm just trying to write good code....


I suggest you to read a bit about DAO (Data Access Object) and VO (Value Object) in order to get the best.

Wish you luck.
Louis Fatham
Greenhorn

Joined: Feb 23, 2005
Posts: 2
Thanks, Adeel! I'll look into that.
Joe Nguyen
Ranch Hand

Joined: Apr 20, 2001
Posts: 161
Perhaps, you haven't closed the connection soon enough. Consider creating a disconnected resultset (Value Object), using connection pool, and preparedStatement caching.
Ben Souther
Sheriff

Joined: Dec 11, 2004
Posts: 13410

Getting the connection is usually the biggest bottle neck your app will face (unless it's a web app, in which the internet will be #1).

If you were re-using a single connection, your first shot was probably much more efficient that the cleaned up code.

As Adeel has stated, your best bet is to implement a connection pool.
http://jakarta.apache.org/commons/dbcp


Java API J2EE API Servlet Spec JSP Spec How to ask a question... Simple Servlet Examples jsonf
 
jQuery in Action, 2nd edition
 
subject: Efficient database connections?